CODEWITHSUNDEEP
sqloracle-databasevariablesselectdeclare
Kairan
Kairan

Reputation: 5542

Declare a variable in Oracle SQL to use in a query

Hi I am trying to declare a variable to use in Oracle SQL select query as such:

DECLARE 
  myDate DATE;
BEGIN
SELECT Source as "Source", DT as "Date", Status as "Status", COALESCE("Count", 0) as "Count"
FROM (Huge SubQuery that includes many WHERE date between x and y);
END;

I need to use myDate for the query so I dont have to update it in 10 places everytime I run the query. Basically its just for declaring a variable that can be used in a where date is between clause in several places.

Upvotes: 8

Views: 46749

Answers (2)

Indika Herath
Indika Herath

Reputation: 39

I have found a way to add variables for the sql query as follows

DEFINE RES_DT = TO_DATE('11-AUG-15');

And also to access the variable through the query we have to use '&' notation as follows

select * from customer where assign_date = &RES_DT;

Upvotes: 2

Hamidreza
Hamidreza

Reputation: 3118

try this:

variable var DATE
exec :var := '15-OCT-13'

and then your select with using :var in it

Upvotes: 3

Related Questions