Table Used in all the ETL Jobs

[devnhi]

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.

[ray.wurlod]

EVAL expressions must be surrounded by double quotes.

[devnhi]

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 ?

[Sainath.Srinivasan]

It is an unix error and not from UV.

Try running the script from dssh (or uvsh).

[devnhi]

Thanks for the reply.But I am confused here . I am running this command from Unix box at the project directory .

Is there anything I have to try differently. I am having a feeling that there is something wrong with the syntax I am using .


Any other Suggestions for me ?

[chulett]

You also have an odd combination of single and double-quotes in there and I think a mismatch is tripping you up.

[devnhi]

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 ;"

[vivekgadwal]

"IF INDEX(@RECORD,'TableNm',1) > 0 THEN 'FOUND' ELSE '' " AS FOUND FMT '5L'
FROM DS_JOBS, DS_JOBOBJECTS

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 EVAL

[devnhi]

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 .

[DS_SUPPORT]

Your Search String "TableNm" is used some where in that job/Stage. Might be your job might be using Generated SQL, but the search string can be there in your User Defined SQL.