[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Re: How to do a Max in a sub query...

[arnieOag]

This compiles and runs but doesn't get rid of the first record.


  | from us.tx.state.oag.olrap.db.InfoApplication as apps
  | where
  | 	apps.codeStatus.statusId = :codeStatus 
  | 	and apps.infoWorkflows.emplid = :emplId
  | 	and apps.infoWorkflows.entryDate = ( select max(wf.entryDate)
  | 		from apps.infoWorkflows wf
  | 		where wf.infoApplication.applicationId = apps.applicationId
  | 	)
  | order by apps.createdDate asc
  | 

The SQL generated is a little off...


  | 13:13:04,031 INFO  [STDOUT] Hibernate: select infoapplic0_.application_id as applicat1_494_, infoapplic0_.fiscal_year_id as fiscal2_494_, infoapplic0_.evaluation_id as evaluation3_494_, infoapplic0_.status_id as status4_494_, infoapplic0_.base_award as base5_494_, infoapplic0_.closed_date as closed6_494_, infoapplic0_.created_date as created7_494_, infoapplic0_.emplid as emplid494_, infoapplic0_.hire_date as hire9_494_, infoapplic0_.job_code as job10_494_, infoapplic0_.last_eval_date as last11_494_, infoapplic0_.months_of_service as months12_494_, infoapplic0_.ok_with_bar as ok13_494_, infoapplic0_.submitted_date as submitted14_494_, infoapplic0_.calculated_award as calculated15_494_, infoapplic0_.actual_award as actual16_494_, infoapplic0_.award_date as award17_494_, infoapplic0_.cr_who as cr18_494_, infoapplic0_.cr_when as cr19_494_, infoapplic0_.up_who as up20_494_, infoapplic0_.up_when as up21_494_ from info_application infoapplic0_, info_workflow infoworkfl1_, info_workflow infoworkfl2_ where infoapplic0_.application_id=infoworkfl2_.application_id and infoapplic0_.application_id=infoworkfl1_.application_id and infoapplic0_.status_id=? and infoworkfl1_.emplid=? and infoworkfl2_.entry_date=(select max(infoworkfl3_.entry_date) from info_workflow infoworkfl3_ where infoapplic0_.application_id=infoworkfl3_.application_id and infoworkfl3_.application_id=infoapplic0_.application_id) order by infoapplic0_.created_date asc
  | 
  | 

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4118813#4118813

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4118813