DSXchange

Can you post the entire routine? Is the sql in a single line or multiple lines?

(single line)
cmd = "SELECT DS_JOBS.NAME AS JOB_NAME,EVAL DS_JOBOBJECTS."if index(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 then 'FOUND' else 'NTFND'" FROM DS_JOBS,DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;"

I think the double quotes is getting messed up.
Put a DSLogInfo() command right after the sql and see if the correct sql shows up in the log as you intended it to be.

Sql without the EVAl works.

#INCLUDE DSINCLUDE JOBCONTROL.H
#INCLUDE DSINCLUDE DSJ_XFUNCS.H
* #INCLUDE UNIVERSE.INCLUDE ODBC.H
*---- Getting Directory path--------------*
strStatsFileLoc = DSGetParamInfo(DSJ.ME, "$HMK_DIRLOGS", DSJ.PARAMVALUE)
Call DSLogInfo("Statistics File Will be located at (strStatsFileLoc) :":strStatsFileLoc, DSJ.ME)

*---- getting Job Name-------------*
strJobName = DSGetJobInfo(DSJ.ME,DSJ.JOBNAME)
Call DSLogInfo("Job Name (strJobName) :":strJobName, DSJ.ME)
strWorking = DSGetParamInfo(DSJ.ME,"$HMK_DIRWORKING", DSJ.PARAMVALUE)
Call DSLogInfo("Working Directory (strWorking) :":strWorking, DSJ.ME)

*-----Getting Current Date ------*
Call DSExecute("UNIX",'echo `date "+%Y%m%d%H%M%S"`', Output, SystemReturnCode)
Call DSLogInfo(Output,DSJ.ME)
Jobrun_datetime = Output[1,14]
Jobrun_date = Output[1,4]:'-':Output[5,2]:'-':Output[7,2]
*------Parse Input args ------*
* "#POPAPPL.ANALYSIS_VERSION"
vTableName=Field(InputArg,'#',2)
FOUND= ' '
*------Searching for all jobs with vTableName ------*
cmd = "SELECT DS_JOBS.NAME AS JOB_NAME,EVAL DS_JOBOBJECTS."if index(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 then 'FOUND' else 'NTFND'" FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;"

execute cmd capturing output returning RtnValue

Lines = DCount(output,@FM)
Jobs=""
for i=3 to Lines
Space = Count(output,' ')
if Trim(Field(output,' ',1))<>"" Then Jobs := ',' : Field(output,' ',1): '|' : Field(output,' ',Space+1)
next i
*Ans = Right(Jobs,LEN(Jobs)-1)

*------- Writing to the File----------*
WriteFile:
strFileName = "SEARCH.":strJobName:Jobrun_datetime:".log"
strFile = strStatsFileLoc:'/':strFileName
OpenSeq strFile To objFileVar
Else Create objFileVar Else ErrorCode =1
WeofSeq objFileVar
WriteSeq "Start Search TableName for Jobs Report" To objFileVar Then
End
WriteSeq "vTableName: " :vTableName To objFileVar Then
End
WriteSeq "Jobs: " :output To objFileVar Then
End
WriteSeq FMT("Jobs: ","8L"):FMT(Jobs,"20L") To objFileVar Then
End
WriteSeq FMT("Lines: ","6L"):FMT(Lines,"6L") To objFileVar Then
End
WriteSeq "End Search TableName for Jobs Report" To objFileVar Then
End
CloseSeq objFileVar
Call DSLogInfo("Search file ":strFile:" created.",DSJ.ME)

ErrorCode = 0 ;

(single line)
cmd = "SELECT DS_JOBS.NAME AS JOB_NAME,EVAL DS_JOBOBJECTS."if index(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 then 'FOUND' else 'NTFND'" FROM DS_JOBS,DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;"

Question: The inner or evaluate quotes are the problem. Can i use escape characters to allow DataStage/Universe to accept the evaluate
or does anyone have a solution?

I thought you said that it works without the EVAL?
Try putting the sql within DQUOTE() function. See if that works.

HERE ARE THE TEST RESULTS USING DQUOTE (failed):
ORIGINAL SQL - One Line (Works in DS Admin):
cmd = "SELECT DS_JOBS.NAME AS JOB_NAME FMT '35L',DS_JOBS.CATEGORY,DS_JOBOBJECTS.NAME AS OBJECT_NAME,
DS_JOBOBJECTS.OLETYPE,
EVAL DS_JOBOBJECTS."if index(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 then 'FOUND' else 'NTFND'" AS FOUND FMT '5L'
FROM DS_JOBS,DS_JOBOBJECTS
WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO and FOUND = 'FOUND'
GROUP BY JOB_NAME,DS_JOBS.CATEGORY,OBJECT_NAME,DS_JOBOBJECTS.OLETYPE, FOUND;"

TEST DQUOTE IN PLACE OF QUOTES:
(Single Line - Test DQUOTE in place of outer quotes)
cmd = DQUOTE(SELECT DS_JOBS.NAME AS JOB_NAME FMT '35L',
EVAL DS_JOBOBJECTS."if index(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 then 'FOUND' else 'NTFND'"
FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;)

(Compile Error)
0045 cmd = DQUOTE(SELECT DS_JOBS.NAME AS JOB_NAME FMT '35L',
EVAL DS_JOBOBJECTS."if index(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 then 'FOUND' else 'NTFND'"
FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;)
^
')' unexpected, Was expecting: ')', ',', ';'
WARNING: Variable 'SELECT' never assigned a value.
WARNING: Variable 'DS_JOBS.NAME' never assigned a value.
WARNING: Variable 'AS' never assigned a value.
WARNING: Variable 'JOB_NAME' never assigned a value.
WARNING: Variable 'FMT' never assigned a value.
WARNING: Variable 'EVAL' never assigned a value.
WARNING: Variable 'DS_JOBOBJECTS.' never assigned a value.
WARNING: Variable 'FROM' never assigned a value.
WARNING: Variable 'DS_JOBS' never assigned a value.
WARNING: Variable 'DS_JOBOBJECTS' never assigned a value.
WARNING: Variable 'WHERE' never assigned a value.
WARNING: Variable 'DS_JOBS.JOBNO' never assigned a value.
WARNING: Variable 'DS_JOBOBJECTS.OBJIDNO' never assigned a value.

1 Errors detected, No Object Code Produced.

(Single Line - Test DQUOTE in place of Evaluate Quotes)
rpcDSTableSearch2Log..JobControl (1):
cmd sql= SELECT DS_JOBS.NAME AS JOB_NAME FMT '35L',
EVAL DS_JOBOBJECTS.DQUOTE(if index(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 then 'FOUND' else 'NTFND')
FROM DS_JOBS, DS_JOBOBJECTS
WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;

ERROR:
rpcDSTableSearch2Log..JobControl (1): Output= DataStage/SQL: syntax error. Unexpected symbol.
Token was "DS_JOBOBJECTS.DQUOTE".
Scanned command was FROM DS_JOBS , DS_JOBOBJECTS SELECT DS_JOBS.NAME AS JOB_NAME FMT "35L" ,
EVAL DS_JOBOBJECTS.DQUOTE

Well, that did not work very well. I am currently not on a DS Machine. Some other kind soul needs to test it out by trial and error and give you a response. Lets just wait for the experts. Be patient.

You need double quotes and single quotes within the SQL command's syntax, therefore you need to surround the command itself with the third quote character - backslash. DQUOTE() yields double quote characters, which are used in the EVAL.

Your previous post with the \ and := worked without any problems executing from a sequence routine stage. (THANKS Ray)
I then added in the AS FOUND logic and this errored out:

cmd = \SELECT DS_JOBS.NAME AS JOB_NAME, \
cmd := \ EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 THEN 'FOUND' ELSE 'NTFND'" AS FOUND \
cmd := \ FROM DS_JOBS,DS_JOBOBJECTS \
cmd := \ WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND FOUND = 'FOUND';\


Generated SQL from Director log:
rpcDSTableSearch2Log..JobControl (1): cmd sql= SELECT DS_JOBS.NAME AS JOB_NAME,
EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0
THEN 'FOUND' ELSE 'NTFND'" AS FOUND FROM DS_JOBS,DS_JOBOBJECTS
WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND FOUND = 'FOUND';

Error from Director log:
pcDSTableSearch2Log..JobControl (1):
Output= IF INDEX ( @RECORD , POPAPPL.ANALYSIS_VERSION , 1 ) > 0 THEN FOUND ELSE NTFND
DataStage/SQL: Internal problem with ALIAS, submit GTAR.

Issue:
The AS FOUND connected with the EVAL is bad.

Pete

This is an internal problem with DataStage/SQL (GTAR = general technical assistance request). Try it without AS introducing the alias name, and try it with a different alias name.

Test Results (alias):
I tried many combinations renaming, omitting 'as' etc with the same result (GTAR).
ACTION: I am opening a case with IBM on this issue.

New problem, same SQL:
The SQL is not converting the vTableName variable consequently all result sets are NTFND.
I have tried many different syntax combinations and none work.

PS: If anyone can point me to an information source other than the Universe Manuals please do!

*------Parse Input args ------*
vTableName=Field(InputArg,'#',2)

*------Searching for all jobs with vTableName ------*
cmd = \SELECT EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,':vTableName',1) > 0 THEN 'FOUND' ELSE 'NTFND'",\
cmd := \ DS_JOBS.NAME AS JOB_NAME FMT '40L' \
cmd := \ FROM DS_JOBS,DS_JOBOBJECTS \
cmd := \ WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO\
cmd := \ ORDER BY 1,2;\

vTableName variable from Director Log:
rpcDSTableSearch..JobControl (1): vTableName= POPAPPL.ANALYSIS_VERSION

Original SQL from Director Log:
rpcDSTableSearch..JobControl (1): cmd sql=
SELECT EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,':vTableName',1) > 0
THEN 'FOUND' ELSE 'NTFND'", DS_JOBS.NAME AS JOB_NAME FMT '40L'
FROM DS_JOBS,DS_JOBOBJECTS
WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO ORDER BY 1,2;

Converted SQL from Director Log:
rpcDSTableSearch..JobControl (1): SQL = IF INDEX ( @RECORD , :vTableName , 1 ) > 0 THEN FOUND ELSE NTFND
IF INDEX(@RECORD,':v
TableName',1) > 0 TH
EN 'FOUND' ELSE 'NTF
ND'................. JOB_NAME................................

NTFND CreateDataSetJob
NTFND CreateDataSetJob

What are the quote characters in (@RECORD,':vTableName',1 meant to be doing? Since it's still inside the backquotes it's within the string. You're also missing a concatenation operator. Try

The \:vTableName:\ modification results in a syntax error:

*------Searching for all jobs with vTableName - Original SQL in Routine ------*
cmd = \SELECT EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,\:vTableName:\,1) > 0 THEN 'FOUND' ELSE 'NTFND'",\
cmd := \ DS_JOBS.NAME AS JOB_NAME FMT '40L' \
cmd := \ FROM DS_JOBS,DS_JOBOBJECTS \
cmd := \ WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;\

vTableName variable from Director Log:
rpcDSTableSearch2Log..JobControl (1): vTableName= POPAPPL.ANALYSIS_VERSION

Original SQL from Director Log:
rpcDSTableSearch2Log..JobControl (1): cmd sql=
SELECT EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,POPAPPL.ANALYSIS_VERSION,1) > 0 THEN 'FOUND' ELSE 'NTFND'", DS_JOBS.NAME AS JOB_NAME FMT '40L' FROM DS_JOBS,DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;

Converted SQL from Director Log (syntax error):
rpcDSTableSearch2Log..JobControl (1): SQL = IF INDEX ( @RECORD , Word POPAPPL.ANALYSIS_VERSION.
POPAPPL.ANALYSIS_VERSION syntax error

I-descriptor "IF INDEX(@RECORD,POPAPPL.ANALYSIS_VERSION,1) > 0 THEN 'FOUND' ELSE 'NTFND'" was not compiled.

My error. The table name needs to be single-quoted. or