Error within a Server Routine

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
Wozzer
Participant
Posts: 6
Joined: Fri Jul 02, 2004 5:55 am

Error within a Server Routine

Post 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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

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

"You can never have too many knives" -- Logan Nine Fingers
Wozzer
Participant
Posts: 6
Joined: Fri Jul 02, 2004 5:55 am

Re: Error within a Server Routine

Post 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
Sainath.Srinivasan
Participant
Posts: 3337
Joined: Mon Jan 17, 2005 4:49 am
Location: United Kingdom

Post by Sainath.Srinivasan »

I have noticed the pipe symbol - '|' - resulting in problems in DSExecute. Can you use other values instead in the SQL.
Wozzer
Participant
Posts: 6
Joined: Fri Jul 02, 2004 5:55 am

Post 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.
Sainath.Srinivasan
Participant
Posts: 3337
Joined: Mon Jan 17, 2005 4:49 am
Location: United Kingdom

Post 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.
Wozzer
Participant
Posts: 6
Joined: Fri Jul 02, 2004 5:55 am

Post 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.
Wozzer
Participant
Posts: 6
Joined: Fri Jul 02, 2004 5:55 am

Re: Error within a Server Routine

Post by Wozzer »

Seem to have sorted the problem.

Its in the wrong table space so this may sort it
Post Reply