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



Joined: 02 Jul 2004
Posts: 6

Points: 69

Post Posted: Thu Jan 20, 2005 10:09 am Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Server
OS: Windows
I have the following SQL running witin a server routine but I keep getting an error. Has anyone any ideas.

The SQL is
"SELECT J_C1CRR , '|', J_C1CRRD FROM PS_JWS_F1113
WHERE ROWNUM = 1 AND J_C1RTTY = 'S'
AND J_C1CRCD = '":CurrencyFrom:"' AND J_C1CRDC = '":CurrencyTo:"'
AND J_C1EFT <= ":TransactionDate:"
ORDER BY J_C1EFT DESC SUPPRESS COLUMN HEADING COUNT.SUP;"

The Error message when I test the job is

TEST #17
********

Arg1 = GBP
Arg2 = USD
Arg3 = 105015

Test completed.


Result = DataStage/SQL: syntax error. Unexpected symbol. Token was "J_C1CRR".Scanned command was FROM PS_JWS_F1113 SELECT J_C1CRR|Y
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 39169
Location: Denver, CO
Points: 200003

Post Posted: Thu Jan 20, 2005 10:15 am Reply with quote    Back to top    

Post the entire text of your routine, please. Use the 'code' tags to wrap it so that it is readable... you can verify that by using the 'Preview'. Without those tags it left justifies everything and i ...

_________________
-craig

If you try and take a cat apart to see how it works, the first thing you have on your hands is a non-working cat. -- Douglas Adams
Rate this response:  
Not yet rated
Wozzer
Participant



Joined: 02 Jul 2004
Posts: 6

Points: 69

Post Posted: Thu Jan 20, 2005 10:22 am Reply with quote    Back to top    

The entry parameters are

InCurrencyFrom
InCurrencyTo
InTransactionDate

This is the Code


CurrencyFrom = Trimf(Trimb(InCurrencyFrom))

If Trimf(Trimb(InCurrencyTo)) = '' OR IsNull(Trimf(Trimb(InCurrencyTo))) Then
CurrencyTo = 'USD'
End Else
CurrencyTo = Trimf(Trimb(InCurrencyTo))
End


If Trimf(Trimb(InTransactionDate)) = '' OR IsNull(Trimf(Trimb(InTransactionDate))) Then
TransactionDate = 999999
End Else
TransactionDate = Int(Trimf(Trimb(InTransactionDate)))
End

ConvRate = @Null
ConvMethod = @Null


*** To get the System constant ***

CmdSysCons = "SELECT J_CCCRYR FROM PS_JWS_F0010 WHERE J_CCCO = '00000' SUPPRESS COLUMN HEADING COUNT.SUP;"

Call DSExecute("TCL", CmdSysCons, OutputSysCons, SysRetCd1)

SystemConstant = Field(EReplace(TRIM(OutputSysCons), @FM, "",2),@FM,1)

*************************************************************************************************************

If CurrencyFrom <> '' and CurrencyTo <> '' then

If SystemConstant = 'N' OR CurrencyFrom = CurrencyTo Then

ConvRate = 1
ConvMethod = SystemConstant

End

*************************************************************************************************************

If SystemConstant <> 'N' AND CurrencyFrom <> CurrencyTo Then

CmdMulDiv1 = "SELECT J_C1CRR,'|',J_C1CRRD FROM PS_JWS_F1113 WHERE ROWNUM = 1 AND J_C1RTTY = 'S' AND J_C1CRCD = '":CurrencyFrom:"' AND J_C1CRDC = '":CurrencyTo:"' AND J_C1EFT <= ":TransactionDate:" ORDER BY J_C1EFT DESC SUPPRESS COLUMN HEADING COUNT.SUP;"

Call DSExecute("TCL", CmdMulDiv1, OutputMulDiv1, SysRetCd2)

OutputMulDiv1 = Field(EReplace(TRIM(OutputMulDiv1), @FM, "",2),@FM,1)


Multiplier = Trim(Field(OutputMulDiv1, "|", 1))
Divisor = Trim(Field(OutputMulDiv1, "|", 2))

If Len(Multiplier) = 0 OR Multiplier = '' Then

Multiplier = @Null

End

If Len(Divisor) = 0 OR Divisor = '' Then

Divisor = @Null

End




If SystemConstant = 'Y' Then

ConvRate = Multiplier
ConvMethod = SystemConstant

End Else

ConvRate = Divisor
ConvMethod = SystemConstant

End

End

End

Ans = ConvRate:"|":ConvMethod
Rate this response:  
Not yet rated
Sainath.Srinivasan

Premium Poster
Participant

Group memberships:
Heartland Usergroup

Joined: 17 Jan 2005
Posts: 3337
Location: United Kingdom
Points: 14194

Post Posted: Thu Jan 20, 2005 10:26 am Reply with quote    Back to top    

I have noticed the pipe symbol - '|' - resulting in problems in DSExecute. Can you use other values instead in the SQL.
Rate this response:  
Not yet rated
Wozzer
Participant



Joined: 02 Jul 2004
Posts: 6

Points: 69

Post Posted: Thu Jan 20, 2005 10:50 am Reply with quote    Back to top    

The pipe does not seem to be the problem as we also have the same style of SQL statement in another routine (which works) and it brings back the correct info.
Rate this response:  
Not yet rated
Sainath.Srinivasan

Premium Poster
Participant

Group memberships:
Heartland Usergroup

Joined: 17 Jan 2005
Posts: 3337
Location: United Kingdom
Points: 14194

Post Posted: Thu Jan 20, 2005 10:54 am Reply with quote    Back to top    

Another check - why are there 3 single quotes - or one double and one single quotes - around CurrencyFrom and CurrencyTo?

Should that be only one double quotes on each side of them.
Rate this response:  
Not yet rated
Wozzer
Participant



Joined: 02 Jul 2004
Posts: 6

Points: 69

Post Posted: Thu Jan 20, 2005 11:03 am Reply with quote    Back to top    

There are double quotes within a set of single quotes.
The double quotes denote that we are using a variable and placing it in the SQL statement.
Rate this response:  
Not yet rated
Wozzer
Participant



Joined: 02 Jul 2004
Posts: 6

Points: 69

Post Posted: Thu Jan 20, 2005 11:34 am Reply with quote    Back to top    

Seem to have sorted the problem.

Its in the wrong table space so this may sort it
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