Hi all

I am not sure why I am seeing this error (screenshot attached) for a
query (see below) that is running well on my development copy (on right)
of the database but not on the production copy (on left) of the
database. The query will later be used in Report Manager so please
ignore the commented lines.

Totally lost, please can someone shed some light on this. How can I
debug the problem?

Please advise.

Kind regards.
Bhavbhuti



WITH RECURSIVE GroupSummary(iID, MyLevel, ilvlID1, ilvlID2, ilvlID3,
ilvlID4, ilvlID5, ilvlID6, ilvlID7, ilvlID8, ilvlID9, ilvlID10) AS
(SELECT lvl1.iID, CAST(0 AS INTEGER), lvl1.iID, CAST(NULL AS INTEGER),
CAST(NULL AS INTEGER), CAST(NULL AS INTEGER), CAST(NULL AS
INTEGER),
CAST(NULL AS INTEGER), CAST(NULL AS INTEGER), CAST(NULL AS
INTEGER),
CAST(NULL AS INTEGER), CAST(NULL AS INTEGER)
FROM mItems lvl1
WHERE lvl1.ipid = 0
UNION ALL
SELECT lvlc.iID, lvlp.MyLevel + 1, lvlp.iID,
CASE WHEN lvlp.MyLevel = 0 THEN lvlc.iID ELSE lvlp.ilvlID2 END,
CASE WHEN lvlp.MyLevel = 1 THEN lvlc.iID ELSE lvlp.ilvlID3 END,
CASE WHEN lvlp.MyLevel = 2 THEN lvlc.iID ELSE lvlp.ilvlID4 END,
CASE WHEN lvlp.MyLevel = 3 THEN lvlc.iID ELSE lvlp.ilvlID5 END,
CASE WHEN lvlp.MyLevel = 4 THEN lvlc.iID ELSE lvlp.ilvlID6 END,
CASE WHEN lvlp.MyLevel = 5 THEN lvlc.iID ELSE lvlp.ilvlID7 END,
CASE WHEN lvlp.MyLevel = 6 THEN lvlc.iID ELSE lvlp.ilvlID8 END,
CASE WHEN lvlp.MyLevel = 7 THEN lvlc.iID ELSE lvlp.ilvlID9 END,
CASE WHEN lvlp.MyLevel = 8 THEN lvlc.iID ELSE lvlp.ilvlID10 END
FROM GroupSummary lvlp
JOIN mItems lvlc
ON lvlp.iID = lvlc.ipid
WHERE lvlp.MyLevel < 10)

SELECT mGroups.cDesc, mGroups.cPack
, SUM(CASE WHEN CAST(tSaleInvoice.tDt AS DATE) BETWEEN
'2009.04.01' AND '2009.12.31'
-- , SUM(CASE WHEN CAST(tSaleInvoice.tDt AS DATE) BETWEEN
<<vp_cProgFromDt>> AND <<vp_cProgToDt>>
AND sSaleInvoiceItem.cSaleType <> 'FREE' THEN
sSaleInvoiceItem.bQty ELSE 0 END) AS bProgQty
, SUM(CASE WHEN CAST(tSaleInvoice.tDt AS DATE) BETWEEN
'2009.04.01' AND '2009.12.31'
-- , SUM(CASE WHEN CAST(tSaleInvoice.tDt AS DATE) BETWEEN
<<vp_cProgFromDt>> AND <<vp_cProgToDt>>
AND sSaleInvoiceItem.cSaleType = 'FREE' THEN
sSaleInvoiceItem.bQty ELSE 0 END) AS bProgFreeQty
, SUM(CASE WHEN CAST(tSaleInvoice.tDt AS DATE) BETWEEN
'2010.01.01' AND '2010.01.31'
-- , SUM(CASE WHEN CAST(tSaleInvoice.tDt AS DATE) BETWEEN
<<vp_cFromDt>> AND <<vp_cToDt>>
AND sSaleInvoiceItem.cSaleType <> 'FREE' THEN
sSaleInvoiceItem.bQty ELSE 0 END) AS bQty
, SUM(CASE WHEN CAST(tSaleInvoice.tDt AS DATE) BETWEEN
'2010.01.01' AND '2010.01.31'
-- , SUM(CASE WHEN CAST(tSaleInvoice.tDt AS DATE) BETWEEN
<<vp_cFromDt>> AND <<vp_cToDt>>
AND sSaleInvoiceItem.cSaleType = 'FREE' THEN
sSaleInvoiceItem.bQty ELSE 0 END) AS bFreeQty
, GroupSummary.ilvlID3
FROM GroupSummary GroupSummary
JOIN mItems
ON mItems.iID = GroupSummary.iID
AND mItems.cTypeID IN ('FIN', 'SEMIFIN')
LEFT JOIN sSaleInvoiceItem
ON sSaleInvoiceItem.iItemID = mItems.iID
JOIN tSaleInvoice
ON tSaleInvoice.iID = sSaleInvoiceItem.iPID
JOIN mItems mGroups
ON mGroups.iID = GroupSummary.ilvlID3
WHERE COALESCE(GroupSummary.iLvlID3, 0) > 0
GROUP BY mGroups.cDesc, mGroups.cPack, GroupSummary.ilvlID3



[Non-text portions of this message have been removed]