SQLLDR Friday Challenge......
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: SQLLDR Friday Challenge......

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    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 ??

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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 07:58 AM.

  3. #3
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

  6. #6
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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                
    )

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by Horace
    So something like this ??
    Well?

    Did it?

  8. #8
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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
  •  



Click Here to Expand Forum to Full Width