All Forums Database
hobart1010 2 posts Joined 04/16
20 Apr 2016
Query that requires optional prompts

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,
 
Sean

Sean
AtardecerR0j0 71 posts Joined 09/12
21 Apr 2016

You could use this code for the optional parameter:
coalesce(?ORDERID, ORDER_ID)
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

Be More!!

yuvaevergreen 93 posts Joined 07/09
21 Apr 2016

What do you mean by optional?...user's option or can it be null...

hobart1010 2 posts Joined 04/16
22 Apr 2016

AtardecerR0j0 -- thank you, but that did not work.  Let me elaborate:
 
Let's say we have one table with two columns:
 
tbl1
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:
ORDER_ID                      SALES_REP
1                                    5
 
User wants flexibility in the code so that s/he can explore difference scenarios.
 
Thank you again for any help.
 
Sean

Sean

yuvaevergreen 93 posts Joined 07/09
25 Apr 2016

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.
 
 

You must sign in to leave a comment.