OJB sub query with effective date and effective sequence logic

["Stark, Roman" <st...@indiana.edu>]

Hi All

I am evaluating OJBs functionality for our project.  Currently most of
the sql is written by hand since it's complex logic against People Soft
(ERP) tables.  
Right now I am trying to use sub query to query data from just one table
based on effective dates.  The sql I want to create is:

SELECT * FROM ps_names a
WHERE a.emplid = '0000005170' AND a.name_type = 'PRF'
AND a.effdt = (SELECT MAX(b.effdt) FROM ps_names b WHERE a.emplid =
b.emplid AND a.name_type = b.name_type)

The code I wrote (think it would work) is:

	broker = PersistenceBrokerFactory.defaultPersistenceBroker();
	// Build sub query for effdt logic
      ReportQueryByCriteria subQry = null;
      subCrit.addEqualToField("EMPLID", Criteria.PARENT_QUERY_PREFIX +
"EMPLID"); 
      subCrit.addEqualToField("NAME_TYPE", Criteria.PARENT_QUERY_PREFIX
+ "NAME_TYPE"); 
      subQry = QueryFactory.newReportQuery(PsNames.class, subCrit);
      subQry.setAttributes(new String[] {"MAX(EFFDT)"});
        
      // Build main query
      crit.addColumnEqualTo("EFFDT", subQry);
      crit.addColumnEqualTo("EMPLID", "0000005170");
      crit.addColumnEqualTo("NAME_TYPE", "PRF");
      
	// Execute  
      Query mainQry = QueryFactory.newQuery(PsNames.class, crit);
      Collection results = broker.getCollectionByQuery(mainQry);

Unfortunately, the result is nothing.

Table definition:

EMPLID	N	VARCHAR2	11	Primary key
NAME_TYPE	N	VARCHAR2	3	Primary key		
EFFDT	N	DATE	7			Primary key
FIRST_NAME	N	VARCHAR2	30			
MIDDLE_NAME	N	VARCHAR2	30			
LAST_NAME	N	VARCHAR2	30			
NAME		N	VARCHAR2	50			
...

What am I missing?  Am I not using the sub query right?  This is only a
simple table that doesn't contain an effective sequence in addition to
the effective date, let alone joining two tables both with effective
date and sequence logic.

Any ideas are appreciated.
Thanks
Roman


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

Re: OJB sub query with effective date and effective sequence logic

["Robert r. Sanders" <ro...@ipov.net>]

I am not too familiar with the ReportQueries; but have you tried turning 
on SQL logging in the OJB.properties file to see what SQL is being 
produced?  A breif  look at the documentation for ReportQueries 
(doc/docu/guides/query.html#Report+Queries) suggests to me that the 
current implementation may be a little too limited to do what you want - 
for instance I think the setAttributes() call is actually setting the 
Object properties/attributes to return  (if I am wrong I hope someone 
more knowledgeable can corret this).   You might try the 
Criteria.addSql("") to add the MAX() statement (although how  you would 
then retrieve it I can't see). 

Alternatively, you can use either: 

persistenceBroker.serviceJdbcAccess() or persistenceBroker.serviceConnectionManager().getConnection() to get an object (JdbcAccess or Connection respectively) from which you can execute arbitrary SQL.



Stark, Roman wrote:

>Hi All
>
>I am evaluating OJBs functionality for our project.  Currently most of
>the sql is written by hand since it's complex logic against People Soft
>(ERP) tables.  
>Right now I am trying to use sub query to query data from just one table
>based on effective dates.  The sql I want to create is:
>
>SELECT * FROM ps_names a
>WHERE a.emplid = '0000005170' AND a.name_type = 'PRF'
>AND a.effdt = (SELECT MAX(b.effdt) FROM ps_names b WHERE a.emplid =
>b.emplid AND a.name_type = b.name_type)
>
>The code I wrote (think it would work) is:
>
>	broker = PersistenceBrokerFactory.defaultPersistenceBroker();
>	// Build sub query for effdt logic
>      ReportQueryByCriteria subQry = null;
>      subCrit.addEqualToField("EMPLID", Criteria.PARENT_QUERY_PREFIX +
>"EMPLID"); 
>      subCrit.addEqualToField("NAME_TYPE", Criteria.PARENT_QUERY_PREFIX
>+ "NAME_TYPE"); 
>      subQry = QueryFactory.newReportQuery(PsNames.class, subCrit);
>      subQry.setAttributes(new String[] {"MAX(EFFDT)"});
>        
>      // Build main query
>      crit.addColumnEqualTo("EFFDT", subQry);
>      crit.addColumnEqualTo("EMPLID", "0000005170");
>      crit.addColumnEqualTo("NAME_TYPE", "PRF");
>      
>	// Execute  
>      Query mainQry = QueryFactory.newQuery(PsNames.class, crit);
>      Collection results = broker.getCollectionByQuery(mainQry);
>
>Unfortunately, the result is nothing.
>
>Table definition:
>
>EMPLID	N	VARCHAR2	11	Primary key
>NAME_TYPE	N	VARCHAR2	3	Primary key		
>EFFDT	N	DATE	7			Primary key
>FIRST_NAME	N	VARCHAR2	30			
>MIDDLE_NAME	N	VARCHAR2	30			
>LAST_NAME	N	VARCHAR2	30			
>NAME		N	VARCHAR2	50			
>...
>
>What am I missing?  Am I not using the sub query right?  This is only a
>simple table that doesn't contain an effective sequence in addition to
>the effective date, let alone joining two tables both with effective
>date and sequence logic.
>
>Any ideas are appreciated.
>Thanks
>Roman
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>For additional commands, e-mail: ojb-user-help@db.apache.org
>
>  
>

-- 
    Robert r. Sanders
    Chief Technologist
    iPOV
    www.ipov.net


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org