using decode and column alias with "group by"? 1

[wilkinsonm]

Hi SQL-ers!

I've been fighting this query for two days straight, and I have tried it in every different convulution that I can think of, but it just isn't happy. The problem seems to lie in my inability to "group by" the column that I alias as chr_strand (line 15). If I try to group by chr_strand, the alias is not recognized (ORA-00904: "CHR_STRAND": invalid identifier), but if I remove it I get a ORA-00979: not a GROUP BY expression error on line 15. I have also tried grouping by e.contig_strand, in case it was not recognizing the alias... no go. If I change line 15 so that it is "max(decode....) chr_strand", then I have no problem at all, so I know that the rest of the query is correct, but as soon as I allow line 15 to return more than one value (i.e. as soon as I need to do a group by) I hit a wall - the alias is not recognized as valid group_by's, nor are the columns that are in the decode statement...

Can anyone see the problem with the query below?

Any advice appreciated!


1 create table js5_exon_1_LITE as
2 select e.exon_id, ch.name chr_name,
3 min(decode
4 (sgp.contig_ori,
5 1,(e.contig_start+sgp.chr_start-sgp.contig_start),
6 (sgp.chr_start+sgp.contig_end-e.contig_end)
7 )
8 ) chr_start,
9 max(decode
10 (sgp.contig_ori,
11 1,(e.contig_end+sgp.chr_start-sgp.contig_start),
12 (sgp.chr_start+sgp.contig_end-e.contig_start)
13 )
14 ) chr_end,
15 decode (sgp.contig_ori,1, e.contig_strand, -e.contig_strand) chr_strand
16 from exon e, assembly sgp, chromosome ch
17 where e.contig_id = sgp.contig_id and sgp.chromosome_id = ch.chromosome_id
18 group by e.exon_id, ch.name, chr_strand
19*
group by e.exon_id, ch.name, chr_strand
*
ERROR at line 18:
ORA-00904: "CHR_STRAND": invalid identifier

 

[BJCooperIT]

Try this:

group by e.exon_id, ch.name,
decode (sgp.contig_ori,1, e.contig_strand, -e.contig_strand)

Good luck!
(select * from life where brain is not null)
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[wilkinsonm]

You should see the smile on my face now!! that worked like a charm!

What's more, I learned something new about Oracle SQL

thanks BJCooperIT!!

Mark

 

[dnoeth]

You can't use an alias in group by, so instead of copying the decode you can use a derived table:

select *
from
(your select..
decode (sgp.contig_ori,1,
e.contig_strand,-e.contig_strand) chr_strand
) dt
group by
chr_strand


Dieter