DSXchange

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

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.

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

I have noticed the pipe symbol - '|' - resulting in problems in DSExecute. Can you use other values instead in the SQL.

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.

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.

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.

Seem to have sorted the problem.

Its in the wrong table space so this may sort it