?
Solved

Referencing Variable Selected Fields in Where Clause

Posted on 2009-02-11
2
Medium Priority
?
306 Views
Last Modified: 2012-06-27
I'm trying to get a variable field name created with "AS" in the select clause to work in the Where clause.

Is this possible? Is there another solution?

This code works without the Var_Priorty <= 2
SELECT page_ID, page_title, 
CASE 
WHEN page_tag = 'howdy' THEN 1 
WHEN page_tag = 'doo' THEN 2 
ELSE 99 END As Var_Priority
FROM table_pages
WHERE page_ID > 100
AND Var_Priority <= 2

Open in new window

0
Comment
Question by:cnxmax
2 Comments
 
LVL 19

Accepted Solution

by:
folderol earned 2000 total points
ID: 23614958
For MS, the only solution is to nest select statements.  This could be a performance hit, although it makes the source more readable.
select * from
(
  SELECT page_ID, page_title, 
  CASE 
    WHEN page_tag = 'howdy' THEN 1 
    WHEN page_tag = 'doo' THEN 2 
    ELSE 99 END 
  As Var_Priority
FROM table_pages
WHERE page_ID > 100
) as derived_table
where derived_table.Var_Priority <= 2

Open in new window

0
 

Author Comment

by:cnxmax
ID: 23635282
That will work in a pinch. Thanks for your help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question