9i JOINS in PL/SQL

From: Roger Stapley <roger.stapley_at_oracle.com>
Date: Tue, 18 Dec 2001 11:20:22 +0000
Message-ID: <3C1F2676.53743146@oracle.com>

Folks,

I have the following query :

    SELECT
    DISTINCT

            rsua.ROWID rsua_rowid


            ,site_use_id

            ,address_id

            ,customer_id

            ,DECODE(receipt_method_id, NULL, 'FALSE', 'TRUE')

in_trx_or_not

    FROM ra_site_uses_all rsua

     INNER
     JOIN   ra_addresses_all raa
     USING  (address_id)
     INNER
     JOIN   ra_customers_all rca
     USING  (customer_id)
     LEFT
     OUTER
     JOIN   ra_customer_trx_all rcta
     ON     rcta.bill_to_site_use_id = rsua.site_use_id
    WHERE   rsua.site_use_code = 'BILL_TO'
    AND     rsua.org_id = 378219
    ORDER
    BY      customer_id


            ,DECODE(receipt_method_id, NULL, 'FALSE', 'TRUE') DESC;

Works fine and returns the expected results on several 9i databases.

When I wrap it in a PL/SQL routine it errors :

DECLARE
  CURSOR cs_main IS
    SELECT
    DISTINCT

            rsua.ROWID rsua_rowid


            ,site_use_id

            ,address_id

            ,customer_id

            ,DECODE(receipt_method_id, NULL, 'FALSE', 'TRUE')

in_trx_or_not

    FROM ra_site_uses_all rsua

     INNER
     JOIN   ra_addresses_all raa
     USING  (address_id)
     INNER
     JOIN   ra_customers_all rca
     USING  (customer_id)
     LEFT
     OUTER
     JOIN   ra_customer_trx_all rcta
     ON     rcta.bill_to_site_use_id = rsua.site_use_id
    WHERE   rsua.site_use_code = 'BILL_TO'
    AND     rsua.org_id = 378219
    ORDER
    BY      customer_id


            ,DECODE(receipt_method_id, NULL, 'FALSE', 'TRUE') DESC;

BEGIN
  FOR r_main IN cs_main
  LOOP
    NULL;
  END LOOP;
END;

ORA-06550: line 11, column 6:
PLS-00103: Encountered the symbol "INNER" when expecting one of the
following:

   , ; for group having intersect minus order start union where    connect

Any ideas - which don't involve rewriting the queries - is there a patch for PL/SQL?

Thanks.
Roger

Received on Tue Dec 18 2001 - 05:20:22 CST