Re: SQL Loader with Expression-Function

Hello, in my system your expresion for column upload_id is wrong. I guess a blank is missing. If your problem is not solved yet try " 'yyyymm')) - 1)" instead of " 'yyyymm'))-1)" best regards
DA Morgan <damorgan_at_psoug.org> hat geschrieben:
> 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
Received on Mon Nov 20 2006 - 17:18:13 CET