-
SQLLDR Friday Challenge......
Hi,
I have a challenge to overcome in SQLLDR.
I have a column TIME_AT_ADDRESS with an input format of YYMM, where YY is the number of years, and MM is the number of months at address.
I need to convert it to number of months, so 0311 (3 years, 11 months) becomes 47 (months)
However, the default value of 9999 needs to be preserved without conversion.
Can this be done purely in SQLLDR ??
-
you can use soemthing like this in your controlfile
Code:
your_column_name DECIMAL EXTERNAL months_between(trunc(sysdate, 'MM'), trunc(:your_column_name, 'MM'))
Last edited by pando; 02-18-2005 at 08:58 AM.
-
Sorry, forgot to mention that column is number(4), so no date functions allowed.
e.g.
TIME_AT_ADDRESS number(4)
Code:
Data in Data Loaded
------- -----------
0311 47
0106 18
9999 9999
-
A wide variety of SQL operators can be applied to field data with the SQL string. This string can contain any combination of SQL expressions that are recognized by Oracle as valid for the VALUES clause of an INSERT statement. In general, any SQL function that returns a single value can be used.
So perhaps:
CASE WHEN :your_col = 9999 THEN 9999
ELSE 12*trunc(:your_col/100) + mod(:your_col,100)
END
-
Code:
your_column_name DECIMAL EXTERNAL decode(:your_column_name, 9999, 9999, months_between(trunc
(sysdate, 'MM'), to_date(:your_column_name, 'YYMM'))
months_between return number
-
So something like this ??
Code:
LOAD DATA
INFILE 'z:\PREVIOUS_ADDRESS.dat'
TRUNCATE INTO TABLE PREVIOUS_ADDRESS
(YOUR_COL POSITION(001:004) INTEGER EXTERNAL CASE WHEN :YOUR_COL = 9999
THEN 9999
ELSE 12*trunc(:YOUR_COL/100) + mod(:YOUR_COL,100)
END,
PA_INCOME POSITION(005:019) CHAR
)
-
Originally posted by Horace
So something like this ??
Well?
Did it?
-
Yes, it worked, once I remembered to put quotes(") round the CASE statement. Thanks again for your valuable input.
Finally:-
Code:
LOAD DATA
INFILE 'z:\PREVIOUS_ADDRESS.dat'
TRUNCATE INTO TABLE PREVIOUS_ADDRESS
(YOUR_COL POSITION(001:004) INTEGER EXTERNAL "CASE WHEN :YOUR_COL = 9999
THEN 9999
ELSE 12*trunc(:YOUR_COL/100) + mod(:YOUR_COL,100)
END",
PA_INCOME POSITION(005:019) CHAR
)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|