Table Used in all the ETL Jobs
Moderators: chulett, rschirm, roy
Table Used in all the ETL Jobs
Hi
I am using the query
"SELECT DISTINCT DS_JOBS.NAME AS JOB_NAME FMT '100L', DS_JOBOBJECTS.NAME AS OBJECT_NAME FMT '100L', DS_JOBOBJECTS.OLETYPE FMT '100L', EVAL DS_JOBOBJECTS.'IF INDEX(@RECORD,'MYTABLE',1) > 0 THEN 'FOUND' ELSE ''' 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 "
But this query is failing saying that
SQL+
IF INDEX ( @RECORD , syntax error
I-descriptor "IF INDEX(@RECORD," was not compiled.
Is there anything I have to correct .Any help is appreciated.
I am using the query
"SELECT DISTINCT DS_JOBS.NAME AS JOB_NAME FMT '100L', DS_JOBOBJECTS.NAME AS OBJECT_NAME FMT '100L', DS_JOBOBJECTS.OLETYPE FMT '100L', EVAL DS_JOBOBJECTS.'IF INDEX(@RECORD,'MYTABLE',1) > 0 THEN 'FOUND' ELSE ''' 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 "
But this query is failing saying that
SQL+
IF INDEX ( @RECORD , syntax error
I-descriptor "IF INDEX(@RECORD," was not compiled.
Is there anything I have to correct .Any help is appreciated.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I have tried this :
/dsadm/Ascential/DataStage/DSEngine/bin/uvsh "SELECT DISTINCT DS_JOBS.NAME AS JOB_NAME FMT '100L', DS_JOBOBJECTS.NAME AS OBJECT_NAME FMT '100L', DS_JOBOBJECTS.OLETYPE FMT '100L', EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,'TableNm',1) > 0 THEN 'FOUND' ELSE ''" 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 ;"
But it is failing saying
ksh: syntax error: `(' unexpected
Any Suggestions for me ?
/dsadm/Ascential/DataStage/DSEngine/bin/uvsh "SELECT DISTINCT DS_JOBS.NAME AS JOB_NAME FMT '100L', DS_JOBOBJECTS.NAME AS OBJECT_NAME FMT '100L', DS_JOBOBJECTS.OLETYPE FMT '100L', EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,'TableNm',1) > 0 THEN 'FOUND' ELSE ''" 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 ;"
But it is failing saying
ksh: syntax error: `(' unexpected
Any Suggestions for me ?
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Thanks for the response. But I am failing to figure out the Problem with this universe query. I would really really appreciate any help in correcting this .
/dsadm/Ascential/DataStage/DSEngine/bin/uvsh
"SELECT DISTINCT DS_JOBS.NAME AS JOB_NAME FMT '100L', DS_JOBOBJECTS.NAME AS OBJECT_NAME FMT '100L', DS_JOBOBJECTS.OLETYPE FMT '100L',
EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,'TableNm',1) > 0 THEN 'FOUND' ELSE '' " 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 ;"
/dsadm/Ascential/DataStage/DSEngine/bin/uvsh
"SELECT DISTINCT DS_JOBS.NAME AS JOB_NAME FMT '100L', DS_JOBOBJECTS.NAME AS OBJECT_NAME FMT '100L', DS_JOBOBJECTS.OLETYPE FMT '100L',
EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,'TableNm',1) > 0 THEN 'FOUND' ELSE '' " 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 ;"
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
Try using ' ' at the else part. May be it doesn't like " " at that point, thinking that these quotes might be the end of the EVALdevnhi wrote:"IF INDEX(@RECORD,'TableNm',1) > 0 THEN 'FOUND' ELSE '' " AS FOUND FMT '5L'
FROM DS_JOBS, DS_JOBOBJECTS
![Smile :)](./images/smilies/icon_smile.gif)
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
Thank you . Now the query is working after I replaced the '' with the ' ' .But now I am wondering with the result of the query. I actually saw this query in the forum to get the jobs where a table has been used .
In one of my job , I did not used the table explicitly anywhere in the query .But the result of the query is returning that the job used the table .
Can somebody explain me , what will the result of the query will be like . Even if the job uses the meta data of the table , it will list out ? How does this will work .
I would appreciate all your help and response on this .
In one of my job , I did not used the table explicitly anywhere in the query .But the result of the query is returning that the job used the table .
Can somebody explain me , what will the result of the query will be like . Even if the job uses the meta data of the table , it will list out ? How does this will work .
I would appreciate all your help and response on this .
-
- Premium Member
- Posts: 232
- Joined: Fri Aug 04, 2006 1:20 am
- Location: Bangalore