Loading and using variables

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 Mon Sep 11 2000 - 22:06:16 CDT