Declaring & Setting Variables in a Select Statement

Declare a variable with SELECT SQL statement to be used in the WHERE clause

You can use APPLY to create something like a named variable row-wise. If the value is calculated once and is valid for the whole set (more like a constant), you might use a CTE (starting with WITH before the SELECT).

The following will use an APPLY to find the table's name for all columns. You can use The.ImportantVariable in the column list as well as in a WHERE-clause:

SELECT The.ImportantVariable
      ,c.*
FROM sys.columns AS c
OUTER APPLY(SELECT name FROM sys.objects AS o WHERE c.object_id=o.object_id) AS The(ImportantVariable)
WHERE The.ImportantVariable LIKE 'a%';

UPDATE Compare CTE and APPLY

Check this! There are some constant values in 1 single row provided by the CTE which you can CROSS JOIN into your query and use as named constants

WITH SomeConstants AS
(
    SELECT 'I''m a constant value' AS Constant1 --might be a complex statement too!
           ,0 AS Constant2
)
SELECT The.ImportantVariable
      ,Constant1
      ,Constant2
      ,c.*
FROM SomeConstants
CROSS JOIN sys.columns AS c
OUTER APPLY(SELECT name FROM sys.objects AS o WHERE c.object_id=o.object_id) AS The(ImportantVariable)
WHERE The.ImportantVariable LIKE 'a%'

Declaring & Setting Variables in a Select Statement

From the searching I've done it appears you can not declare and set variables like this in Select statements. Is this right or am I missing something?

Within Oracle PL/SQL and SQL are two separate languages with two separate engines. You can embed SQL DML within PL/SQL, and that will get you variables. Such as the following anonymous PL/SQL block. Note the / at the end is not part of PL/SQL, but tells SQL*Plus to send the preceding block.

declare 
    v_Date1 date := to_date('03-AUG-2010', 'DD-Mon-YYYY');
    v_Count number;
begin
    select count(*) into v_Count
    from Usage
    where UseTime > v_Date1;
    dbms_output.put_line(v_Count);
end;
/

The problem is that a block that is equivalent to your T-SQL code will not work:

SQL> declare 
  2      v_Date1 date := to_date('03-AUG-2010', 'DD-Mon-YYYY');
  3  begin
  4      select VisualId
  5      from Usage
  6      where UseTime > v_Date1;
  7  end;
  8  /
    select VisualId
    *
ERROR at line 4:
ORA-06550: line 4, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement

To pass the results of a query out of an PL/SQL, either an anonymous block, stored procedure or stored function, a cursor must be declared, opened and then returned to the calling program. (Beyond the scope of answering this question. EDIT: see Get resultset from oracle stored procedure)

The client tool that connects to the database may have it's own bind variables. In SQL*Plus:

SQL> -- SQL*Plus does not all date type in this context
SQL> -- So using varchar2 to hold text
SQL> variable v_Date1 varchar2(20)
SQL>
SQL> -- use PL/SQL to set the value of the bind variable
SQL> exec :v_Date1 := '02-Aug-2010';

PL/SQL procedure successfully completed.

SQL> -- Converting to a date, since the variable is not yet a date.
SQL> -- Note the use of colon, this tells SQL*Plus that v_Date1
SQL> -- is a bind variable.
SQL> select VisualId
  2  from Usage
  3  where UseTime > to_char(:v_Date1, 'DD-Mon-YYYY');

no rows selected

Note the above is in SQLPlus, may not (probably won't) work in Toad PL/SQL developer, etc. The lines starting with variable and exec are SQLPlus commands. They are not SQL or PL/SQL commands. No rows selected because the table is empty.

Declaring a variable in a SELECT statement

There are no variables in SQL at all. Just tables, columns and expressions.
So, obviously you cannot "declare a variable" to begin with. The wrong terminology easily inspires an inadequate approach ...

Also, dateadd() and getdate()? You may be thinking of MS SQL Server. Those functions do not exist in Postgres.

Either way, use a subquery (or a CTE) to build new expression from constant input:

CREATE TEMP TABLE tmp1_variables AS (
SELECT *, end_date - start_date AS new_variable
FROM  (
   SELECT date '2016-10-29' AS start_date
        , CURRENT_DATE - 10 AS end_date
        , date '2015-10-31' AS date1
   ) sub
);

Displaying valid Postgres syntax.

In Postgres, you can just add / subtract integer to / from a date (but not a timestamp).

And if you add an interval to a date, you get a timestamp.

Of course, you can cast the result back to date:

(date1 + new_variable + interval '1 days')::date

But rather compute a date to begin with:

date1 + new_variable + 1

Aside: unquoted identifiers are cast to lower case in Postgres.

Set Variable for Select Result in SQL Server

Your syntax is wrong for the SET

It should be

DECLARE @bDate DATE;
SET @bDate = (SELECT birth_date FROM person WHERE id='1');

SELECT @bDate;

OR, you can use SELECT

DECLARE @bDate DATE;
SELECT @bDate = birth_date FROM person WHERE id='1';

SELECT @bDate;

Select and assign to variable in one statement?

Unfortunately it has to be done in two operations.

Test:

DECLARE @VAR DATETIME
SELECT @VAR=GETDATE(), GETDATE()

Yields error message 141.

Here's another SO post on this.

Declaring a variable and setting its value from a SELECT query in Oracle

SELECT INTO

DECLARE
   the_variable NUMBER;

BEGIN
   SELECT my_column INTO the_variable FROM my_table;
END;

Make sure that the query only returns a single row:

By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure your WHERE clause is specific enough to only match one row

If no rows are returned, PL/SQL raises NO_DATA_FOUND. You can guard against this exception by selecting the result of an aggregate function, such as COUNT(*) or AVG(), where practical. These functions are guaranteed to return a single value, even if no rows match the condition.

A SELECT ... BULK COLLECT INTO statement can return multiple rows. You must set up collection variables to hold the results. You can declare associative arrays or nested tables that grow as needed to hold the entire result set.

The implicit cursor SQL and its attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN provide information about the execution of a SELECT INTO statement.

---

Related Topics