Re: using decode in procedure

From: Mark <markg_at_mymail.co.uk>
Date: 11 Apr 2002 01:41:47 -0700
Message-ID: <ddb31653.0204110041.1f3fa4d4@posting.google.com>

You seem like you are new to Oracle, hence the INNER JOIN etc syntax, Forgive me if you are not.

If you havent declared your SQL as a cursor, you need to select your values 'into' variables or a record.

Assuming your are expecting ONE row from your query try the code below. If not, i suggest you find an Oracle PL/SQL book and look at Cursors and Cursor For Loops.

DECLARE

   v_Parent_Organisations VARCHAR2(100);
   v_divisions  VARCHAR2(100);
   v_division_codes  NUMBER; -- ???

BEGIN   Select

     decode (a.Description, 'Company Wide','Corporate',a.Description) Parent_Organisations,

     c.Description Divisions,
     c.Code Division_Codes
  INTO
     v_Parent_Organisations,
     v_divisions ,
     v_division_codes 

  FROM tblLUBusinessGroup a, tblDivisionLink b, tblLUDivision c   WHERE b.Business_Group_Code = a.Code
  AND c.Code (+) = b.Division_Code
  ORDER BY
     a.Description, c.Description;

  ..
  ..
  ..

END; Hope the above guides you.

M   

novaweb_at_iinet.net.au (Paul Fell) wrote in message news:<4b270a4e.0204101856.49287f0b_at_posting.google.com>...
> Oracle 9i
>
> I want to use the following :
>
> Select decode (a.Description, 'Company
> Wide','Corporate',a.Description) Parent_Organisations,
> c.Description Divisions,
> c.Code Division_Codes
> from tblLUBusinessGroup a
> INNER JOIN tblDivisionLink b ON b.Business_Group_Code = a.Code
> LEFT JOIN tblLUDivision c ON c.Code = b.Division_Code
> Order By a.Description, c.Description;
>
> This same code works in SQL*Plus, but when I try to compile in a
> procedure I get the error message :
>
> "PLS-00428: an INTO clause is expected in this SELECT statement"
>
> The decode is meant to return 'Corporate' where the value is 'Company
> Wide' otherwise default to the value of the field.
>
> Any help much appreciated.
>
> thanks
> Paul
Received on Thu Apr 11 2002 - 03:41:47 CDT