DSXchange

Hi,

One of DRS stage is using a user-defined query as shown below.
When I ran the job, its not throwing any error message and I am able to run the job successfully.

But the above said DRSstage is not listing any rows. I am very curious to know how this user-defined query works. Actually in the WHERE clause of the SELECT statement is having '?' in all the conditions.

Could you please suggest me how will it pick up the rows?

SELECT
LTrim(RTrim(A.EMPLID)),
LTrim(RTrim(A.ACAD_CAREER)),
%DateTimeOut(A.EFFDT),
LTrim(RTrim(A.SRC_SYS_ID)),
LTrim(RTrim(A.ACAD_PROG)),
LTrim(RTrim(A.PROG_ACTION)),
LTrim(RTrim(A.PROG_STATUS)),
LTrim(RTrim(A.PROG_REASON))
FROM
#$OWS_SCHEMA#PS_ACAD_PROG A
WHERE
A.EMPLID=? AND
A.ACAD_CAREER=? AND
A.EFFDT= (SELECT MAX(A1.EFFDT)
FROM #$OWS_SCHEMA#PS_ACAD_PROG A1
WHERE A1.EMPLID=A.EMPLID
AND A1.ACAD_CAREER=A.ACAD_CAREER
AND A1.ACAD_PROG=A.ACAD_PROG
AND A1.EFFDT <=%DateTimeIn(?)
AND A1.SRC_SYS_ID=A.SRC_SYS_ID) AND
A.EFFSEQ =
(SELECT MAX(A2.EFFSEQ)
FROM #$OWS_SCHEMA#PS_ACAD_PROG A2
WHERE A2.EMPLID=A.EMPLID
AND A2.ACAD_CAREER=A.ACAD_CAREER
AND A2.ACAD_PROG =A.ACAD_PROG
AND %DatePart(A2.EFFDT) =%DatePart(A.EFFDT)
AND A2.SRC_SYS_ID=A.SRC_SYS_ID) AND
A.SRC_SYS_ID=? AND
A.ACAD_PROG=?

Thanks in advance.

Each ? is what is called a "parameter" marker and corresponds to a column in your job design on the link to which the SQL applies. If the column is marked as Key the parameter marker corresponding to it goes into the WHERE clause. Non-key columns match match in data type and number with the columns named in the SELECT clause.

you can also try and test the query outside of datastage by replacing each '?' by the actual values from the input.
if that query returns any rows, then investigate further with datastage, else the data that you are trying to fetch is failing to meet some condition in your user defined SQL.

rgds,
Nik

How does it distinguish between multiple '?' markers?. If I have 5 '?' markers and 5 values coming from the input link then how does it map the incoming values to the '?' markers? just basing on the data types?

Positional. First column mapped to first ?, etc, with adjustments made for Key versus non-Key columns.