Avatar of katiep23
katiep23
 asked on

Need help with Pro*C Query error

I have a query that runs fine in Toad, but I get an error that says "Error at line 225, column 15 in file LANBLOracle.sqc
             (with oracleholds as (SELECT DISTINCT oeh.order_number, ohd.NAME
..............1
PCC-S-02201, Encountered the symbol "with" when expecting one of the following:
   ( select, multiset, the,"

Line 225 is "             (with oracleholds as (SELECT DISTINCT oeh.order_number, ohd.NAME"

Any ideas what the problem could be?  Thanks!

Here's the query:

      SELECT
            A.JOINKEY AS FULLKEY,
             A.SHIPPED_QUANTITY AS ONTQTY,
             B.QUANTITY_INVOICED AS ARQTY,
            A.TRV_FLAG,
            A.ZIC_FLAG
         FROM
             (with oracleholds as (SELECT DISTINCT oeh.order_number, ohd.NAME
                   FROM            ont.oe_order_headers_all oeh,
                                        ont.order_lines oel,
                                   ont.hold_sources ohs,
                                   ont.order_holds oha,
                                   ont.hold_definitions ohd
                        WHERE        oeh.header_id = oel.header_id
                        AND          oeh.header_id = ohs.hold_entity_id
                        AND          ohs.hold_id = ohd.hold_id
                        AND          oha.order_hold_id = ohd.hold_id
                        AND          ohd.NAME IN
                  ('TRV HOLD',
                   'ZIC HOLD'
                   ))
      SELECT  
                   JOINKEY,  
                     OEL.SHIPPED_QUANTITY,  
                               case when OEH.ORDER_NUMBER IN (SELECT order_number from oracleholds where name = 'ABL TRV HOLD') then 'X' else '' end as TRV_FLAG,
                               case when OEH.ORDER_NUMBER IN (SELECT order_number from oracleholds where name = 'ABL ZIC HOLD') then 'X' else '' end as ZIC_FLAG
                 FROM
                     ONT.ORDER_LINES OEL,
                                  ONT.ORDER_HEADERS OEH,
                     WSH.DELIVERY_DETAILS WDD LEFT OUTER JOIN
                                  WSH.DELIVERY_ASSIGNMENTS WDA ON
         WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
         LEFT OUTER JOIN
         WSH.WSH_NEW_DELIVERIES WND ON
         WDA.DELIVERY_ID = WND.DELIVERY_ID
                 WHERE  
                     OEL.LINE_ID = WDD.SOURCE_LINE_ID                AND
                               OEL.HEADER_ID = OEH.HEADER_ID  ,  
             (
                 SELECT DISTINCT  
                     JOINKEY,  
                     RAL.QUANTITY_INVOICED
                 FROM  
                     AR.CUSTOMER_LINES RAL,  
                     ONT.ORDER_LINES OEL,  
                     ONT.ORDER_HEADERS OEH
                 WHERE  
                     RAL.SALES_ORDER = TO_CHAR(OEH.ORDER_NUMBER)     AND  
                     RAL.SALES_ORDER_LINE = TO_CHAR(OEL.LINE_NUMBER) AND  
                     OEH.HEADER_ID = OEL.HEADER_ID ) B
         WHERE  
             A.JOINKEY = B.JOINKEY (+) ORDER BY 1
Oracle Database

Avatar of undefined
Last Comment
andrewst

8/22/2022 - Mon
earth man2

            (with oracleholds as (SELECT DISTINCT oeh.order_number, ohd.NAME
                FROM            ont.oe_order_headers_all oeh,
                                    ont.order_lines oel,
                                ont.hold_sources ohs,
                                ont.order_holds oha,
                                ont.hold_definitions ohd
                    WHERE        oeh.header_id = oel.header_id
                    AND          oeh.header_id = ohs.hold_entity_id
                    AND          ohs.hold_id = ohd.hold_id
                    AND          oha.order_hold_id = ohd.hold_id
                    AND          ohd.NAME IN
                  ('TRV HOLD',
                   'ZIC HOLD'
                   ))

should look more like

             (SELECT DISTINCT oeh.order_number, ohd.NAME
                FROM            ont.oe_order_headers_all oeh,
                                    ont.order_lines oel,
                                ont.hold_sources ohs,
                                ont.order_holds oha,
                                ont.hold_definitions ohd
                    WHERE        oeh.header_id = oel.header_id
                    AND          oeh.header_id = ohs.hold_entity_id
                    AND          ohs.hold_id = ohd.hold_id
                    AND          oha.order_hold_id = ohd.hold_id
                    AND          ohd.NAME IN
                  ('TRV HOLD',
                   'ZIC HOLD'   )
                   ) as oracleholds
ASKER CERTIFIED SOLUTION
andrewst

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy