Avatar of Alaska Cowboy
Alaska Cowboy
Flag for United States of America asked on

Sql Loader, when clause not working with other parameters

I have what I thought would be an easy load but this is giving me fits . . .

First thing is the WHEN clause isn't working with other parameters.

the error is:

SQL*Loader: Release - Production on Tue Aug 9 16:02:32 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL*Loader-350: Syntax error at line 18.
Expecting "(", found keyword when.
        when (2:4) != 'HDR'

if I comment out the 2nd when clause, the data loads (but I have other issues, will post separately).

I have another load that works fine with the same parameters, just no when clause
-- This file is used to build the control file for Medicare Part D Manufacturers List
-- There are two parts to this control file
-- Part one is the infile, badfile info (designated in the UNIX script), built from the script.
        APPEND INTO TABLE medpartd.etl_file_load_det
        when (2:4) = 'HDR'
        etl_load_file_id "medpartd.etl_file_load_det_seq.nextval",
        etl_file_nm constant 'MMR Load from CGI',
        etl_prcs_load_dt "sysdate"
        INTO TABLE MEDPARTD.cgdp_rfr_mftrr_list
        fields terminated by ',' optionally enclosed by '"' trailing nullcols
        when (2:4) != 'HDR'
        mftrr_nm char,
        p_nbr char,
        lblr_cd char,
        lblr_cd_firm_nm char,
        add_dt date "mm/dd/yyyy",
        efft_dt date "mm/dd/yyyy",
        mftrr_list_skey "medpartd.mor_rpt_det_seq.nextval",
        aud_insrt_id "user",
        aud_srce_sys_cd constant '194',
        aud_insrt_tmstp "localtimestamp",
        etl_load_file_id "medpartd.etl_file_load_det_seq.currval"

/****** this works fine in a different load ******/
                TRAILING NULLCOLS

Open in new window

Oracle Database

Avatar of undefined
Last Comment
Alaska Cowboy

8/22/2022 - Mon
no worries :-) no nothing

Check this:

length is optional and is  maximum length, having also in mind the follwing

If  length isn' t specified, then it is derived from position specification.

With no length specification,  if there is no position specificed, char  data is supposed to be of  length:1 -except for delimited fields for which when no length  specified then the default=255 bytes.

If a delimited char has more 255 bytes one should specify maximum length. If not you get an error that says  "...exceeds maximum length".
no worries :-) no nothing

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Alaska Cowboy

georgeki, worked !
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck