Query that requires optional prompts

Database

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

4 REPLIES
Teradata Employee

Re: Query that requires optional prompts

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

Enthusiast

Re: Query that requires optional prompts

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

Re: Query that requires optional prompts

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

Enthusiast

Re: Query that requires optional prompts

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.