Oracle SQL reuse User Input

Question

I basically want to reuse an input to save the user from inputting same thing again..

Here's how the query looks like:

select * from table
where bb.bbn_from_dttm >= TO_DATE('&Bill_Date_ddmmyyyy','dd-mm-yyyy')
and bb.bbn_to_dttm <= LAST_DAY('&Bill_Date_ddmmyyyy');

I don't want the user inputting this twice and getting the value from the first input would give me the date range I need.

This is SQL Oracle.

Any clues?

use '&&Bill_Date_ddmmyyyy' at both the places in your code. -- select * from table where bb.bbn_from_dttm >= TO_DATE('&&Bill_Date_ddmmyyyy','dd-mm-yyyy') and bb.bbn_to_dttm <= LAST_DAY('&&Bill_Date_ddmmyyyy'); — Popeye
– Popeye, Commented Jul 4, 2019 at 15:16
@Tejash----ORA-00980 Wouldn't this fail the second time you run the query as it would not ask for input? Also if so Could you change the original query to how you suggest it - would be helpful. — zuNEC
– zuNEC, Commented Jul 4, 2019 at 15:19
In a single session, it will not ask for the input again. If you run it in another session then only it will ask you for input again. — Popeye
– Popeye, Commented Jul 4, 2019 at 15:22
Making WHERE condition as bb.bbn_to_dttm between date'&&Bill_Date_yyyy_mm_dd' and date'&&Bill_Date_yyyy_mm_dd' without less, greater and equal signs, and entering the value as 2019-07-04(conforming to ISO standard) is enough whenever prompted. — Barbaros Özhan
– Barbaros Özhan, Commented Jul 4, 2019 at 16:00

user5683823 · Accepted Answer · 2019-07-04 16:44:37Z

1

Note the use of an inline view to convert the input string into a date (just once for the entire query) - then the rest of the query uses the date created in this inline view, not the user's input. Change & to && if you need the query to use the same input whenever run in the same session (although in most cases that's not what users would want).

select bb.* 
from   my_table bb
       join
       ( select TO_DATE('&Bill_Date_ddmmyyyy','dd-mm-yyyy') as bill_date
         from   dual
       ) d
  on   bb.bbn_from_dttm >= d.bill_date and bb.bbn_to_dttm <= LAST_DAY(d.bill_date);

answered Jul 4, 2019 at 16:44

user5683823

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL reuse User Input

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related