quigleyryan
asked on
SQL Select doesn't work with case statement
select 'any' as field1
union
SELECT distinct [soemthing] as field1 FROM table1
order by isnumeric(field1), case when isnumeric(field1) = 1 then cast(field1 as decimal) else null end, field1
receive error: ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
I also tried
select 'any' as field1
union
SELECT [soemthing] as field1 FROM table1
group by field1
order by isnumeric(field1), case when isnumeric(field1) = 1 then cast(field1 as decimal) else null end, field1
which gives me an error that field1 is an invalid column... please help.
thanks
ASKER
No that doesn't work.. but thanks
OK, do you want to order the entire result set or just the second query so that "any" is always on top? And are you sure you want to use UNION instead of UNION ALL?
union means to join to select statements
look at the examples
look at the examples
select ename
union
select dname from dept;
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
select ename from emp
union
select dname from dept
ENAME
--------------
ACCOUNTING
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
OPERATIONS
RESEARCH
SALES
SCOTT
SMITH
TURNER
WARD
18 rows selected.
ASKER
i want any at the top... and the second query has alpha numerics that need to be ordered.
this query works when i don't have the case statement in the order by :-(
this query works when i don't have the case statement in the order by :-(
ASKER
leoahmad: please notice the '' around Any...
select field1
from (
select 'any' as field1
union
SELECT [soemthing] as field1 FROM table1
group by field1
) as derived
order by isnumeric(field1), case when isnumeric(field1) = 1 then cast(field1 as decimal) else null end, field1
from (
select 'any' as field1
union
SELECT [soemthing] as field1 FROM table1
group by field1
) as derived
order by isnumeric(field1), case when isnumeric(field1) = 1 then cast(field1 as decimal) else null end, field1
ASKER
Scott - that kinda worked.... if i don't use the field1 and only use the column name - why wouldn't the alias work on group by?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks Scott
union
SELECT distinct [soemthing] as field1 FROM table1
order by isnumeric(field1), case when isnumeric(field1) = 1 then cast(field1 as decimal) else null end, field1