Re: Help with variables in PL/SQL
Date: Fri, 20 Jun 2003 01:07:33 GMT
Message-ID: <Xns939FD6B2A19BFpobox002bebubcom_at_204.127.204.17>
swayzack1_at_yahoo.com (Morgan Clark) wrote in news:11203f39.0306191037.3e9758fb_at_posting.google.com:
> Hi... I'm trying to do what I consider to be a real simple thing, but
> I can't get it to work right! I have a query that I'm going to have
> to run every now and then - I just have it saved as a text file that
> I'm going to copy and paste into SQL/Plus when I need to. Not the
> best way, I know, but we have very limited permissions to the actual
> database (it's a data warehouse hosted offsite)...
>
> In my query, I look at a certain date a bunch of times. I'd love to
> make it a variable. In T-SQL (what I'm used to), I could do something
> like:
> DECLARE _at_theDate datetime
> SET _at_theDate = '5/30/2003'
>
> SELECT whatever
> FROM wherever
> WHERE thisdate > function(_at_theDate) AND thatdate <= function(@theDate)
>
> ... point is, I reference the same date in the SELECT a whole bunch
> of times, and it'd be nice to just set it once. The research I've
> done on Oracle all has examples of declaring a variable, then using
> SQL that either returns one row, or manipulates data in some way -
> nothing that just returns a set of data in a SELECT. I've read about
> cursors, but they seem to be used just to loop through the results and
> manipulate the data... I just want to display the result of a SELECT
> statement that I'm using a variable in. Can somebody help me out???
> I know this is a stupid question, and I apologize, but it's such a
> stupid question that it's tough to find the answer to....... Thanks.
If you want to keep the query as SQL and avoid writing procedural code, you can use an in line view. In this example the date is selected from dual and aliased to d, which can then be used anywhere in the query.
In your example you compare a string to a date which you shouldn't do though.
SQL> select object_id, d + 1, d - 1, 2 add_months(d, 3), trunc(d,'Q') 3 from all_objects,
4 (select to_date('06192003','MMDDYYYY') d from dual) 5 where created > add_months(d,-50)
6 and rownum < 4;
OBJECT_ID D+1 D-1 ADD_MONTH TRUNC(D,'
---------- --------- --------- --------- ---------
17286 20-JUN-03 18-JUN-03 19-SEP-03 01-APR-03
7559 20-JUN-03 18-JUN-03 19-SEP-03 01-APR-03
22542 20-JUN-03 18-JUN-03 19-SEP-03 01-APR-03
If you want to do it in PL/SQL then you can look up date variables in the PL/SQL User's Guide and Reference in the documentation at http://tahiti.oracle.com
-- Martin BurbridgeReceived on Fri Jun 20 2003 - 03:07:33 CEST