#6 Select existing AttributeSet Instance

[|gun|th|er|]

I've reached an error, when i want to select an
existing attribute set instance (see error log).

For retrace:
Create a instance attribute set w/ some attributes. Set
this attribute set to a product in the 'Product'
window. Go to 'Bill of Materials' window, select the
product as 'Product' in the 'Bill of Material' tab. Now
enter the attribute set instance field and try to
choose an existing instance attribute set.

The reason for this rely on a PostgreSQL specific.
Responsible for the execption especially is the
statement part:
"...(TRUNC(asi.GuaranteeDate)-TRUNC(SysDate))-p.GuaranteeDaysMin..."
Apart from an other bug, associated w/ the 'sysdate'
(see
http://sourceforge.net/tracker/index.php?func=detail&aid=1233404&group_id=97192&atid=617267\)
the subtraction of two timestamps, dates, etc.results a
datatype 'Interval'. 'GuaranteeDaysMin' in contrast is
a simple integer. The postgres operation, that should
do such substraction type takes a timestamp parameter,
which isn't confirm to 'Interval'. Therefor the
subtract operation of (Interval)-(numeric) istn't still
supported.

Regards
Gunther Hoppe

Error log:

===========> PAttributeInstance.refresh: refresh -
SELECT s.M_AttributeSetInstance_ID, asi.Description,
asi.Lot, asi.SerNo, asi.GuaranteeDate, l.Value
tyOnHand, s.QtyReserved, s.QtyOrdered,
(TRUNC(asi.GuaranteeDate)-TRUNC(SysDate))-p.GuaranteeDaysMin,
TRUNC(asi.GuaranteeDate)-TRUNC(SysDate), CASE WHE
0 THEN
TRUNC(((TRUNC(asi.GuaranteeDate)-TRUNC(SysDate))/p.GuaranteeDays)*100)
ELSE 0 END FROM M_Storage s INNER JOIN M_Locator l ON
(s.M_Locator_ID=l.
JOIN M_Product p ON (s.M_Product_ID=p.M_Product_ID)
LEFT OUTER JOIN M_AttributeSetInstance asi ON
(s.M_AttributeSetInstance_ID=asi.M_AttributeSetInsta
rehouse_ID=? AND s.M_Product_ID=? AND s.AD_Client_ID IN
(0,11) AND (s.QtyOnHand<>0 OR s.QtyReserved<>0 OR
s.QtyOrdered<>0) ORDER BY asi.GuaranteeDate,
java.sql.SQLException: ERROR: operator does not exist:
interval - numeric; State=42883; ErrorCode=0
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:240)
at
sun.reflect.GeneratedMethodAccessor11.invoke(Unknown
Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at
org.postgresql.ds.common.PooledConnectionImpl$StatementHandler.invoke(PooledConnectionImpl.java:467)
at $Proxy1.executeQuery(Unknown Source)
at
org.compiere.util.CPreparedStatement.executeQuery(CPreparedStatement.java:99)
at
org.compiere.apps.search.PAttributeInstance.refresh(PAttributeInstance.java:259)
at
org.compiere.apps.search.PAttributeInstance.dynInit(PAttributeInstance.java:233)
at
org.compiere.apps.search.PAttributeInstance.init(PAttributeInstance.java:86)
at
org.compiere.apps.search.PAttributeInstance.<init>(PAttributeInstance.java:64)
at
org.compiere.grid.ed.VPAttributeDialog.cmd_select(VPAttributeDialog.java:497)
at
org.compiere.grid.ed.VPAttributeDialog.actionPerformed(VPAttributeDialog.java:396)
at
javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849)
at
javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169)
at
javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)

[Victor Perez Juarez]

Logged In: YES
user_id=332830

Yes, The problem is into ConvertMap.java I need to add the
convertion sysdate to current_timestamp of PostgreSQL, this
error fixed in the next release

[|gun|th|er|]

Logged In: YES
user_id=1184985

Hi,

yes - the conversion from 'SysDate' to 'Current_Timestamp'
is one problem - but here i'm pointing to another one,
related to the unsupported subtract operation
INTERVAL-NUMERIC. CMPCS only supports the TIMESTAMP-NUMERIC
operation, defined as a postgres OPERATION. But this isn't
the valid operation in this case, because
TIMESTAMP-TIMESTAMP results an INTERVAL datatype, not
TIMESTAMP.

Regards
Gunther

[Victor Perez Juarez]

Logged In: YES
user_id=332830

I review this problem and I know how to solve it, I need to
add support for Date_Part() into Convert.java

Cheers
Victor