Re: Loading and using variables

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.
Received on Tue Sep 12 2000 - 12:35:33 CDT