hi guys
thanks for your help yesterday.
I am now trying to incorporate the query from yesterday into an existing query so I can show the highest itemcode's reporting group in the existing query..but I have a syntax error somewhere at my Select statement.
ERROR: Keyword SELECT not expected.
I have tried putting brackets at every possible place but still no go..can you please help? (ps-this whole query has been giving me nightmares!)
WITH CALC1 AS (SELECT OTQUOT, OTIT01 AS ITEMS, ROUND(OQCQ01 * OVRC01,2) AS COST
FROM
@
[email protected]
WHERE OTIT01 < ''
UNION ALL
SELECT OTQUOT, OTIT02 AS ITEMS, ROUND(OQCQ02 * OVRC02,2) AS COST
FROM
@
[email protected]
WHERE OTIT02 < ''
UNION ALL
SELECT OTQUOT, OTIT03 AS ITEMS, ROUND(OQCQ03 * OVRC03,2) AS COST
FROM
@
[email protected]
WHERE OTIT03 < ''
UNION ALL
SELECT OTQUOT, OTIT04 AS ITEMS, ROUND(OQCQ04 * OVRC04,2) AS COST
FROM
@
[email protected]
WHERE OTIT04 < ''
UNION ALL
SELECT OTQUOT, OTIT05 AS ITEMS, ROUND(OQCQ05 * OVRC05,2) AS COST
FROM
@
[email protected]
WHERE OTIT05 < ''
UNION ALL
SELECT OTQUOT, OTIT06 AS ITEMS, ROUND(OQCQ06 * OVRC06,2) AS COST
FROM
@
[email protected]
WHERE OTIT06 < ''
UNION ALL
SELECT OTQUOT, OTIT07 AS ITEMS, ROUND(OQCQ07 * OVRC07,2) AS COST
FROM
@
[email protected]
WHERE OTIT07 < ''
UNION ALL
SELECT OTQUOT, OTIT08 AS ITEMS, ROUND(OQCQ08 * OVRC08,2) AS COST
FROM
@
[email protected]
WHERE OTIT08 < ''
UNION ALL
SELECT OTQUOT, OTIT09 AS ITEMS, ROUND(OQCQ09 * OVRC09,2) AS COST
FROM
@
[email protected]
WHERE OTIT09 < ''
UNION ALL
SELECT OTQUOT, OTIT10 AS ITEMS, ROUND(OQCQ10 * OVRC10,2) AS COST
FROM
@
[email protected]
WHERE OTIT10 < ''
)
(SELECT OTQUOT, DESC
FROM (
SELECT OTQUOT, ITEMS, B.IXRPGP AS GROUP, C.OTRDSC AS DESC, COST, ROW_NUMBER() OVER
(ORDER BY COST DESC) AS RN
FROM CALC1 AS A INNER JOIN @
[email protected] AS B ON (A.ITEMS = B.IKITMC) INNER JOIN
DATAGRP.GDSGRP AS C ON (B.IXRPGP = C.OKRPGP)
) T
WHERE T.RN = 1)
SELECT
A.OKPBRN,
A.OCAREA,
A.OTCCDE,
A.OTCNAM,
A.OTSMAN,
A.OKPBRN||A.OAPNUM AS OTQUOT,
A.OTONUM,
A.OTCAD1,
A.OTCAD2,
A.OTCAD3,
A.OTPCDE,
A.OTDEL1,
A.OTDEL2,
A.OTDEL3,
CHAR(DATE(CASE WHEN SUBSTR(A.ODOQDT,5,4) = '0000' THEN '0001' ELSE SUBSTR(A.ODOQDT,5,4)
END ||'-'||
CASE WHEN SUBSTR(A.ODOQDT,4,2) = '00' THEN '
01' ELSE SUBSTR(A.ODOQDT,3,2) END ||'-'||
CASE WHEN SUBSTR(A.ODOQDT,1,2) = '00' THEN '
01' ELSE SUBSTR(A.ODOQDT,1,2) END), ISO) AS
ODOQDT_CCYYMMDD,
CHAR(DATE(CASE WHEN SUBSTR(A.ODDELD,7,2) = '' THEN '0001' ELSE '20'||SUBSTR(A.ODDELD,7,2)
END ||'-'||
CASE WHEN SUBSTR(A.ODDELD,4,2) = '' THEN '
01' ELSE SUBSTR(A.ODDELD,4,2) END ||'-'||
CASE WHEN SUBSTR(A.ODDELD,1,2) = '' THEN '
01' ELSE SUBSTR(A.ODDELD,1,2) END), ISO) AS
ODDELD_CCYYMMDD,
B.DESC,
A.OVQTVL
FROM
@
[email protected] AS A INNER JOIN CALC1 AS B ON (A.OKPBRN||A.OAPNUM = B.OTQUOT)
WHERE
A.OKPBRN = '@OKPBRN@'
AND A.OTCCDE NOT LIKE '*DEP%'
AND CHAR(DATE(CASE WHEN SUBSTR(A.ODOQDT,5,4) = '0000' THEN '0001' ELSE SUBSTR
(A.ODOQDT,5,4) END ||'-'||
CASE WHEN SUBSTR(A.ODOQDT,4,2) = '00' THEN '
01' ELSE SUBSTR(A.ODOQDT,3,2) END ||'-'||
CASE WHEN SUBSTR(A.ODOQDT,1,2) = '00' THEN '
01' ELSE SUBSTR(A.ODOQDT,1,2) END), ISO) =
CHAR(CURDATE() - 3 MONTH, ISO)
AND A.OCQF01 = '0'
AND A.OCQF02 = '0'
AND A.OCQF04 = '0'
AND A.OCQF05 = '0'
AND A.OCQF06 = '0'
AND A.OCQF07 = '0'
AND A.OCQF08 = '0'
AND A.OCQF09 = '0'
AND A.OCQF10 = '1'
AND A.OTCGRP LIKE 'S/%'
ORDER BY
A.OTSMAN ASC,
A.OVQTVL DESC,
CHAR(DATE(CASE WHEN SUBSTR(A.ODDELD,7,2) = '' THEN '0001' ELSE '20'||SUBSTR(A.ODDELD,7,2) END ||'-'||
CASE WHEN SUBSTR(A.ODDELD,4,2) = '' THEN '
01' ELSE SUBSTR(A.ODDELD,4,2) END ||'-'||
CASE WHEN SUBSTR(A.ODDELD,1,2) = '' THEN '
01' ELSE SUBSTR(A.ODDELD,1,2) END),ISO) ASC
