troubleshooting Question

Sql Loader, when clause not working with other parameters

Avatar of Alaska Cowboy
Alaska CowboyFlag for United States of America asked on
Oracle Database
4 Comments2 Solutions628 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros