I want to make a derived table with a query to achieve something like this -
Select A.date,A.order,A.name from A where A.date = @prompt(‘enter date’…)
union
Select B.date,B.order,B.name from B where B.order = A.order (orders fetched from table A for the input A.date, and not for the same date)
The issue is, I just cannot make a view on the database with a union as well -
Select A.date,A.order,A.name from A
union
Select B.date,B.order,B.name from B
because if I create a ‘condition object’ over this view for say a date, then it would apply a filter on the entire view, which I do not want.
Can you please guide me towards the approach I should follow for this? Can I use cascaded prompts in this case? If yes, then how?
No no problems creating a derived table, but I had a different issue, it is to do with the the prompts and how to pass the values entered in one prompt of a table used and pass it on to other table, if you have a look at my first post you should get a fair idea.
Thank you.
You are correct, this can be acheived through derived table, with a query something like this :
Select B.date,B.order,B.name from B
where B.order in (Select A.order from A where A.date = @prompt(‘enter date’…) )
UNION
Select A.order from A where A.date = @prompt(‘enter date’…)
Now this leads to the other requirement that user has, he doesn’t want this prompt (@prompt(‘enter date’…) )
) compulsarily i.e. he wants a seperate ‘condition object’ in the universe which he can select and based on the date he enters it should fetch the information from table A and then select the information for the orders corresponding to the input date from table B ?
In the above approach the prompts are built within the derived table, so it doesn’t give the user with the option.
By that I meant, the user wants to see a prompt, but he wants to choose the prompt whenever he needs it, so for that he wants a condition object (a predefined filter at the universe level), on choosing this filter it would prompt the user to ‘enter date’, and based on this date further processing would happen, as explained in my previous post.
So we do not want the '@prompt(‘enter date’…) ’ in the derived table query, instead have it seperately as a condition object.
Change your ETL process such that you will not have to come up with the Derived tables in the first place - probably have an aggregate table or a view with the required data.
Provide a prompt to the User, but he will have the option of choosing ALL or a value for the filter. Check the FAQ FAQ: Designer for details.
Any problem with creating a Derived table?
