Declaring Setting Variables in a Select Statement

Question

I m attempting to write a simple query where I declare some variables and then use them in a select statement in Oracle   I ve been able to do this before in SQL Server with the following   DECLARE  date1   DATETIME SET  date1    03-AUG-2010   SELECT U VisualID FROM Usage u WITH NOLOCK  WHERE U UseTime  gt   Date1   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 mssing something

User · Answer

I have tried this and it worked   define PROPp START DT   TO DATE  01-SEP-1999    select   from proposal where prop start dt    amp PROPp START DT    nbsp

User · Answer

The SET command is TSQL specific - here s the PLSQL equivalent to what you posted   v date1 DATE    TO DATE  03-AUG-2010    DD-MON-YYYY     SELECT u visualid   FROM USAGE u   WHERE u usetime  gt  v date1    There s also no need for prefixing variables with      I tend to prefix variables with  v   to distinguish between variables  amp  columns etc   See this thread about the Oracle equivalent of NOLOCK

User · Answer

Coming from SQL Server as well  and this really bugged me  For those using Toad Data Point or Toad for Oracle  it s extremely simple  Just putting a colon in front of your variable name will prompt Toad to open a dialog where you enter the value on execute  SELECT   FROM some table WHERE some column    var name

User · Answer

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  gt  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 gt  declare    2      v Date1 date    to date  03-AUG-2010    DD-Mon-YYYY      3  begin   4      select VisualId   5      from Usage   6      where UseTime  gt  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 gt  -- SQL Plus does not all date type in this context SQL gt  -- So using varchar2 to hold text SQL gt  variable v Date1 varchar2 20  SQL gt  SQL gt  -- use PL SQL to set the value of the bind variable SQL gt  exec  v Date1     02-Aug-2010    PL SQL procedure successfully completed   SQL gt  -- Converting to a date  since the variable is not yet a date  SQL gt  -- Note the use of colon  this tells SQL Plus that v Date1 SQL gt  -- is a bind variable  SQL gt  select VisualId   2  from Usage   3  where UseTime  gt  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

User · Answer

Try the to date function

[sql] Declaring & Setting Variables in a Select Statement

Examples related to sql

Examples related to oracle

Examples related to variables

Examples related to plsql