locked
Is it possible to do this? Query help RRS feed

  • Question

  • Hi,

    I have this state where I create a column called "Test" on the fly but I want to use this column in my where clause. Is it possible to do it or is there anyway I can use the "Test" column to filter data. Please help. Here is my statement

     

     

    (case when rm.status='Deceased' and rm1.first_name is not null and rm1.status='Active' then '1'

     

    when rm.status='Deceased' and rm1.first_name is not null and rm1.status='Deceased' then '2'

     

    when rm.status='Deceased' and rm1.first_name is null and rm1.status is null then '3'

     

    else ''

     

    end) as 'Test',

    Here is my filter that I tried to use but does not work.

    and

     

    'Test' not in ('3','2')

    Any help would be appreciated. Thank you

    Wednesday, May 5, 2010 7:35 PM

Answers

  • Yes you can do this... just use nested SELECT statements:

    SELECT Field1, Field2, Test
    FROM(
     SELECT Field1, Field2,
     (case when rm.status='Deceased' and rm1.first_name is not null and rm1.status='Active' then '1'
     when rm.status='Deceased' and rm1.first_name is not null and rm1.status='Deceased' then '2'
     when rm.status='Deceased' and rm1.first_name is null and rm1.status is null then '3'
     else ''
     end) as Test,
     FROM dbo.yourdatabase
    ) as Tbl
    WHERE Test not in ('3','2')



    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    Wednesday, May 5, 2010 7:38 PM
  • Hi YPandey,

    Aaron's query will work fine.

    I just want to give you another choice. We can use the Common Table Expressions (CTE) in TSQL to do so too:

    ;With CTE AS
    (
    SELECT ... (case when rm.status='Deceased' and rm1.first_name is not null and rm1.status='Active' then '1' when rm.status='Deceased' and rm1.first_name is not null and rm1.status='Deceased' then '2' when rm.status='Deceased' and rm1.first_name is null and rm1.status is null then '3' else '' end) as 'Test',
    FROM Table
    )
    SELECT ... FROM CTE 
    WHERE Test=@Test

    For more information about CTE, please see:
    http://msdn.microsoft.com/en-us/library/ms175972.aspx

    If you have any more questions, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Marked as answer by YPandey Friday, May 7, 2010 2:43 PM
    Friday, May 7, 2010 1:45 PM

All replies

  • Yes you can do this... just use nested SELECT statements:

    SELECT Field1, Field2, Test
    FROM(
     SELECT Field1, Field2,
     (case when rm.status='Deceased' and rm1.first_name is not null and rm1.status='Active' then '1'
     when rm.status='Deceased' and rm1.first_name is not null and rm1.status='Deceased' then '2'
     when rm.status='Deceased' and rm1.first_name is null and rm1.status is null then '3'
     else ''
     end) as Test,
     FROM dbo.yourdatabase
    ) as Tbl
    WHERE Test not in ('3','2')



    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    Wednesday, May 5, 2010 7:38 PM
  • Hi Aaron,

       Thank you for your reply. The issue is that "Test" is not a column in my table. I am just creating the field in the middle of my query so can i still use your logic. Please let me know. Thank you.

    Wednesday, May 5, 2010 7:47 PM
  • Yes, I understand this.  What I did was create a secondary SELECT statement that selects all the items you selected in the first SELECT statement, along with "Test".  You can take this logic and apply it to your situation. 

    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    • Marked as answer by YPandey Wednesday, May 5, 2010 8:07 PM
    • Unmarked as answer by YPandey Wednesday, May 5, 2010 8:07 PM
    Wednesday, May 5, 2010 8:03 PM
  • Thank you for your help.
    • Marked as answer by YPandey Wednesday, May 5, 2010 8:07 PM
    • Unmarked as answer by YPandey Wednesday, May 5, 2010 8:07 PM
    Wednesday, May 5, 2010 8:07 PM
  • Hi YPandey,

    Aaron's query will work fine.

    I just want to give you another choice. We can use the Common Table Expressions (CTE) in TSQL to do so too:

    ;With CTE AS
    (
    SELECT ... (case when rm.status='Deceased' and rm1.first_name is not null and rm1.status='Active' then '1' when rm.status='Deceased' and rm1.first_name is not null and rm1.status='Deceased' then '2' when rm.status='Deceased' and rm1.first_name is null and rm1.status is null then '3' else '' end) as 'Test',
    FROM Table
    )
    SELECT ... FROM CTE 
    WHERE Test=@Test

    For more information about CTE, please see:
    http://msdn.microsoft.com/en-us/library/ms175972.aspx

    If you have any more questions, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Marked as answer by YPandey Friday, May 7, 2010 2:43 PM
    Friday, May 7, 2010 1:45 PM
  • Thank you Jin. I appreciate your help.
    Friday, May 7, 2010 2:43 PM