DECODE in SQL LDR control file 1

[kevina555]

Hi,

I need to decode an incoming value from the delimited file and insert the decoded value into the target table. For this, I was wondering if I could write a "decode" in the control file itself or if I have to use a Before Insert Row level trigger........


Thank you.

 

[LKBrwnDBA]

You can use sql functions in the control file:

LOAD DATA
INFILE ...
INSERT INTO TABLE MYTABLE
FIELDS TERMINATED BY ',' 
(COL1
,COL2 "DECODE(:COL2,'1','ONE','2','TWO','OTHER')"
,col3)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[kevina555]

Hi LKBrwnDBA,

Thank you for the clarification. I was hoping this would be possible. But the problem now is that I have a list of about 50 values to be decoded (into 50 others obviously) and when I give all these 100 in the proper format, I receive the SQLLDR 350 error which says that the 'max allowed token length of 258 is exceeded". So, I Guess I'm back to using an after insert trigger.

Any suggestions please.....

Thank you.

 

[LKBrwnDBA]

Use BEFORE insert trigger!

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[kevina555]

Hi LKBrwnDBA,

I've written a before insert trigger, but something seems to be wrong wit this, 'coz I get the PLS 00103 error everytime I try to run this.


CREATE OR REPLACE TRIGGER IDMAP
BEFORE INSERT ON fcBalances FOR EACH ROW

BEGIN


IF :NEW.ACCOUNTID = 11777 THEN

:NEW.ACCOUNTID :=3030;
ENDIF;
IF
:NEW.ACCOUNTID = 11778 THEN

:NEW.ACCOUNTID :=7640;
ENDIF;
IF :NEW.ACCOUNTID = 11779 THEN

:NEW.ACCOUNTID :=2350;
ENDIF;
IF :NEW.ACCOUNTID = 11718 THEN

:NEW.ACCOUNTID :=1050;
ENDIF;
IF :NEW.ACCOUNTID = 11780 THEN

:NEW.ACCOUNTID :=5100;
ENDIF;
IF :NEW.ACCOUNTID = 11756 THEN

:NEW.ACCOUNTID :=600;
ENDIF;
IF :NEW.ACCOUNTID = 11782 THEN


:NEW.ACCOUNTID :=6280;
ENDIF;
IF :NEW.ACCOUNTID = 11720 THEN

:NEW.ACCOUNTID :=2540;
ENDIF;
IF :NEW.ACCOUNTID = 11830 THEN

:NEW.ACCOUNTID :=1700;
ENDIF;
IF :NEW.ACCOUNTID = 11758 THEN

:NEW.ACCOUNTID :=2700;
ENDIF;
IF :NEW.ACCOUNTID = 11762 THEN

:NEW.ACCOUNTID :=9870;
ENDIF;
IF :NEW.ACCOUNTID = 11784 THEN

:NEW.ACCOUNTID :=6800;
ENDIF;
IF :NEW.ACCOUNTID = 11722 THEN

:NEW.ACCOUNTID :=2660;
ENDIF;
IF :NEW.ACCOUNTID = 11763 THEN

:NEW.ACCOUNTID :=6800;
ENDIF;
IF :NEW.ACCOUNTID = 11723 THEN

:NEW.ACCOUNTID :=5520;
ENDIF;
IF :NEW.ACCOUNTID = 11785 THEN

:NEW.ACCOUNTID :=990;
ENDIF;
IF :NEW.ACCOUNTID = 11786 THEN

:NEW.ACCOUNTID :=4100;
ENDIF;
IF :NEW.ACCOUNTID = 11724 THEN

:NEW.ACCOUNTID :=4750;
ENDIF;
IF :NEW.ACCOUNTID = 11787 THEN

:NEW.ACCOUNTID :=2650;
ENDIF;
IF :NEW.ACCOUNTID = 11726 THEN

:NEW.ACCOUNTID :=5700;
ENDIF;
IF :NEW.ACCOUNTID = 11790 THEN

:NEW.ACCOUNTID :=4280;
ENDIF;
IF :NEW.ACCOUNTID = 11791 THEN

:NEW.ACCOUNTID :=5200;
ENDIF;
IF :NEW.ACCOUNTID = 11770 THEN

:NEW.ACCOUNTID :=2480;
ENDIF;
IF :NEW.ACCOUNTID = 11727 THEN

:NEW.ACCOUNTID :=4950;
ENDIF;
IF :NEW.ACCOUNTID = 11792 THEN

:NEW.ACCOUNTID :=7920;
ENDIF;
IF :NEW.ACCOUNTID = 11771 THEN

:NEW.ACCOUNTID :=1200;
ENDIF;
IF :NEW.ACCOUNTID = 11794 THEN

:NEW.ACCOUNTID :=5050;
ENDIF;
IF :NEW.ACCOUNTID = 11772 THEN

:NEW.ACCOUNTID :=1170;
ENDIF;
IF :NEW.ACCOUNTID = 11795 THEN

:NEW.ACCOUNTID :=5200;
ENDIF;
IF :NEW.ACCOUNTID = 11842 THEN

:NEW.ACCOUNTID :=1200;
ENDIF;
IF :NEW.ACCOUNTID = 11773 THEN

:NEW.ACCOUNTID :=6420;
ELSE

:NEW.ACCOUNTID := 0000;

ENDIF;
END IDMAP;




Is there something wrong that I'm doing here.....


Thank you.

 

[LKBrwnDBA]

Space between END and IF:

...
END IF;


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb