0005776: Trunk revision 9471 does not compile in Oracle

Description

Trunk revision 9471 does not compile in Oracle:

ERROR org.openbravo.data.Sqlc - SQL error in query:
     [java]
     [java] SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7,
     [java] SUM(LINENETAMT) AS LINENETAMT, SUM(REFUNDAMT) AS REFUNDAMT,
     [java] C_DIVIDE(SUM(REFUNDAMT), (SUM(LINENETAMT)+SUM(REFUNDAMT)))*100 AS RATIO,
     [java] SUM(QTYINVOICED) AS QTYINVOICED, SUM(REFUNDQTY) AS REFUNDQTY,
     [java] SUM(LINENETREF) AS LINENETREF, SUM(REFUNDAMTREF) AS REFUNDAMTREF,
     [java] C_DIVIDE(SUM(REFUNDAMTREF), (SUM(LINENETREF)+SUM(REFUNDAMTREF)))*100 AS RATIOREF,
     [java] SUM(QTYINVOICEDREF) AS QTYINVOICEDREF, SUM(REFUNDQTYREF) AS REFUNDQTYREF, UOMSYMBOL,
     [java] C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM,
     [java] C_CURRENCY_ISOSYM(?) AS CONVISOSYM,
     [java] '' AS ID, '' AS NAME, '' AS TRANSCURRENCYID, '' AS TRANSDATE, '' AS TRANSCLIENTID, '' AS TRANSORGID
     [java] FROM (SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7,
     [java] C_CURRENCY_CONVERT(SUM(LINENETAMT), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS LINENETAMT,
     [java] C_CURRENCY_CONVERT(SUM(REFUNDAMT), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS REFUNDAMT,
     [java] SUM(QTYINVOICED) AS QTYINVOICED, SUM(REFUNDQTY) AS REFUNDQTY,
     [java] C_CURRENCY_CONVERT(SUM(LINENETREF), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS LINENETREF,
     [java] C_CURRENCY_CONVERT(SUM(REFUNDAMTREF), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS REFUNDAMTREF,
     [java] SUM(QTYINVOICEDREF) AS QTYINVOICEDREF, SUM(REFUNDQTYREF) AS REFUNDQTYREF, UOMSYMBOL,
     [java] TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID
     [java] FROM (SELECT to_char('1') AS NIVEL1, to_char('2') AS NIVEL2, to_char('3') AS NIVEL3, to_char('4') AS NIVEL4, to_char('5') AS NIVEL5, to_char('6') AS NIVEL6, to_char('7') AS NIVEL7,
     [java] CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN -1 THEN 0 ELSE C_ORDERLINE.LINENETAMT END AS LINENETAMT,
     [java] CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN -1 THEN -1*C_ORDERLINE.LINENETAMT ELSE 0 END AS REFUNDAMT,
     [java] CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN -1 THEN 0 ELSE C_ORDERLINE.QTYORDERED END AS QTYINVOICED,
     [java] CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN -1 THEN (-1*C_ORDERLINE.QTYORDERED) ELSE 0 END AS REFUNDQTY,
     [java] 0 AS LINENETREF, 0 AS REFUNDAMTREF, 0 AS QTYINVOICEDREF, 0 AS REFUNDQTYREF, C_UOM.UOMSYMBOL,
     [java] COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRCURRENCYID,
     [java] TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRDATE,
     [java] C_ORDERLINE.AD_CLIENT_ID AS TRCLIENTID,
     [java] C_ORDERLINE.AD_ORG_ID AS TRORGID
     [java] FROM C_ORDER left join AD_USER on C_ORDER.SALESREP_ID = AD_USER.AD_USER_ID,
     [java] C_ORDERLINE left join M_PRODUCT on C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
     [java] left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
     [java] left join C_UOM on C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID,
     [java] C_BPARTNER, C_BP_GROUP, M_WAREHOUSE
     [java] WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
     [java] AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
     [java] AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
     [java] AND C_ORDER.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID
     [java] AND C_ORDER.ISSOTRX = 'Y'
     [java] AND C_ORDER.PROCESSED = 'Y'
     [java] AND 0=0 AND C_ORDER.AD_ORG_ID IN ('1')
     [java] AND C_ORDER.AD_CLIENT_ID IN ('8')
     [java] AND 1=1
     [java] UNION ALL SELECT to_char('9') AS NIVEL1 , to_char('10') AS NIVEL2 , to_char('11') AS NIVEL3 , to_char('12') AS NIVEL4 , to_char('13') AS NIVEL5 , to_char('14') AS NIVEL6 , to_char('15') AS NIVEL7 ,
     [java] 0 AS LINENETAMT, 0 AS REFUNDAMT, 0 AS QTYINVOICED, 0 AS REFUNDQTY,
     [java] CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN -1 THEN 0 ELSE C_ORDERLINE.LINENETAMT END AS LINENETREF,
     [java] CASE SIGN(C_ORDERLINE.LINENETAMT) WHEN -1 THEN (-1*C_ORDERLINE.LINENETAMT) ELSE 0 END AS REFUNDAMTREF,
     [java] CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN -1 THEN 0 ELSE C_ORDERLINE.QTYORDERED END AS QTYINVOICEDREF,
     [java] CASE SIGN(C_ORDERLINE.QTYORDERED) WHEN -1 THEN (-1*C_ORDERLINE.QTYORDERED) ELSE 0 END AS REFUNDQTYREF, C_UOM.UOMSYMBOL,
     [java] COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRCURRENCYID,
     [java] TO_DATE(COALESCE(C_ORDERLINE.DATEORDERED, C_ORDER.DATEORDERED, NOW())) AS TRDATE,
     [java] C_ORDERLINE.AD_CLIENT_ID AS TRCLIENTID,
     [java] C_ORDERLINE.AD_ORG_ID AS TRORGID
     [java] FROM C_ORDER left join AD_USER on C_ORDER.SALESREP_ID = AD_USER.AD_USER_ID,
     [java] C_ORDERLINE left join M_PRODUCT on C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
     [java] left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
     [java] left join C_UOM on C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID,
     [java] C_BPARTNER, C_BP_GROUP, M_WAREHOUSE
     [java] WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
     [java] AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
     [java] AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
     [java] AND C_ORDER.M_WAREHOUSE_ID = M_WAREHOUSE.M_WAREHOUSE_ID
     [java] AND C_ORDER.ISSOTRX = 'Y'
     [java] AND C_ORDER.PROCESSED = 'Y'
     [java] AND 3=3 AND C_ORDER.AD_ORG_ID IN ('1')
     [java] AND C_ORDER.AD_CLIENT_ID IN('16')
     [java] AND 2=2) AA
     [java] WHERE LINENETAMT <> 0
     [java] OR REFUNDAMT <> 0
     [java] OR LINENETREF <> 0
     [java] OR REFUNDAMTREF <> 0
     [java] GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, UOMSYMBOL, TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID) ZZ
     [java] GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, UOMSYMBOL, CONVSYM, CONVISOSYM, 1
     [java] Exception:java.sql.SQLException: ORA-00904: "CONVISOSYM": invalid identifier
     [java]
     [java] java.sql.SQLException: ORA-00904: "CONVISOSYM": invalid identifier

Issue History
Date Modified	Username	Field	Change
2008-10-31 11:07	rafaroda	New Issue
2008-10-31 11:07	rafaroda	Assigned To	=> rafaroda
2008-10-31 11:07	rafaroda	sf_bug_id	0 => 2211549
2008-10-31 11:07	rafaroda	Regression testing	=> No
2008-10-31 12:45	svnbot	Checkin
2008-10-31 12:45	svnbot	Note Added: 0009885
2008-10-31 12:45	svnbot	Status	new => resolved
2008-10-31 12:45	svnbot	Resolution	open => fixed
2008-10-31 12:45	svnbot	svn_revision	=> 9500
2008-11-25 18:56	psarobe	Status	resolved => closed

Notes
(0009885) svnbot (reporter) 2008-10-31 12:45	Repository: openbravo Revision: 9500 Author: rafaroda Date: 2008-10-31 12:45:52 +0100 (Fri, 31 Oct 2008) Fixes bug 0005776 Changed XSQLs to work with Oracle. --- U trunk/src/org/openbravo/erpCommon/ad_reports/ReportInvoiceCustomerDimensionalAnalysesJR_data.xsql U trunk/src/org/openbravo/erpCommon/ad_reports/ReportInvoiceVendorDimensionalAnalysesJR_data.xsql U trunk/src/org/openbravo/erpCommon/ad_reports/ReportMaterialDimensionalAnalysesJR_data.xsql U trunk/src/org/openbravo/erpCommon/ad_reports/ReportParetoProduct_data.xsql U trunk/src/org/openbravo/erpCommon/ad_reports/ReportPurchaseDimensionalAnalysesJR_data.xsql U trunk/src/org/openbravo/erpCommon/ad_reports/ReportRefundSalesDimensionalAnalyses_data.xsql U trunk/src/org/openbravo/erpCommon/ad_reports/ReportSalesDimensionalAnalyzeJR_data.xsql U trunk/src/org/openbravo/erpCommon/ad_reports/ReportShipmentDimensionalAnalyzeJR_data.xsql --- https://dev.openbravo.com/websvn/openbravo/?rev=9500&sc=1 [^]

Relationships [ Relation Graph ] [ Dependency Graph ]