oracle 使用SQL Loader对两个字段求和时遇到问题

flvlnr44  于 2022-12-26  发布在  Oracle
关注(0)|答案(1)|浏览(123)

我在把两个字段计算成一个字段时遇到了麻烦

The Table columns are as follows

Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STUD_TYPE                                          VARCHAR2(15)
 FACULTY                                            VARCHAR2(25)
 COURSE_YEAR                                        NUMBER(1)
 PAYOUT_DATE                                        DATE
 STIPEND                                            NUMBER(3)
 COMPENSATION                                       NUMBER(4,2)
 TOTAL_PAID                                         NUMBER(5,2)

和.ctl文件

LOAD DATA
INFILE 'payments.dat'
TRUNCATE
INTO TABLE CATALOG
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
    Stud_type,
    Faculty,
    Course_year,
    Payout_date,
    Stipend,
    Compensation,
    Total_paid "SUM(Stipend,Compensation)"
)

从.dat文件加载的0个字段出现错误

Rejected - Error on table CATALOG, column TOTAL_PAID.
ORA-00934: group function is not allowed here
c2e8gylq

c2e8gylq1#

诚然,你是在把这两个值相加,但你应该使用的不是sum函数,而是字面上的 add values。

SQL> desc catalog
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STUD_TYPE                                          VARCHAR2(15)
 STIPEND                                            NUMBER(3)
 COMPENSATION                                       NUMBER(4,2)
 TOTAL_PAID                                         NUMBER(5,2)

SQL>

控制文件-注意total_paid列的值:

LOAD DATA
INFILE *
REPLACE
INTO TABLE catalog
FIELDS TERMINATED BY ',' 
TRAILING NULLCOLS
(
stud_type,
stipend,
compensation,
total_paid ":stipend + :compensation"
)

begindata
Type A,50,10
Type B,20,-2

测试:

SQL> $sqlldr scott/tiger@orcl control=test47.ctl log=test47.log

SQL*Loader: Release 18.0.0.0.0 - Production on Pet Pro 23 07:56:01 2022
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 1
Commit point reached - logical record count 2

Table CATALOG:
  2 Rows successfully loaded.

Check the log file:
  test47.log
for more information about the load.

结果:

SQL> select * from catalog;

STUD_TYPE          STIPEND COMPENSATION TOTAL_PAID
--------------- ---------- ------------ ----------
Type A                  50           10         60
Type B                  20           -2         18

SQL>

相关问题