Home » Developer & Programmer » Reports & Discoverer » Unable to Parse Query (2.5)
icon4.gif  Unable to Parse Query [message #387169] Wed, 18 February 2009 02:21
privs
Messages: 11
Registered: February 2009
Location: South Africa
Junior Member

I have developed the report in version 6i and it works well, but need to convert it to version 2.5

It gives me an error that it cant parse the code


here is the code, can any one help me :

select * from
(
SELECT 'IMPORTS','FULL',DECODE (a.packagetypecode, 'CNT12', '20', 'CNT6', '40'), nvl(B.Cnt,0),
DECODE (a.packagetypecode, 'CNT6', nvl(B.Cnt,0) *2, 'CNT12', nvl(B.Cnt,0)) T
FROM (
SELECT DISTINCT packagetypecode
FROM suppackagetypes
where packagetypecode in ('CNT12','CNT6')
) A
LEFT JOIN (
SELECT mi.packagetypecode, COUNT(mi.PACKAGETYPECODE) AS Cnt
FROM manifestitems mi, manifestsequences ms
WHERE mi.booknumber in (SELECT booknumber
FROM books b
WHERE BOOKTYPE = 'M'
AND b.arrivalnumber IN (
SELECT va.arrivalnumber
FROM supvessels sv, vesselarrivals va
WHERE sv.vesselnumber = va.vesselnumber
AND sv.shippingline =
DECODE (:p_shippingline,
'ALL', sv.shippingline,
:p_shippingline
)))

and mi.empty = 'N'
and mi.BOOKNUMBER = ms.booknumber
and ms.TRANSHIPMENT = 'N'
GROUP BY mi.packagetypecode
) B
ON A.packagetypecode = B.packagetypecode
UNION
SELECT 'IMPORTS','EMPTY',DECODE (a.packagetypecode, 'CNT12', '20', 'CNT6', '40'), nvl(B.Cnt,0),
DECODE (a.packagetypecode, 'CNT6', nvl(B.Cnt,0) *2, 'CNT12', nvl(B.Cnt,0)) T
FROM (
SELECT DISTINCT packagetypecode
FROM suppackagetypes
where packagetypecode in ('CNT12','CNT6')
) A
LEFT JOIN (
SELECT mi.packagetypecode, COUNT(mi.PACKAGETYPECODE) AS Cnt
FROM manifestitems mi, manifestsequences ms
WHERE mi.booknumber in (SELECT booknumber
FROM books b
WHERE BOOKTYPE = 'M'
AND b.arrivalnumber IN (
SELECT va.arrivalnumber
FROM supvessels sv, vesselarrivals va
WHERE sv.vesselnumber = va.vesselnumber
AND sv.shippingline =
DECODE (:p_shippingline,
'ALL', sv.shippingline,
:p_shippingline
)))

and mi.empty = 'Y'
and mi.BOOKNUMBER = ms.booknumber
and ms.TRANSHIPMENT = 'N'
GROUP BY mi.packagetypecode
) B
ON A.packagetypecode = B.packagetypecode
)

union

select * from
(
SELECT 'EXPORTS','FULL',DECODE (a.packagetypecode, 'CNT12', '20', 'CNT6', '40'), nvl(B.Cnt,0),
DECODE (a.packagetypecode, 'CNT6', nvl(B.Cnt,0) *2, 'CNT12', nvl(B.Cnt,0)) T
FROM (
SELECT DISTINCT packagetypecode
FROM suppackagetypes
where packagetypecode in ('CNT12','CNT6')
) A
LEFT JOIN (
SELECT si.packagetypecode, COUNT(si.PACKAGETYPECODE) AS Cnt
from shippingitems si,shippingsequences ss
WHERE si.booknumber in (SELECT booknumber
FROM books b
WHERE BOOKTYPE = 'M'
AND b.arrivalnumber IN (
SELECT va.arrivalnumber
FROM supvessels sv, vesselarrivals va
WHERE sv.vesselnumber = va.vesselnumber
AND sv.shippingline =
DECODE (:p_shippingline,
'ALL', sv.shippingline,
:p_shippingline
)))

and si.empty = 'N'
and si.BOOKNUMBER = ss.booknumber
and ss.TRANSHIPMENT = 'N'
GROUP BY si.packagetypecode
) B
ON A.packagetypecode = B.packagetypecode
UNION
SELECT 'EXPORTS','EMPTY',DECODE (a.packagetypecode, 'CNT12', '20', 'CNT6', '40'), nvl(B.Cnt,0),
DECODE (a.packagetypecode, 'CNT6', nvl(B.Cnt,0) *2, 'CNT12', nvl(B.Cnt,0)) T
FROM (
SELECT DISTINCT packagetypecode
FROM suppackagetypes
where packagetypecode in ('CNT12','CNT6')
) A
LEFT JOIN (
SELECT mi.packagetypecode, COUNT(mi.PACKAGETYPECODE) AS Cnt
FROM manifestitems mi, manifestsequences ms
WHERE mi.booknumber in (SELECT booknumber
FROM books b
WHERE BOOKTYPE = 'M'
AND b.arrivalnumber IN (
SELECT va.arrivalnumber
FROM supvessels sv, vesselarrivals va
WHERE sv.vesselnumber = va.vesselnumber
AND sv.shippingline =
DECODE (:p_shippingline,
'ALL', sv.shippingline,
:p_shippingline
)))

and mi.empty = 'Y'
and mi.BOOKNUMBER = ms.booknumber
and ms.TRANSHIPMENT = 'N'
GROUP BY mi.packagetypecode
) B
ON A.packagetypecode = B.packagetypecode
)

union

select * from
(
SELECT 'TRANSHIPMENTS','FULL',DECODE (a.packagetypecode, 'CNT12', '20', 'CNT6', '40'), nvl(B.Cnt,0),
DECODE (a.packagetypecode, 'CNT6', nvl(B.Cnt,0) *2, 'CNT12', nvl(B.Cnt,0)) T
FROM (SELECT DISTINCT packagetypecode
FROM suppackagetypes
WHERE packagetypecode IN ('CNT12', 'CNT6')) a
LEFT JOIN
(SELECT mi.packagetypecode, COUNT (mi.packagetypecode) AS cnt
FROM manifestitems mi, manifestsequences ms
WHERE mi.booknumber in (SELECT booknumber
FROM books b
WHERE BOOKTYPE = 'M'
AND b.arrivalnumber IN (
SELECT va.arrivalnumber
FROM supvessels sv, vesselarrivals va
WHERE sv.vesselnumber = va.vesselnumber
AND sv.shippingline =
DECODE (:p_shippingline,
'ALL', sv.shippingline,
:p_shippingline
)))

AND mi.empty = 'N'
AND mi.booknumber = ms.booknumber
AND ms.transhipment = 'N'
GROUP BY mi.packagetypecode) b ON a.packagetypecode =
b.packagetypecode
UNION
SELECT 'TRANSHIPMENTS','EMPTY',DECODE (a.packagetypecode, 'CNT12', '20', 'CNT6', '40'), nvl(B.Cnt,0),
DECODE (a.packagetypecode, 'CNT6', nvl(B.Cnt,0) *2, 'CNT12', nvl(B.Cnt,0)) T
FROM (SELECT DISTINCT packagetypecode
FROM suppackagetypes
WHERE packagetypecode IN ('CNT12', 'CNT6')) a
LEFT JOIN
(SELECT mi.packagetypecode, COUNT (mi.packagetypecode) AS cnt
FROM manifestitems mi, manifestsequences ms
WHERE mi.booknumber in (SELECT booknumber
FROM books b
WHERE BOOKTYPE = 'M'
AND b.arrivalnumber IN (
SELECT va.arrivalnumber
FROM supvessels sv, vesselarrivals va
WHERE sv.vesselnumber = va.vesselnumber
AND sv.shippingline =
DECODE (:p_shippingline,
'ALL', sv.shippingline,
:p_shippingline
)))

AND mi.empty = 'Y'
AND mi.booknumber = ms.booknumber
AND ms.transhipment = 'Y'
GROUP BY mi.packagetypecode) b ON a.packagetypecode =
b.packagetypecode
)
  • Attachment: sql code.txt
    (Size: 6.61KB, Downloaded 128 times)

[Updated on: Thu, 19 February 2009 01:09]

Report message to a moderator

Previous Topic: Mailing labels report
Next Topic: multi select lov in oracle 10g reports
Goto Forum:
  


Current Time: Mon Dec 05 06:31:08 CST 2016

Total time taken to generate the page: 0.08252 seconds