Friday, August 20, 2010

SQL Question

I have a SELECT statement that returns a couple of fields. 

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:

  1. 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.

    ReplyDelete