DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
edward_m


since April 2006

Group memberships:
Premium Members

Joined: 24 Jun 2005
Posts: 257
Location: Philadelphia,PA
Points: 2077

Post Posted: Fri Oct 28, 2005 9:23 am Reply with quote    Back to top    

DataStage® Release: 5x
Job Type: Server
OS: Windows
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 15 Jan 2003
Posts: 5209
Location: Lutz, FL
Points: 39192

Post Posted: Fri Oct 28, 2005 9:26 am Reply with quote    Back to top    

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

_________________
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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42665
Location: Denver, CO
Points: 219727

Post Posted: Fri Oct 28, 2005 9:29 am Reply with quote    Back to top    

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

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
edward_m


since April 2006

Group memberships:
Premium Members

Joined: 24 Jun 2005
Posts: 257
Location: Philadelphia,PA
Points: 2077

Post Posted: Fri Oct 28, 2005 9:32 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
Bala R
Participant



Joined: 30 May 2005
Posts: 66

Points: 380

Post Posted: Fri Oct 28, 2005 9:59 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
Bala R
Participant



Joined: 30 May 2005
Posts: 66

Points: 380

Post Posted: Fri Oct 28, 2005 10:01 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
kcbland

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 15 Jan 2003
Posts: 5209
Location: Lutz, FL
Points: 39192

Post Posted: Fri Oct 28, 2005 10:17 am Reply with quote    Back to top    

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

_________________
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
Rate this response:  
Not yet rated
Bala R
Participant



Joined: 30 May 2005
Posts: 66

Points: 380

Post Posted: Fri Oct 28, 2005 10:36 am Reply with quote    Back to top    

Oops.. sorry for double posting. Didn't realise i did that. Got to get used to how to post properly Embarassed . My bad.
Rate this response:  
Not yet rated
edward_m


since April 2006

Group memberships:
Premium Members

Joined: 24 Jun 2005
Posts: 257
Location: Philadelphia,PA
Points: 2077

Post Posted: Fri Oct 28, 2005 10:51 am Reply with quote    Back to top    

kcbland,

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

Thanks.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours