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]
