SELECT olh.ofndr_num,blc.body_loc_desc FROM ofndr_loc_hist olh, body_loc_cd blc
WHERE olh.end_dt IS NULL AND olh.body_loc_cd = blc.body_loc_cd
AND blc.loc_typ_cd = "N"
ORDER BY blc.body_loc_desc, olh.ofndr_num
I would like, when blc.body_loc_desc changes value, to spit out a line with null, and blc.body_loc_desc in it. (I need to feed this into iReport, which doesn't seem to want to do grouping the way that it should, and I need something that gives me a clear break between groups.) Any suggestions how to do this with Informix SQL?
I'm not familiar with Informix, but all flavors of SQL are similar. What you want to do is union this query with another query which gets the distinct values in body_loc_cd. Try this:
ReplyDeleteSELECT olh.ofndr_num,blc.body_loc_desc FROM ofndr_loc_hist olh, body_loc_cd blc
WHERE olh.end_dt IS NULL AND olh.body_loc_cd = blc.body_loc_cd
AND blc.loc_typ_cd = "N"
UNION SELECT NULL as ofndr_num, body_loc_desc FROM body_loc_cd
ORDER BY body_loc_desc, ofndr_num
It may not be exactly right, but play around with it and you should get it to work.