Link to home
Start Free TrialLog in
Avatar of Alaska Cowboy
Alaska CowboyFlag 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 10.2.0.3.0 - 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 ******/
        APPEND INTO TABLE MEDPARTD.MEMBER_ENROLLMENT_LOAD
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
                TRAILING NULLCOLS
        (
        FILE_PRCS_DT CHAR,

Open in new window

Avatar of no worries :-) no nothing
no worries :-) no nothing
Flag of Greece image

Check this:

CHAR
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".
ASKER CERTIFIED SOLUTION
Avatar of no worries :-) no nothing
no worries :-) no nothing
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Alaska Cowboy

ASKER

georgeki, worked !