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
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