Hi Roman,
AFAIK, the subquery *may* be executed as a separate query (because of
extents) so the dependent attributes get a little tricky and may not be
supported. But that being said and done, there are some errors in your
code.
Criteria.addEqualToColumn("ColumnName", "ColumnName") expects two column
names.
Criteria.addEqualToField("FieldName", "FieldName") expects two field
names (mapped to column names).
Criteria.addEqualTo("AttributeName", Value) expects a field name (mapped
to a column name, but will accept a column name) and a value to build a
where clause.
So maybe something like this would work:
broker = PersistenceBrokerFactory.defaultPersistenceBroker();
// Build sub query for effdt logic
ReportQueryByCriteria subQry = null;
subCrit.addEqualTo("EMPLID", "0000005170");
subCrit.addEqualTo("NAME_TYPE", "PRF");
subQry = QueryFactory.newReportQuery(PsNames.class, subCrit);
subQry.setAttributes(new String[] {"MAX(EFFDT)"});
// Build main query
crit.addIn("EFFDT", subQry);
crit.addEqualTo("EMPLID", "0000005170");
crit.addEqualTo("NAME_TYPE", "PRF");
// Execute
Query mainQry = QueryFactory.newQuery(PsNames.class, crit);
Collection results = broker.getCollectionByQuery(mainQry);
-----Original Message-----
From: Stark, Roman [mailto:[EMAIL PROTECTED]
Sent: Monday, November 22, 2004 8:13 AM
To: OJB Users List
Cc: Stark, Roman
Subject: OJB sub query with effective date and effective sequence logic
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
