SQL Code HELP

[Shin25]

Hi All

I have the followin sql query in sql 2005 and I want to return where TEST = 'Yes', please can
you advise how I would do that?


SELECT
File.dbo.MI.Premium as a,
File.dbo.MI.Num,
CASE
when Premium = 1 then 'Yes'
when Premium = 0 then 'No'
else '?'
END AS TEST
FROM
File.dbo.MIRep
WHERE
--TEST = 'Yes' and
File.dbo.MIRep."_Process" = 'RI'

Many Thanks

 

[SQLSister]

Where premium = 1

Of course if you do this you no longer need the caste statement and can just replace it with 'Yes' as Test

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

There are a couple ways of doing this.

SELECT
File.dbo.MI.Premium as a,
File.dbo.MI.Num,
CASE
    when Premium = 1 then 'Yes'
    when Premium = 0 then 'No'
    else '?'
END AS TEST
FROM
  File.dbo.MIRep
WHERE
    Premium = 1 and
    File.dbo.MIRep."_Process"  = 'RI'

SELECT
File.dbo.MI.Premium as a,
File.dbo.MI.Num,
CASE
    when Premium = 1 then 'Yes'
    when Premium = 0 then 'No'
    else '?'
END AS TEST
FROM
  File.dbo.MIRep
WHERE
  'Yes' = CASE
            when Premium = 1 then 'Yes'
            when Premium = 0 then 'No'
            else '?'
          END
    And File.dbo.MIRep."_Process"  = 'RI'

Select a, num, Test
From   (
        SELECT
        File.dbo.MI.Premium as a,
        File.dbo.MI.Num,
        CASE
            when Premium = 1 then 'Yes'
            when Premium = 0 then 'No'
            else '?'
        END AS TEST
        FROM
          File.dbo.MIRep
        WHERE
          File.dbo.MIRep."_Process"  = 'RI'  
        ) As SomeAlias
Where  Test = 'Yes'

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JoeAtWork]

Of course if you do this you no longer need the caste statement and can just replace it with 'Yes' as Test

LOL! Nicely spotted!