troubleshooting Question

Need help with Pro*C Query error

Avatar of katiep23
katiep23 asked on
Oracle Database
2 Comments1 Solution1148 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
andrewst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros