Alaska Cowboy
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
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,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
georgeki, worked !
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".