[OJB-9] Missing table qualifier in count sql preceding distinct QueryByCriteria

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.0.x CVS
• Fix Version/s: 1.1 CVS
• Component/s: PB-API
• Environment: SAPDB 7.5; Win XP; OJB 1.0.1
• Assignee: Unassigned
• Reporter: S. Schlösser
• Created: 30/Mar/05 16:52
• Updated: 09/Jun/05 05:47
• Resolved: 09/Jun/05 05:47

Description

Hi,

When doing an sql with with distinct=true such as:

Query:QueryByCriteria from class de.intermediate.components.generated.projekt.ProjektValue where [fk_hauptProjekt < 1, [subProjekte.workorders.fk_statusList <> 105], mandant = 001, deleted = false]
org.apache.ojb.broker.PersistenceBrokerSQLException: com.sap.dbtech.jdbc.exceptions.DatabaseException: [-5014] (at 23): Missing definite column name:MANDANT
com.sap.dbtech.jdbc.exceptions.DatabaseException: [-5014] (at 23): Missing definite column name:MANDANT
[-5014] (at 23): Missing definite column name:MANDANT

OJB does the following count SQL statement:

SELECT count(distinct mandant || ID) FROM projekt A0,projekt A1,workorder A2 WHERE A1.mandant=A2.mandant AND A1.ID=A2.fk_projekt AND A0.mandant=A1.mandant AND A0.ID=A1.fk_hauptProjekt AND (((( A0.fk_hauptProjekt < ?) AND (A2.fk_statuslist <> ?)) AND A0.mandant = ?) AND A0.DELETED = ?)

This obviously fails since the usual A?.* table specifier is missing from the select clause.

I post this as a bug since it is OJB which issues the offending count statement. What I am trying to do is to retrieve all projects which have subprojects which have workorders with a certain status. Since each project may have mutliple subprojects and workorders I issue the distinct to retrieve each project at most once.

Yours
S.Schlösser