Menu
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.
746
Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
Just declare the variable on one line (no semicolon), then the exec line to set its value (end with semicolon), then your select statement. Finally, run it as a script (F5), not as a statement (F9).
To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
To use variables in a SQL query, the query must be written as a formula, starting with the equals (=) sign and containing strings concatenated with the ampersand (&). You can use variables in place of a table name or combine them with the WHERE clause, in place of filter values.
try this:
variable var DATE
exec :var := '15-OCT-13'
and then your select with using :var in it
82
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
