Routine Sql to list all jobs linked to specific table-fails
Moderators: chulett, rschirm, roy
SQL is in single line, sql without eval works.
(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;"
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;"
Routine
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 ;
#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?
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?
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Code: Select all
cmd = \SELECT DS_JOBS.NAME AS JOB_NAME, \
cmd := \ EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 THEN 'FOUND' ELSE 'NTFND'" \
cmd := \ FROM DS_JOBS,DS_JOBOBJECTS \
cmd := \ WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;\
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.
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Code: Select all
cmd = \SELECT DS_JOBS.NAME AS JOB_NAME, \
cmd := \ EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 THEN 'FOUND' ELSE 'NTFND'" GOTCHA\
cmd := \ FROM DS_JOBS,DS_JOBOBJECTS \
cmd := \ WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND GOTCHA = 'FOUND';\
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.
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
Code: Select all
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;\
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.
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.
*------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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
My error. The table name needs to be single-quoted.
or
Code: Select all
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;\
Code: Select all
cmd = \SELECT EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,\ : SQuote(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;\
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.