Simple SQL

[Steve95]

Hi All

Iam new to SQL espcially sql 2005, I have a select statement where Iam selecting to fields from a table and then at the same same adding the 2 fields to and outputting as a new column denoted with a t.

I want to refine the select by the new the results that have been generated ie.

where t = '200604'

Anyway I have posted my whole statement,please can you help

SELECT procesyr, ((procesyr*100) + procesqtr) t, procesqtr
where t = '200604'
from ReporRates

Please can you help

Mnay Thanks

 

[bborissov]

SELECT procesyr, ((procesyr*100) + procesqtr) t, procesqtr  
HAVING ((procesyr*100) + procesqtr) = 200604
from ReporRates

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[r937]

boris, HAVING should come after FROM ;-)

what i think you meant to write was:

SELECT procesyr
     , ((procesyr*100) + procesqtr) t
     , procesqtr  
  FROM ReporRates
 WHERE ((procesyr*100) + procesqtr) = 200604

if you're going to use the ugly HAVING without a GROUP BY, you might as well use the column alias

SELECT procesyr
     , ((procesyr*100) + procesqtr) t
     , procesqtr  
  FROM ReporRates
HAVING t = 200604

alternatively,

SELECT *
  FROM (
       SELECT procesyr
            , ((procesyr*100) + procesqtr) t
            , procesqtr  
         FROM ReporRates
       ) as d
 WHERE t = 200604

you could also do this, which will be more efficient, assuming that there is an index on procesyr and procesqtr --

SELECT procesyr
     , ((procesyr*100) + procesqtr) t
     , procesqtr  
  FROM ReporRates
 WHERE procesyr = 2006
   AND procesqtr = 04

r937.com | rudy.ca

 

[bborissov]

You are right of course, all other statements except field list must come after FROM clause.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP