Re: Variable puzzle, cannot use a variable in a where clause

Phil wrote:

> Hi,
> I try to do something like select x from y where z = variable.
> I cannot get it to work and could not find any example or info.
>
> As an example the code bellow works as far as setting the variable
> value is concerned however it returns an error.
> ORA-06550: line 11, column 1:
> PLS-00428: an INTO clause is expected in this SELECT statement.
> When I try to use the variable in a select clause.
> Thanks,
> Phil
>
> Declare my_Date number(6);
> begin
> select
> TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(a.fiscal_yyyymm_num),
> 'YYYYMM'), 3),'YYYYMM'))
> Into my_Date
> FROM dataware.fiscal_calendar a
> WHERE a.query_dt = TRUNC(sysdate);
>
> --dbms_output.put_line (my_Date);
>
> Select * from dataware.Orders o where o.ShipSchedule_yyyymm_num =
> my_Date;
>
> end;

Do you think someone should be able help you based on what you posted?

1. What is the data type of the column fiscal_yyyymm_num?
2. What does the data in that column look like?

For all we know it is a NUMBER(10) with the value 666 in it.

But being generous and assuming it contains 200412

It seems to work just fine:

CREATE TABLE t (
fiscal_yyyymm_num NUMBER(6),
query_dt DATE);

INSERT INTO t VALUES (200412, TRUNC(SYSDATE)); COMMIT; DECLARE
  my_Date number(6);
BEGIN
   SELECT
TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fiscal_yyyymm_num), 'YYYYMM'), 3),'YYYYMM'))
   INTO my_Date
   FROM t
   WHERE query_dt = TRUNC(sysdate);

   dbms_output.put_line (my_Date);
/*
Select * from dataware.Orders o where o.ShipSchedule_yyyymm_num = my_Date;
*/
END;
/

With the exception of the final SQL statement which as you know from your earlier statement must be changed into a SELECT INTO.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)