Re: Loading and using variables

That would work but I guess my confusion is that once I get the information from the first query into the variable I just want to use it in a query that returns information to the screen. If I change the second query to read:

SELECT COUNT(*) from Table2 where criteria = criteria;

Then I get the result printed back to the screen for me, why if I use the variable does the output have to go anywhere other than the screen. That's the part that's confusing me :)

Thanks

-Paul-

In article <FPtv5.5337$oc3.286331_at_news.flash.net>,   "Eric Givler" <egivler_at_flash.net> wrote:
> How about?
>
> variable result varchar2(32)
> variable mycount number
>
> BEGIN
> SELECT DISTINCT(Data)
> into :result
> FROM TABLE1
> WHERE Criteria = OtherCriteria;
>
> SELECT COUNT(*)
> into :mycount
> from TABLE2 WHERE Criteria = :Result
> END;
> /
>
> print result
> print mycount
>
> <paul_at_davishome.com> wrote in message news:8pk6is$4b4
 $1_at_nnrp1.deja.com...
> > I need to write a PL/SQL routine for the office that loads a field
 from
> > a database, inserts into a variable and then uses it later on in
> > another query later in the SQL block. Here is an example of what I
 mean:
> >
> > DECLARE
> > Result VARCHAR2(32)
> > BEGIN
> > SELECT DISTINCT(Data)
> > FROM TABLE1
> > INTO Result
> > WHERE Criteria = OtherCriteria;
> >
> > SELECT COUNT(*) from TABLE2 WHERE Criteria = Result
> > END;
> >
> > The reason for doing this is that the Select Count is actually a big
> > monster query to get the latest results from a number of tables, the
> > Result is the latest identifier and changes each month. I don't
 want to
> > update the query each month and I certainly don't want to add
 another
> > sub-query into a routine that we spent time optimizing :)
> >
> > The above code generates an error on the final line of code,
> > complaining about wanting an INTO statement. I'm stumped on this one
> > and can use any help available.
> >
> > Cheers
> >
> > -Paul-
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Sep 12 2000 - 13:48:29 CDT