USING CASE STATEMENT IN SQL LOADER CONTROL FILE

Question

I have a control file that loads text to oracle but i am trying to strip first 3 digits of Phone number example 1-469-428-5920 to 469-428-5920, please see below control file. what should be the syntax?

I keep getting error below, Please help

thanks

options (ERRORS=1000, SKIP=2)
load data 
infile '/clients/vca2/data/API/ADHOC_LOAD/VCA_RP_Hospital_Website_Master_List_VCA.CSV'
replace
into table VCABARK_CDI_DV.RP_HOSPITAL_MASTER_LIST
fields terminated by '|'
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
(
    HOSPITAL_EXT_NUMBER, 
    REGION, 
    HOSPITAL_NAME, 
    ADDRESS_1, 
    CITY , 
    STATE, 
    POSTAL_CODE, 
    PHONE "CASE WHEN SUBSTR(:PHONE,1,2) = '1-'  THEN ‘SUBSTR(:PHONE,3)’  ELSE ‘:PHONE’ END",
        RCF_PHONE,
    FAX , 
    EMAIL ,
    WEBSITE_CLASS, 
    DOMAIN, 
    SHORTNAME, 
    PMS, 
    INDICATOR_24_HOUR, 
    CARECLUB, 
    HOME_DELIVERY, 
    APPOINTMENT_CTA, 
    FREE_FIRST_EXAM, 
    AAHA , 
    AAFP_MEMBER, 
    CAT_FRIENDLY,
    FEAR_FREE , 
    LOW_STRESS, E
    VECCS, 
    FACEBOOK_URL, 
    GMB_URL , 
    YELP_URL, 
    BOOK_URL, 
    REQUEST_URL,
        HOURS_OPERATIONS_URL,
    HD_URL_SUBDOMAIN, 
    HD_URL_ORIGINAL, 
    PET_PORTAL_URL, 
    AIS_CLINIC_ID, 
    AIS_TOKEN
 )

What error are you getting? Why do you have the (curly) quotes around bits of the case expression? — Alex Poole
– Alex Poole, Commented Mar 20, 2019 at 19:00

Alex Poole · Accepted Answer · 2019-03-20 19:03:28Z

2

You haven't said what error you are getting, but you should have quotes - curly or otherwise - within your case expression. Instead of:

PHONE "CASE WHEN SUBSTR(:PHONE,1,2) = '1-'  THEN ‘SUBSTR(:PHONE,3)’  ELSE ‘:PHONE’ END",

use:

PHONE "CASE WHEN SUBSTR(:PHONE,1,2) = '1-' THEN SUBSTR(:PHONE,3) ELSE :PHONE END",

answered Mar 20, 2019 at 19:03

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

okla Over a year ago

Thank you Alex for your response, please see error below SQL*Loader-291: Invalid bind variable :PHONE⡩n SQL string for column PHONE.

Alex Poole Over a year ago

@okla - is that your original error? You should have got "ORA-00911: invalid character" from what you showed. Or are you saying you're getting that with my modified expression? I've just retested that and it's working OK... But that message looks weird towards the end, do you actually have control characters embedded in your expression, as well as curly quotes? Did you edit your existing string to look like mine, or copy-and-paste? (I assume the curly quotes are from copying from Word or something; wouldn't surprise me if there was other rubbish in there too *8-)

okla Over a year ago

Alex, I tried the solution above and it worked perfectly. Thank you very much! Appreciate all your help.

Gary_W · Accepted Answer · 2019-03-20 20:41:37Z

1

You could also just keep the last 12:

PHONE "SUBSTR(:PHONE, -12, 12)",

answered Mar 20, 2019 at 20:41

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

Collectives™ on Stack Overflow

USING CASE STATEMENT IN SQL LOADER CONTROL FILE

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related