I have Postgres that works when on it own, but doesn't work when integrated into java and called on idempiere. I'm looking for suggestion.
I get the ff error:
caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "00"
Position: 1055; State=42601; ErrorCode=0
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:120)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:384)
at jdk.internal.reflect.GeneratedMethodAccessor48.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.compiere.db.StatementProxy.invoke(StatementProxy.java:130)
at com.sun.proxy.$Proxy11.executeUpdate(Unknown Source)
at org.compiere.util.DB.executeUpdate(DB.java:1039)
at org.compiere.util.DB.executeUpdate(DB.java:898)
at org.compiere.util.DB.executeUpdate(DB.java:885)
at ice.enterprise.base.report.StockAgingReport.createDetailLines(StockAgingReport.java:145)
at ice.enterprise.base.report.StockAgingReport.doIt(StockAgingReport.java:72)
at org.compiere.process.SvrProcess.process(SvrProcess.java:201)
at org.compiere.process.SvrProcess.startProcess(SvrProcess.java:147)
at org.adempiere.util.ProcessUtil.startJavaProcess(ProcessUtil.java:173)
at org.compiere.apps.AbstractProcessCtl.startProcess(AbstractProcessCtl.java:467)
at org.compiere.apps.AbstractProcessCtl.run(AbstractProcessCtl.java:235)
15:21:49.994===========> DataEngine.loadPrintData: null - ERROR: column "levelno" does not exist
Position: 924
SQL=SELECT T_ReportStockAgeing.CurrentCost,T_ReportStockAgeing.Date1,T_ReportStockAgeing_ICE.Description,T_ReportStockAgeing.OnHand,(SELECT NVL(AD_PInstance.Name,'-1') ||'_'|| NVL(CAST (AD_PInstance.AD_PInstance_ID AS Text),'-1') ||'_'|| NVL((SELECT NVL(AD_Process.Name,'-1') ||'_'|| NVL(AD_Process.Value,'-1') FROM AD_Process WHERE AD_PInstance.AD_Process_ID=AD_Process.AD_Process_ID),'-1') FROM AD_PInstance WHERE T_ReportStockAgeing.AD_PInstance_ID=AD_PInstance.AD_PInstance_ID) AS AAD_PInstance_ID,T_ReportStockAgeing.AD_PInstance_ID AS AD_PInstance_ID,T_ReportStockAgeing.ProductCode,T_ReportStockAgeing.Qty1,T_ReportStockAgeing.Qty2,T_ReportStockAgeing.Qty3,T_ReportStockAgeing.Qty4,T_ReportStockAgeing.Value1,T_ReportStockAgeing.Valu2,T_ReportStockAgeing.Value3,T_ReportStockAgeing.Value4,T_ReportStockAgeing_ICE.T_ReportStockAgeing_ICE_UU,LevelNo FROM T_ReportStockAgeing_ICE WHERE T_ReportStockAgeing_ICE.AD_PInstance_ID=2109500 [136]
15:21:50.030===========> AbstractProcessDialog.doRun: org.postgresql.util.PSQLException: ERROR: column "levelno" does not exist
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.logging.Level;
import org.compiere.print.MPrintFormat;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Ini;
public class StockAgingReport extends SvrProcess {
private int p_AD_Org_ID = 0;
private int p_AD_Client_ID = 0;
private int p_C_AcctSchema_ID = 0;
private int p_M_Product_Category_ID = 0;
private Timestamp p_Date = null;
private long m_start = System.currentTimeMillis();
@Override
protected void prepare() {
StringBuffer sb = new StringBuffer ("Record_ID=")
.append(getRecord_ID());
// Parameter
ProcessInfoParameter[] para = getParameter();
for (int i = 0; i < para.length; i++)
{
String name = para[i].getParameterName();
if (para[i].getParameter() == null && para[i].getParameter_To() == null)
;
else if (name.equals("Date"))
{
p_Date = (Timestamp)para[i].getParameter();
}
else if (name.equals("AD_Org_ID"))
p_AD_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();
else if (name.equals("AD_Client_ID"))
p_AD_Client_ID = para[i].getParameterAsInt();
else if (name.equals("C_AcctSchema_ID"))
p_C_AcctSchema_ID = para[i].getParameterAsInt();
else if (name.equals("M_Product_Category_ID"))
p_M_Product_Category_ID = para[i].getParameterAsInt();
else
log.log(Level.SEVERE, "Unknown Parameter: " + name);
}
log.fine(sb.toString());
}
@Override
protected String doIt() throws Exception {
createDetailLines();
int AD_PrintFormat_ID = DB.getSQLValue(get_TrxName(), "Select AD_PrintFormat_ID from AD_PrintFormat Where name = 'Stock_Ageing'");
if (AD_PrintFormat_ID > 0) {
if (Ini.isClient())
getProcessInfo().setTransientObject (MPrintFormat.get (getCtx(), AD_PrintFormat_ID, false));
else
getProcessInfo().setSerializableObject(MPrintFormat.get (getCtx(), AD_PrintFormat_ID, false));
}
if (log.isLoggable(Level.FINE)) log.fine((System.currentTimeMillis() - m_start) + " ms");
return "";
}
private void createDetailLines() {
StringBuffer sb = new StringBuffer ("INSERT INTO T_ReportStockAgeing "
+ "(AD_PInstance_ID, AD_Client_ID, AD_Org_ID, ProductCode, Description, CurrentCost,"
+ " Qty1, Qty2, Qty3, Qty4, OnHand,"
+ " Value1, Valu2, Value3 ,Value4) ");
sb.append("SELECT ").append(getAD_PInstance_ID()).append(", ").append(Env.getAD_Client_ID(getCtx())).append(", ").append(Env.getAD_Org_ID(getCtx())).append(", ")
.append( "ProductCode, Description, Case OnHand WHEN 0 THEN 0 ELSE CurrentCost*OnHand END AS CurrentCost,"
+ "Qty1, Qty2, Qty3, Qty4, OnHand,"
+ "CASE OnHand WHEN 0 THEN 0 ELSE round((CurrentCost) * Qty1,2) END as Value1, "
+ "CASE OnHand WHEN 0 THEN 0 ELSE round((CurrentCost) * Qty2,2) END as Valu2, "
+ "CASE OnHand WHEN 0 THEN 0 ELSE round((CurrentCost) * Qty3,2) END as Value3, "
+ "CASE OnHand WHEN 0 THEN 0 ELSE round((CurrentCost) * Qty4,2) END as Value4 "
+ "FROM ( "
+ "SELECT "
+ "prod.value as ProductCode, "
+ "prod.description as Description, "
+ "M_Product_Category_ID, "
+ "(SELECT COALESCE (max(c.CurrentCostPrice),0) FROM M_Cost c WHERE c.AD_Org_ID =" +p_AD_Org_ID +" AND c.C_AcctSchema_ID= "+p_C_AcctSchema_ID+" AND prod.M_Product_ID = c.M_Product_ID AND prod.ProductType !='A' ) as CurrentCost, "
+ "(SELECT COALESCE (SUM (st.qtyonhand),0)FROM M_Storage st "
+ "WHERE prod.M_Product_ID = st.M_Product_ID "
+ "AND st.DateMaterialPolicy >= ( ("+p_Date+"::date) - interval '3 month' ) "
+ "AND st.DateMaterialPolicy <= ("+p_Date+"::date) "
+ "AND st.AD_Org_ID = "+p_AD_Org_ID+" AND prod.ProductType !='A' "
+ ") as Qty1, "
+ "(SELECT COALESCE (SUM (st.qtyonhand),0)FROM M_Storage st "
+ "WHERE prod.M_Product_ID = st.M_Product_ID "
+ "AND st.DateMaterialPolicy >= ("+p_Date+"::date- interval '6 month' ) "
+ "AND st.DateMaterialPolicy <= ("+p_Date+"::date - interval '3 month') "
+ "AND st.AD_Org_ID = "+p_AD_Org_ID+" AND prod.ProductType !='A') "
+ "as Qty2, "
+ "(SELECT COALESCE (SUM (st.qtyonhand),0)FROM M_Storage st "
+ "WHERE prod.M_Product_ID = st.M_Product_ID "
+ "AND st.DateMaterialPolicy >= ("+p_Date+"::date - interval '12 month' ) "
+ "AND st.DateMaterialPolicy <= ("+p_Date+"::date- interval '6 month') "
+ "AND st.AD_Org_ID = "+p_AD_Org_ID+" AND prod.ProductType !='A' "
+ ") as Qty3, "
+ "(SELECT COALESCE (SUM (st.qtyonhand),0)FROM M_Storage st "
+ "WHERE prod.M_Product_ID = st.M_Product_ID "
+ "AND st.DateMaterialPolicy < ("+p_Date+"::date- interval '12 month') "
+ "AND st.AD_Org_ID = "+p_AD_Org_ID+" AND prod.ProductType !='A' "
+ ") as Qty4, "
+ "(SELECT COALESCE (SUM (st.qtyonhand),0)FROM M_Storage st "
+ "WHERE prod.M_Product_ID = st.M_Product_ID "
+ "AND st.AD_Org_ID = "+p_AD_Org_ID+" AND prod.ProductType !='A' "
+ ") as OnHand "
+ "FROM M_Product prod "
+ "LEFT JOIN AD_Org org ON org.AD_Org_ID = "+p_AD_Org_ID+" "
+ "LEFT JOIN M_Product_Category prodcat ON prodcat.M_Product_Category_ID = "+p_M_Product_Category_ID+" AND prodcat.AD_Client_ID = "+p_AD_Client_ID+" "
+ "WHERE prod.M_Product_Category_ID = "+ p_M_Product_Category_ID+" AND prod.ProductType !='A' "
+ ")temp ");
sb.append(" ) as temp ");
int no = DB.executeUpdate(sb.toString(), get_TrxName());
log.fine("#" + no);
log.finest(sb.toString());
}
}
I tried to integrate your class example for testing in iDempiere, but as GhostCat pointed, it's better if you create a minimal reproducible example, and also very important, please post also the error that is being thrown by the system, in UI and/or in console log.
The class doesn't have some imports, all the variables are not used, and the private method createDetailLines is never called, I assume the error you're mentioning is in that method, but there is no way to know if is not called.
Now, reviewing the SQL, there are two things to notice:
1 - it's not formatted for java, you use "AND c.C_AcctSchema_ID= $P{C_AcctSchema_ID}" and that's not the way as java manage variables, that sounds like jasper report syntax instead of java. For JDBC you must use ? as a replacement for binding variables
2 - iDempiere is multi-database, the way how the system is designed is to write oracle compatible SQL syntax, and there is a translation layer that converts the oracle statement ot postgresql syntax. So, it's better to avoid using postgresql specific syntax like "::date" or interval '3 month' - it could work, but it can also have problems with the convert layer. If you want to use specific postgresql syntax and avoid the convert layer being confused you can surround the postgresql specific syntax with NATIVE_PostgreSQL_KEYWORK - please don't blame me about the error in this constant :-)