hi roman, wallace,
have you tried using Criteria.PARENT_QUERY_PREFIX for the attribute of the
subquery ?
subCrit.addEqualToField("EMPLID", Criteria.PARENT_QUERY_PREFIX + "EMPLID");
so the subqueries refers to the field of the enclosing parent query.
for an example see
http://db.apache.org/ojb/docu/guides/query.html#subqueries
hth
jakob
> Hi Wallace
>
> While your suggestion below works, I do find it a bit silly to have to
> specify the key values in the sub query and in the outer query. That
> redundancy defeats the purpose of the sub query I think.
>
> Also, the mechanism described will not allow the sql that were to
> retrieve all the current primary names:
>
> SELECT * FROM ps_names a WHERE a.name_type = 'PRI'
> AND a.effdt = (SELECT MAX(b.effdt) FROM ps_names b WHERE a.emplid =
> b.emplid AND a.name_type = b.name_type AND b.effdt <= SYSDATE)
>
> I was really excited when I read about the sub query functionality but
> it doesn't seem to working as I thought it would.
>
> Does anyone have any suggestions to implement these type of queries?
>
> Thanks
> Roman
>
> -----Original Message-----
> From: Gelhar, Wallace Joseph [mailto:[EMAIL PROTECTED]
> Sent: Monday, November 22, 2004 10:14
> To: OJB Users List
> Subject: RE: OJB sub query with effective date and effective sequence
> logic
>
> 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]
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
--
Geschenkt: 3 Monate GMX ProMail + 3 Top-Spielfilme auf DVD
++ Jetzt kostenlos testen http://www.gmx.net/de/go/mail ++
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
