We have a query where the rows returned depend on a date range. Here is an example:
SELECT a.* FROM TABLE1, INNER JOIN TABLE2 ON TABLE1.pty_id = TABLE2.pty_id AND TABLE1.evt_end_dte between TABLE2.stt_dte and TABLE2.end_dte where TABLE1.evt_end_dte between '2010-10-01' and '2010-10-31' QUALIFY ROW_NUMBER() OVER (PARTITION BY pty_id ORDER BY evt_end_tme) = 1
We would like to turn this into a view definition, but I'm not sure that can be done. I believe we would have to call a macro that inserts the rows into a global temporary table and then reads from that table.
Has anyone run into this situation before and if so, can anyone suggest a good way to do this?
Depends how and when you want the date to change! If the dates are always last month - based on calendar dates, the following will work:
SELECT Table1.* FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.pty_id = TABLE2.pty_id AND TABLE1.evt_end_dte between TABLE2.stt_dte and TABLE2.end_dte -- where TABLE1.evt_end_dte between '2010-10-01' and '2010-10-31' Where Table1.Evt_End_Dte Between (Add_Months(Current_Date,-1)/100*100)+1 And ((Current_Date/100*100)+1 (Date)) - 1 QUALIFY ROW_NUMBER() OVER (PARTITION BY Table1.pty_id ORDER BY evt_end_tme) = 1 ;
You can put this in a view and it will work (at least in V2R6 onwards). Older levels used to evaluate the current date at time of view creation and put it in the view code; hence it did not update the dates when used next month.
You could get problems with this approach at month end. If your loads are behind (or some of them), you could get problems when the calendar date changes but some data is not yet loaded. The approach commonly used is to hard code the dates (but use a generation process for the views to update dates) after the data is updated at month-end.
I'm finally getting back to this topic. Is there a way to do this with a Macro, volatile table, or global temporary table? The challenge is that the dates are passed in by Business Objects. So, we'd like to take those dates and then build an object on the fly within Teradata. Today, we do this through a derived table in Business Objects. However, the business logic we use is changing. We'd rather do it in Teradata so that we change it in one place rather than change it everywhere it's used in Business Objects.
Of course you can use a macro to pass dates into a select, this is exactly what they're made for: replace macro foobar (start_dte date, end_date date) as ( SELECT a.* FROM TABLE1, INNER JOIN TABLE2 ON TABLE1.pty_id = TABLE2.pty_id AND TABLE1.evt_end_dte between TABLE2.stt_dte and TABLE2.end_dte where TABLE1.evt_end_dte between :start_dte and :end_date QUALIFY ROW_NUMBER() OVER (PARTITION BY pty_id ORDER BY evt_end_tme) = 1; );
You can use Global Temp Tables, too: http://developer.teradata.com/blog/dnoeth/2011/03/global-and-session-level-parameters-in-sql