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

[Robert r. Sanders]

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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 

--
   Robert r. Sanders
   Chief Technologist
   iPOV
   www.ipov.net
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]