sql loader

This is subsequent to

http://www.experts-exchange.com/Databases/Oracle/Q_21865533.html

Now I'm able to concatenate more than one record using logical operator continueif

But the pbm is, I need to write more than one continueif as the current line's last char should not be '|' and the next lines first letter should be '|' then I want to concatenate else not.

I wrote conditions like,

CONTINUEIF LAST PRESERVE != '|'

CONTINUEIF FIRST PRESERVE != '|'

but how do i write these two in a same loader script? if i write like this,
CONTINUEIF (LAST PRESERVE != '|' and  FIRST PRESERVE != '|')

it throws error. please help.




LVL 17
ram_0218Asked:
Who is Participating?
 
paquicubaConnect With a Mentor Commented:
Ram,

This is just to give you an idea (External Tables are very flexible and faster than SQL*Loader when used with APPEND hint.



FIELDS.TXT
----------

|1234567|,|
Y|
|89|,
|Y|
|123456789|,|N
|
|12345678|
,|N|
|555
|,|N|
|
88888|,|N|


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DROP TABLE TEMPTBL;

CREATE TABLE TEMPTBL(
  TEMPCOL1 VARCHAR2(100),
  TEMPCOL2 VARCHAR2(100)
  )
  ORGANIZATION EXTERNAL
  (TYPE ORACLE_LOADER
  DEFAULT DIRECTORY CDIR
  ACCESS PARAMETERS
  (
  RECORDS DELIMITED BY NEWLINE
  NOBADFILE
  NODISCARDFILE
  NOLOGFILE
  FIELDS TERMINATED BY ','
  MISSING FIELD VALUES ARE NULL
  REJECT ROWS WITH ALL NULL FIELDS
  (
  TEMPCOL1 CHAR(100),
  TEMPCOL2 CHAR(100)
  )
  )
  LOCATION ('FIELDS.TXT'))
  REJECT LIMIT UNLIMITED;


ALEX@PROD > SELECT * FROM TEMPTBL;

TEMPCOL1             TEMPCOL2
-------------------- --------------------
|1234567|            |
Y|
|89|
|Y|
|123456789|          |N
|
|12345678|
                     |N|
|555
|                    |N|
|
88888|               |N|

12 rows selected.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


SELECT TEMPCOL1, TEMPCOL2 FROM(
SELECT REPLACE(TEMPCOL1,'|') TEMPCOL1, REPLACE(TEMPCOL2,'|') TEMPCOL2
FROM(
SELECT
CASE WHEN LENGTH(TEMPCOL1) = 1 AND SUBSTR(TEMPCOL2,1,1) <> '|' THEN
TEMPCOL1||LEAD(TEMPCOL1) OVER ( ORDER BY ROWNUM)
WHEN SUBSTR(TEMPCOL1,1,1) = '|' AND SUBSTR(TEMPCOL2,-1,1) = '|' THEN
TEMPCOL1
END
TEMPCOL1,
CASE WHEN LENGTH(TEMPCOL1) = 1 AND SUBSTR(TEMPCOL2,1,1) <> '|' THEN
LEAD(TEMPCOL2) OVER ( ORDER BY ROWNUM )
ELSE
TEMPCOL2
END
TEMPCOL2
FROM
(SELECT TEMPCOL1,
CASE
WHEN TEMPCOL2 IS NULL THEN
LEAD(TEMPCOL1) OVER (ORDER BY ROWNUM)
WHEN LENGTH(TEMPCOL2) = 1 THEN
TEMPCOL2||LEAD(TEMPCOL1) OVER (ORDER BY ROWNUM)
WHEN SUBSTR(TEMPCOL2,-1,1) <> '|' THEN
TEMPCOL2||LEAD(TEMPCOL1) OVER (ORDER BY ROWNUM)
ELSE
TEMPCOL2
END||LEAD(TEMPCOL2) OVER (ORDER BY ROWNUM) TEMPCOL2
FROM TEMPTBL)))
WHERE TEMPCOL1 IS NOT NULL
/

TEMPCOL1             TEMPCOL2
-------------------- --------------------
1234567              Y
89                   Y
123456789            N
12345678             N
555                  N
88888                N

6 rows selected.



0
 
paquicubaCommented:
I don't get it:

<< as the current line's last char should not be '|' >> CONTINUEIF LAST PRESERVE != '|'   -- OK

<< and the next lines first letter should be '|' >> CONTINUEIF FIRST PRESERVE != '|'  -- ?
0
 
actonwangCommented:
CONTINUEIF syntax is as following:

cont_if ::=

  CONTINUEIF { [THIS | NEXT] [PRESERVE]  [(]  pos_spec | LAST [PRESERVE] [(] }  
             operator { str | X'hex_str' } [)]


only one condition can be defined.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
actonwangCommented:
have you tried my other suggestion in last post - using str (record stream format)?

I gave you a detailed sample for you to chew on.

acton
0
 
paquicubaCommented:
'Next' would be an option if you never have '|' as the last and first byte.

CONTINUEIF NEXT PRESERVE = '|'
0
 
actonwangCommented:
if you use continueif, the \r \n would be lost. If you want to preserve them, you'd have to use str.

let me know how your inputting data look like (especially for comment data). I could help you out for that.


Acton
0
 
ram_0218Author Commented:
guys! i'm sorry, i'm very weak in english i apologize for my language. Heres what i'm trying to say,

every record and field gets terminated with a '|'

so my first record looks like,

|a|,|b|,|c|,|de
fg|

Here after de, theres a new line and thats good and can be caught by
CONTINUEIF LAST PRESERVE != '|'

so my record becomes a,b,c,defg

but when i've,
|a|,|b|,|c|,|
defg|

please note here, defg the string has new line character at its first place so this has to be handled differently.

Now i'm tring to write a loader script which can handle these two situations. Hope my question is clear now.
0
 
ram_0218Author Commented:
>>only one condition can be defined.

This is where I'm stuck. I need to write one more continueif statement, Somehow I'll have to handle this situation.
0
 
actonwangCommented:
I c. I can help you out for this.

Are you working on unix or windows?
0
 
ram_0218Author Commented:
UNIX, but as directed, I cant do this neither in UNIX nor in DB2.

If I'm allowed to so, I would have done it myself more easily !
0
 
actonwangCommented:
did you try str?

Also a quick question: does each record end by "\r\n" or "\n"?
0
 
actonwangCommented:
more specificly:

in your case, does it look like:

|a|,|b|,|c|,|   <- \n in the end?
defg|              <- \r\n in the end or \n in the end?


acton
0
 
ram_0218Author Commented:
both lines do have '\n'

but here the first record is the catch, the second line does not start with a '|' so it should be attached to the previous line.

In my earlier example,
The first line was not terminated by the '|' so it should be attached to the next line.
0
 
actonwangCommented:
is this not enough?

CONTINUEIF NEXT PRESERVE (1) != '|'
0
 
actonwangCommented:
this means: if the next rows doesn't start with '|', then merge the next row. I think that is what you want.
0
 
ram_0218Author Commented:
what about the record like this?

|aa|bb|cc
|def|

Please note here the first line does not get ended with a | so we should concatenate the next line also. But the next line starts with a |

practically speaking we encounter newlines before and after or in middle of the comments field which is causing this jimmick.
0
 
actonwangCommented:
those lines will be in the bad file and you can continue to process those data.

acton
0
 
actonwangCommented:
>>|aa|bb|cc
>>|def|

i though your record would be as:

|aa|,|bb|,|cc
|,|def|

if you use
fields terminated by "," enclosed by "|"

if you use CONTINUEIF NEXT PRESERVE (1) != '|' then sqlloader would reject those lines into bad file.

you could process those lines in bad file to do another APPEND load.

Acton
0
 
actonwangCommented:
if you guys can define a special end characters for each record, STR should do this pretty easily.

acton
0
 
paquicubaCommented:
Can you create an external table to manipulate the strings?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.