ERROR WHILE QUERYING FROM DS TABLES IN UNIVERSE STAGE

[aschindler]

Hi,

I'm using below query in universe stage in a server job and facing below error in view data.

QUERY:
SELECT A.NAME AS JOB_NAME,
CASE WHEN A.JOBNO=B.OBJIDNO AND B.OLETYPE LIKE'%Stage%' AND B.NAME LIKE'Src%' THEN B.NAME END AS SRC_STAGE_NAME,
CASE WHEN A.JOBNO=B.OBJIDNO AND B.OLETYPE LIKE'%Stage%' AND B.NAME LIKE'Ds%' THEN B.NAME END AS DS_STAGE_NAME
FROM DS_JOBS A,DS_JOBOBJECTS B
WHERE A.NAME IN('<JOBNAME>')
ERROR:
SYNTAX ERROR. UNEXPECTED VERB. TOKEN WAS "SELECT".SCANNED COMMAND WAS SELECT

[ArndW]

I don't have access to the documentation here, but I believe that the CASE isn't supported, since when I remove the two CASEs I get a valid result.

Note that the SELECT statement in UniVerse is parsed differently depending upon whether it is a Pick style SELECT or a SQL style SELECT.

[ray.wurlod]

Arnd is correct on both counts; CASE is not supported (use EVAL and an I-type expression), and you have not created a "UniVerse" SQL statement because your statement lacks a terminating semi-colon, so you've got an invalid RetrieVe SELECT statement.

[aschindler]

Thanks ray,

Will try EVAL expression.

[aschindler]

Hi Team,

I'm using below query to get source stage and target stage,but its returning deuplicates:

QUERY:

Code: Select all

SELECT A.NAME AS JOB_NAME,
       B.NAME AS SRC_STAGE,
       C.NAME AS TGT_STAGE 
  FROM DS_JOBS A,
       DS_JOBOBJECTS B,
       DS_JOBOBJECTS C 
 WHERE A.JOBNO=B.OBJIDNO 
   AND B.OLETYPE LIKE'%Stage%' 
   AND B.NAME LIKE'SRC%' 
   AND C.OLETYPE LIKE'%Stage%' 
   AND C.NAME LIKE'TGT%'

OUTPUT:

JOB_NAME SRC_STAGE TGT_STAGE
JOBA SRC_STG TGT
JOBA SRC_STG TGT_STAGE
JOBA SRC_STG TGT_OP
JOBA SRC_STG TGT_DS
JOBA SRC_STG TGT_OUTPUT

But if i use below query ,showing datasource is empty:

Code: Select all

SELECT A.NAME AS JOB_NAME,
       B.NAME AS SRC_STAGE,
       C.NAME AS TGT_STAGE 
  FROM DS_JOBS A,
       DS_JOBOBJECTS B,
       DS_JOBOBJECTS C 
 WHERE A.JOBNO=B.OBJIDNO 
   AND B.OLETYPE LIKE'%Stage%' 
   AND B.NAME LIKE'SRC%' 
   AND C.OLETYPE LIKE'%Stage%' 
   AND C.NAME LIKE'TGT%'
   and A.JOBNO=C.OBJIDNO

Please help me with a query to get source stage and target stage.

[ray.wurlod]

Source and target stage are not in the same DS_JOBOBJECTS record, as you've noticed. It might be a better approach to UNION two queries (one for source stages and one for target stages). The technically correct approach is to determine the link records in DS_JOBOBJECTS, on which the DSRIDs of the stages connected by the link are recorded, then retrieve those.

If you have a link naming convention (for example target-writing links have names ending in "_OUT") this would be even easier.