Jakob, et al Yes I have, that's how I started out. If you look at the bottom this this thread you will see my original code which uses exactly that Criteria.PARENT_QUERY_PREFIX. I am unable to get it to work to retrieve the same results as the raw SQL does. Hence why I am at a loss here.
Thanks Roman -----Original Message----- From: Jakob Braeuchi [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 10:17 To: OJB Users List Subject: RE: OJB sub query with effective date and effective sequence logic 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
