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
Transform Function
Moderators: chulett, rschirm, roy
Transform Function
somaraju
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Literals in DataStage/SQL must be contained within single quotes.
Try this
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:
So you don't need a routine at all!
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")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 117
- Joined: Wed Feb 06, 2013 9:24 am
- Location: Chennai,TN, India