Here is what I am trying to do... I am designing an extract process to export cusomters and orders. The input will be list of customers and a date range.
The first thing it has to do is find all the orders in that data range for that group of customers. Query on the order table.
The 1st output is a distinct list of all the customers from Query 1 with additional info from the Customer and CustomerAddress table (if a customer has no orders in the period defined it is not returned - eventhough it was part of the input).
The 2nd output is the Order Data from Qury1 with some other details from associated table.
The 3rd through 8th output are various categories of order details that may or may not exist for a particular Order. Each of these has to join to Query 1 to make sure we only get the relevant order details.
What I have now is a view for Query1, and 8 views for the outputs. The calling software (that is building the outut file) makes 8 calls each one of which joins to Query1 with the same WHERE clause (date range and list of customers).
The perfomance opportunity I see here is that Query1 runs 8 times in this scenario. My thought is to put the results of Query1 into a temp table (volatile) and then join to that 8 times. I would like to consolidate this into a single call from the application.
I have thought of using a macro to do the eight outputs, but macros cannot create temporary tables or a stored procedure, but that cannot output the data AND cannot call the macro.
I am pretty new to Teradata, but I think the info I have in the previous paragraph is correct... please help if it is not.