Transform Function

[somu_june]

Hi All,

I'm trying to find the category name for a particular job by a transformer function. Below is the code


Ans = ""
svName = "pxHtttlemp"
svTrimName = Trim (svName,".","A")
svFMName = Trim (svTrimName,@FM,"A")
*Get list of jobids that contains a SearchString
cmd = \SELECT DS_JOBS.NAME AS JOB_NAME, DS_JOBS.CATEGORY AS JOB_CAT \
cmd := \ FROM DS_JOBS, DS_JOBOBJECTS \
cmd := \ WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND DS_JOBS.NAME = \
cmd := \"\
cmd := svFMName
cmd := \";\
Anscmd = cmd
CALL DSExecute("TCL", Anscmd, vOutput, vReturnCode)
Ans := vOutput


But when executing the above function, I'm getting below error

TEST #1
*******


Test completed.


Result = DataStage/SQL: syntax error. Unexpected symbol. Token was "pxHtttlemp".
Scanned command was FROM DS_JOBS , DS_JOBOBJECTS SELECT DS_JOBS.NAME AS JOB_NAME , DS_JOBS.CATEGORY AS JOB_CAT WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND DS_JOBS.NAME = "pxHtttlemp"


I searched for the above error, but I'm not successfull, please help me in understanding where the error is occuring..

Thanks,
Somu

[ray.wurlod]

Literals in DataStage/SQL must be contained within single quotes.

Try this

Code: Select all

Ans = "" 
svName = "pxHtttlemp" 
svTrimName = Trim (svName,".","A") 
svFMName = Trim (svTrimName,@FM,"A") 
*Get list of jobids that contains a SearchString 
cmd = \SELECT DS_JOBS.NAME AS JOB_NAME, DS_JOBS.CATEGORY AS JOB_CAT \ 
cmd := \ FROM DS_JOBS, DS_JOBOBJECTS \ 
cmd := \ WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND DS_JOBS.NAME = \ 
cmd := \'\ 
cmd := svFMName 
cmd := \';\ 
Anscmd = cmd 
CALL DSExecute("TCL", Anscmd, vOutput, vReturnCode) 
Ans := vOutput

That said, your SQL code appears really cumbersome. What exactly are you trying to achieve? You aren't actually using DS_JOBOBJECTS at all. I think your whole requirement could be solved with the function:

Code: Select all

 Trans("DS_JOBS", svFMName, 3, "X")

So you don't need a routine at all!

[somu_june]

Thanks Ray for the help. It's working after using single quotes instead of double quotes for literals........

[ray.wurlod]

Sigh.

[prasannakumarkk]

somu what is the first parameter TCL in dsexecute indicates. Where is your xmeta installed