A user desires a query to provide prompts (using the question mark+name_of_prompt) and some of the prompts should be optional. Example:
SELECT * FROM ORDERS
WHERE ORDER_ID = ?ORDERID? /* this can be optional */
AND SALES_RE; = ?SALESREP /* not optional */
How can this be done in Teradata please?
Thank you, in advance,
You could use this code for the optional parameter:
It will be always asked, then you should introduce your value (if you have it) or null
It will works only for NOT NULL columns
AtardecerR0j0 -- thank you, but that did not work. Let me elaborate:
Let's say we have one table with two columns:
column 1: ORDER_ID
column 2: SALES_REP
Column 1 values: ('1','2','3','4')
Column 2 values: ('5','6','7','8')
User wants to have two "where" clauses in the query.
SEL * FROM TBL1
WHERE ORDER_ID = (or includes) <list values> -- this is a mandatory prompt
AND / OR SALES_REP = (or includes) <list values> -- this is an optional prompt
In this example, the ORDER_ID value would be entered as "1", and nothing would be entered for the SALES_REP. The results would return the record:
User wants flexibility in the code so that s/he can explore difference scenarios.
Thank you again for any help.
I think SP would be a better option.
Check the input,
If the input is only one, then execute the dynamic SQL based on only one input.
If its two, execute the dynamic SQL based on two inputs.