Re: SQL Loader with Expression-Function

maik.bischoff_at_arcor.de wrote:
> Hello all,
>
> I have a problem with the SQL-Loader using the expression-function. I
> get the following error but I cannot find whats wrong with my
> control-file.
>
> Perhaps anyone can help me.
>
> Thx and brdgs,
> Maik
>
>
>
>
> Record 1: Rejected - Error on table DHL.SA_CUSTOMER_IMPORT, column
> UPLOAD_ID.
> ORA-00936: missing expression
>
>
> Control-File:
>
> LOAD DATA APPEND INTO TABLE DHL.SA_CUSTOMER_IMPORT FIELDS TERMINATED BY
> " " OPTIONALLY ENCLOSED BY '\''
> TRAILING NULLCOLS (
> APPLICATION_ID CONSTANT 1,
> UPLOAD_ID EXPRESSION "SELECT
> 'LISA'||to_char(to_number(to_char(sysdate,'YYYYMM'))-1)||'ACT' from
> DUAL",
> VERSION CONSTANT 'F2',
> CUSTOMER_ID,
> BU_ID,
> YEAR_ID "REPLACE(:YEAR_ID,'Y','')",
> PERIOD_ID "REPLACE(:PERIOD_ID,'M','')",
> ACTIVITY_ID "REPLACE(:ACTIVITY_ID,'A_','')",
> SEGMENT_ID CONSTANT '',
> PRODUCT_ID CONSTANT '',
> SCENARIO_ID,
> ACCOUNT_ID "DECODE(REPLACE(:ACCOUNT_ID,'ACC_',''), '10_A_10','10',
> '10_A_20','10',
> '10_A_30','10',
> '10_A_40','10',
> '10_A_50','10',
> '20_A_10','20',
> '20_A_50','20',REPLACE(:ACCOUNT_ID,'ACC_',''))",
> DATA_UNIT_ID,
> USER_ID CONSTANT 'LISAASIAEXPORT',
> CHANGE_DATE SYSDATE,
> DATA_VALUE,
> ROW_NUMBER SEQUENCE(1,1),
> ACTION_ID CONSTANT 10)
>

Could you please

1. give your Oracle version number
2. provide the DDL for the SA_CUSTOMER_IMPORT table
3. provide 5 rows of data you are trying to load.

It will make it easier to figure out what is happening.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org