DSXchange

When i tried to use the following sql (user defined SQL) in target oraoci stage,i am getting ORA-00933 error .

DELETE FROM mdm.mdm_benefit_fact
WHERE MBF_POST_PERIOD_KEY=:3
INSERT INTO MDM.MDM_BENEFIT_FACT (MC_CID_CDE,MG_GROUP_NUMBER,MBF_POST_PERIOD_KEY,MBF_EFFECT_PERIOD_KEY,MB_BENEFIT_KEY,MSI_SUBSID_CDE,MA_AFFILIATION_KEY,MMC_MCTR_KEY,MPR_PRODUCER_KEY,MPB_PRIM_BROK_KEY,MBF_BENEFIT_PROVIDER_KEY,MMC_MRKT_CO_KEY,MR_REP_KEY,MMA_MGR_KEY,MDC_DIRECTOR_KEY,MVP_VP_KEY,MGM_GM_KEY,MFC_COUNTY_KEY,MSIC_SIC_KEY,MS_SEGMENT_KEY,MDCC_DISTRIBUTE_CHANNEL_CDE,MBF_SALE_INVOICE_AMT,MBF_INF_INVOICE_AMT,MBF_CANCEL_INVOICE_AMT,MBF_SALE_AVG_AGES_FEMALE_SUB,MBF_INF_AVG_AGES_FEMALE_SUB,MBF_CANCEL_AVG_AGES_FEMALE_SUB,MBF_SALE_AVG_AGES_MALE_SUB,MBF_INF_AVG_AGES_MALE_SUB,MBF_CANCEL_AVG_AGES_MALE_SUB,MBF_INF_SUB_COUNT_MALE,MBF_SALE_SUB_COUNT_MALE,MBF_CANCEL_SUB_COUNT_MALE,MBF_INF_SUB_COUNT_FEMALE,MBF_SALE_SUB_COUNT_FEMALE,MBF_CANCEL_SUB_COUNT_FEMALE,MBF_LOAD_DATE,MBF_SALE_CONTRACT_COUNT,MBF_INF_CONTRACT_COUNT,MBF_CONTRACT_LOST_COUNT,MBF_ACCOUNT_STATUS,MBF_PSR_STATUS,MBF_SR_STATUS,MBF_SALE_CHANGE,MBF_INF_CHANGE,MBF_CANCEL_CHANGE,MSCA_STAT_CATEGORY_KEY,MBF_BENEFIT_EFFECTIVE_DT,MBR_BENEFIT_CANCEL_DT,MBF_BENEFIT_ID,MFA_FUNDING_ARRANGEMENT_KEY,MNR_NAIC_CD,MMAG_ASSOCIATION_GROUP_ID,MAS_ASSOCIATION_KEY) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,TO_DATE(:37, 'YYYY-MM-DD HH24:MI:SS'),:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,TO_DATE(:48, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:49, 'YYYY-MM-DD HH24:MI:SS'),:50,:51,:52,:53,:54)

Could anydoby guide me whats is wrong with my SQL.

Thanks

You can't have two statements. The data streaming to this stage uses prepared SQL, so you're going to have to move the DELETE to another stage or job. You'll probably be able to move back to generated SQL then to handle your inserts.

Besides the fact that you haven't properly ended the first sql statement, you can't use User Defined SQL like this in one link, break it up into two. All columns defined in the stage need to be bound into each sql statement.

Thats fine.
I tried to use the same delete statement in before SQL in target SQL,its giving error and it says
ORA-01008: not all variables bound .
Please suggest me how to use this delete statement in before SQL

Thanks

edward_m,
The number of columns in the link should be equal to the number of bind variables used. Guessing its bcoz you used :3 twice.
The advice given to you by others was to put the delete SQL in another stage. Not in before or after SQL in the same stage.

edward_m,
The number of columns in the link should be equal to the number of bind variables used. Guessing its bcoz you used :3 twice.
The advice given to you by others was to put the delete SQL in another stage. Not in before or after SQL in the same stage.

You would be better served by gathering the distinct values for "mdm.mdm_benefit_fact" and issuing one delete statement. You do realize that for every row in your dataset you will issue that delete statement? Wouldn't you rather have a dedicated job that just takes that list of values and feeds an OCI stage with the generated SQL statement for DELETE? Rather simple and easier, don't you think?

Oops.. sorry for double posting. Didn't realise i did that. Got to get used to how to post properly . My bad.

kcbland,

You are exactly right.I will go with your solution.
Thanks for all replies.

Thanks.