Home » Developer & Programmer » Reports & Discoverer » Unable to Parse Query (2.5)
 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 1245 times)
[Updated on: Thu, 19 February 2009 01:09] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Wed May 08 13:55:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
