need to insert control record one time in sql loader

Alaska Cowboy
Alaska Cowboy used Ask the Experts™
I want to insert a record in a control table at the beginning of the load (with NEXTVAL seqno), then with the regular load will use CURRVAL.

Can I say something like "where rownum = 1" ? so it only happens once ?

APPEND INTO TABLE medpartd.etl_file_load_det
        when (rownum = 1) <-- something line this ??
        etl_load_file_id "medpartd.etl_file_load_det.nextval",
        etl_file_nm constant 'MMR Load from CGI',
        etl_prcs_load_dt "sysdate"
        INTO TABLE MEDPARTD.cgdp_stg_mthly_membshp_det
        mthly_membshp_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",
        MCO_CTRT_NBR  position (1:5) char,
        RUN_DT_OF_THE_FILE  position (6:13) char,
        PAYMT_DT  position (14:19) char,
        HIC_NBR  position (20:31) char,

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
rownum is for records selection



is there any way to accomplish what I'm trying to do ?
I fabricated a HDR record and then was able to say "WHEN (1:3) = 'HDR' " for the control record and then "WHEN (1:3) != 'HDR for the data records.



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial