order by 2 columns in ACE
Posted in 1995
Topics: SQL Development & Query Writing
Hi all, I'm trying to improve an ACE report, but it's doing my head in!! What I've got is one column where some entries are preceded by a "?", but I want to group by this column disregarding any "?"s. First of all I defined a variable and tried: before group of column if column[1,1] = "?" then let variable = column[2,30] else let variable = column print column Of course (with 20:20 hindsight) this gets rid of all the ?s, but the entries are still ordered by the original column i.e. the report doesn't print the "?", but entries that have a "?" still appear in a separate group. My next attempt was to try and use temp tables: select entnum, genus, subsample, org_no, a_or_d from entdiagd where genus = $gen into temp temp1; select entnum, species from entdiagd where species[1,1] != "?" into temp temp2; select entnum, species[2,30] species from entdiagd where species[1,1] = "?" into temp temp3; select samptab.entnum, ack_date, domero, crop, grower, grower_addr1, samptab.orig_country, genus, species, subsample, org_no, a_or_d from samptab, temp1, temp2, temp3 where samptab.entnum = temp1.entnum and samptab.entnum = temp2.entnum and samptab.entnum = temp3.entnum and ack_date >= $pstart and ack_date <= $pend and samptab.croptype not in ("CUTTING", "GROWING", "SEEDLING") order by species, ack_date, crop, domero, subsample, org_no end ... before group of species print "------------------------------------------------------------------------------" skip 1 line print "~i", genus clipped, 1 space, species clipped, "~j" Of course, (20:20 etc) this compiles, runs all four statements and returns the error message "Ambiguous column species", as it should! (My problem is that I wanted the column to be ambiguous, hoping ACE would mix the columns together...) The upshot is that the manuals and I have exhausted all our ideas, plus a fair amount of time, when maybe I should have asked "the list" at the outset! CAN ANYBODY HELP. As always, I am using ISQL v4.10.UE1, SE, on a SPARC 20. Cheers, Richard. (no sig file, this message is long enough already!)