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?
1 comment:
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:
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"
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.
Post a Comment