ORA-00933:

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
edward_m
Charter Member
Charter Member
Posts: 257
Joined: Fri Jun 24, 2005 9:34 am
Location: Philadelphia,PA

ORA-00933:

Post by edward_m »

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
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

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.
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
edward_m
Charter Member
Charter Member
Posts: 257
Joined: Fri Jun 24, 2005 9:34 am
Location: Philadelphia,PA

Post by edward_m »

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
Bala R
Participant
Posts: 66
Joined: Mon May 30, 2005 9:52 pm

Post by Bala R »

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.
Bala R
Participant
Posts: 66
Joined: Mon May 30, 2005 9:52 pm

Post by Bala R »

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.
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

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?
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Bala R
Participant
Posts: 66
Joined: Mon May 30, 2005 9:52 pm

Post by Bala R »

Oops.. sorry for double posting. Didn't realise i did that. Got to get used to how to post properly :oops: . My bad.
edward_m
Charter Member
Charter Member
Posts: 257
Joined: Fri Jun 24, 2005 9:34 am
Location: Philadelphia,PA

Post by edward_m »

kcbland,

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

Thanks.
Post Reply