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
Error within a Server Routine
Moderators: chulett, rschirm, roy
Re: Error within a Server Routine
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
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Re: Error within a Server Routine
Seem to have sorted the problem.
Its in the wrong table space so this may sort it
Its in the wrong table space so this may sort it