Page 1 of 1
Error within a Server Routine
Posted: Thu Jan 20, 2005 10:09 am
by Wozzer
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
Posted: Thu Jan 20, 2005 10:15 am
by chulett
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 is hard as heck to read.
Re: Error within a Server Routine
Posted: Thu Jan 20, 2005 10:22 am
by Wozzer
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
Posted: Thu Jan 20, 2005 10:26 am
by Sainath.Srinivasan
I have noticed the pipe symbol - '|' - resulting in problems in DSExecute. Can you use other values instead in the SQL.
Posted: Thu Jan 20, 2005 10:50 am
by Wozzer
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.
Posted: Thu Jan 20, 2005 10:54 am
by Sainath.Srinivasan
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.
Posted: Thu Jan 20, 2005 11:03 am
by Wozzer
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.
Re: Error within a Server Routine
Posted: Thu Jan 20, 2005 11:34 am
by Wozzer
Seem to have sorted the problem.
Its in the wrong table space so this may sort it