Re: Help required with SQL query

Ian Clark wrote: > > Hi All, > > Picture, if you will, a table with two columns and the following > data: > > lookupid description > 001 trousers > 002 jackets > 003 shirts > 004 suits > 005 knitwear > 006 waistcoats > > Now, what I want to do is perform a select statement that when > processed will show the following output: > > 001 002 003 other > > where 'other' is the grouping of 004, 005 and 006 > > As things stand, the ACE report I am modifying has just one select > statement and processes all of the above data as seperate items but > now I need to group a few of them into one. I'm sure I can come up > with something involving a temporary table but it would be nice to do > it in one. > > One further thing, although I don't need to do it for this report, > let's say I wanted to produce the following output: > > other1 other2 other3 > > where 'other1' is the grouping of 001 and 002, 'other2' is the > grouping of 003 and 004, and 'other3' is the grouping of 005 and 006. > > Is this doable in one go? Interesting one this isn't it? :-) > > Any help/assistance given would be extremely very welcome. > > Cheers, Ian. Two ideas spring to mind: 1. Create a table which encodes the rows the way you want them reported, ie 001 001 002 002 003 003 004 OTHER 005 OTHER 006 OTHER Then join and group by that. Alternatively, you could do it manually in ACE with lots of local variables. You're a bit vague about what you want to do with the data, but assuming part of the problem is organising things in a row when they arrive in the wrong order, the solution is a long string where you use string[i,j] to pick out sections of it like an array. This is easier in 4GL, which has real arrays and allows non-SELECT SQL in reports. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 Mail: Peter.Lancashire.PL1@bayer.co.uk --- My Internet plumbing does not allow me to mail and post news together. Sorry. All opinions are my own and not those of Bayer plc. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/