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.
How does a user-defined query works?
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 20
- Joined: Fri May 16, 2008 3:00 am
- Location: bangalore
How does a user-defined query works?
Thanks,
Tirumal G
Tirumal G
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
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
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
-
- Premium Member
- Posts: 34
- Joined: Fri May 16, 2008 6:24 am