Using result from BEFORE-SQL in Input SQL

[JNG]

In my INPUT Stage, I need to select data from a table all rows that are loaded in the current month. I have a separate date control table where I have a row for each month. The max date on the date control table gives the current month capture date.

ie. SELECT ...
FROM FACT
WHERE CAPTR_DT = (SELECT MAX(CAPTR_DT)
FROM MONTH_CONTROL)

In my process, I need to UNION multiple SELECT statements extracting data from various data tables, all with the condition to select only current month data. So I need to code the sub-select in each SELECT.

My question is - is it possible to execute the MONTH_CONTROL SELECT as a BEFORE SQL, and somehow pass the value to the USER-DEFINED SQL, and use that as a variable in the SQL, instead of executing the subquery for every SELECT statement in the UNION?

Thanks.
Jng

[kcbland]

No, the BEFORE/AFTER SQL is meant more for statements such as session variables or stored procedure calls. There is no ability to use it as an extension of the SQL statement.

[kduke]

You could load this into a hash file and look it up instead of doing this on every row.

[JNG]

You could load this into a hash file and look it up instead of doing this on every row.

How can I do the lookup in the INPUT stage? I only know how to do lookup from a TRANSFORM stage. Any help is appreciated.

Thanks.

[datastage]

maybe I am misunderstanding your requirement here, but it looks to me you have the answer in front of you...can't you continue to pass the max month select in the where clause like you demonstrated within each select you need to union? It seems redundant but a simple max() select from a control table should execute in a second (or less) so it seems no big deal to have that subselect embedded in muliple wheres and not worry about trying to find a purist way of doing it once and passing it in.

hope this helps.

[ray.wurlod]

Think about the amount of work you're asking DataStage to do.

How many rows are selected by this query?

Code: Select all

SELECT ... 
FROM FACT 
WHERE CAPTR_DT = (SELECT MAX(CAPTR_DT) 
FROM MONTH_CONTROL) 

How many rows are selected by this query?

Code: Select all

SELECT ... 
FROM FACT 

then doing a lookup for every row within DataStage.

Now answer the same questions at a date 12 months from now.

Performance Rule #2: Don't process any data you don't need to.