Re: SQL question

Hi Syed,
 
I don't think you can use column aliases in the other clauses, they are for display only, IIRC.
I think you'll have to use the full expression (select distinct SECT_I from...) in both the where- and order by-clauses.
 
Hope that helps,
Kim

-----Original Message-----
From: Syed.Alam [mailto:[email protected]]
Sent: den 16 maj 2002 22:22
To: [email protected]
Subject: SQL question


Hi
 
Even though I am using OLEDB driver, but it is more a SQL syntax related problem:
 
I am writing a query in which order clause is sort of dynamically built up like:
 
select POG_NUM_N ,DEPT_I , ( select distinct SECT_I from t_pog_detail where P.POG_NUM_N = POG_NUM_N ) as SECTOR
from 
pog_scma p
order by DEPT_I , 
COALESCE (dept_i, SECTOR)
 
or 

 
select POG_NUM_N ,DEPT_I , ( select distinct SECT_I from t_pog_detail where P.POG_NUM_N = POG_NUM_N ) as SECTOR
from 
pog_scma p
order by DEPT_I , 
case DEPT_I
 WHEN 52 THEN  ITEM_I
 ELSE  SECTOR
END

In both the cases, ISQL/W has probelm with column SECTOR with in CASE and COALESCE statement. What can be done here? 
 
Thanks
 
PS: I am using SQL 6.5