April 28, 2006 at 12:16 pm
I need to be able to use the Alias in the WHERE clause. This was possible in Sybase and I need to provide the same functionality in SQL Server.
Users can create any type of Alias field. This is simply a made up example.
SELECT LEFT((CASE
WHEN DxAge >= '0' AND DxAge <= '10' THEN 'a 0 to 10'
WHEN DxAge >= '11' AND DxAge <= '20' THEN 'b 11 to 20'
WHEN DxAge >= '21' AND DxAge <= '30' THEN 'a 21 to 30'
WHEN DxAge >= '31' AND DxAge <= '40' THEN 'c 31 to 40'
WHEN DxAge >= '41' AND DxAge <= '50' THEN 'q 41 to 50'
WHEN DxAge >= '51' AND DxAge <= '60' THEN 'a 51 to 60'
WHEN DxAge >= '61' AND DxAge <= '70' THEN 'b 61 to 70'
WHEN DxAge >= '71' AND DxAge <= '80' THEN 'm 71 to 80'
WHEN DxAge >= '81' AND DxAge <= '90' THEN 'r 81 to 90'
ELSE 'Over 90' END),8) as Age_Group FROM (Pt INNER JOIN Dx on Dx.PtID = Pt.PtID) WHERE (Age_Group LIKE 'a%' AND Pt.FacID IN ('1') AND Dx.StatFlag IN ('C'))
Is there a workaround for this? I have to generate the SQL for any and all possible Alias fields. What I'm saying is that I have no control over how simple or complex the user makes the field.
Any help is greatly appreciated.
April 30, 2006 at 4:12 am
It's impossible to use column alias in WHERE clause.
For example:
select t_id, price*amount as x from t where x>1
will give an error "Invalid column name 'x'."
I could not find in BOL this restriction.
The only sentence I found in BOL about it:
"A column alias that is defined in the select list cannot be used to specify a grouping column."
It seems me very strange, because most of RDBMS allows such things.
May 3, 2006 at 10:14 am
Try this below if you have I problem I will need table create scripts to fix it.
SELECT Age_Group
FROM
(
SELECT
LEFT((
CASE
WHEN DxAge >= '0' AND DxAge = '11' AND DxAge = '21' AND DxAge = '31' AND DxAge = '41' AND DxAge = '51' AND DxAge = '61' AND DxAge = '71' AND DxAge = '81' AND DxAge <= '90' THEN 'r 81 to 90'
ELSE 'Over 90' END
),8) as Age_Group,
Pt.FacID, Dx.StatFlag
FROM (Pt INNER JOIN Dx on Dx.PtID = Pt.PtID)
) AS X1(Age_Group, FacID, StatFlag)
WHERE (X1.Age_Group LIKE 'a%' AND X1.FacID IN ('1') AND X1.StatFlag IN ('C'))
Tim S
May 3, 2006 at 10:41 am
1) Try this.
SELECT LEFT((CASE
WHEN DxAge >= '0' AND DxAge <= '10' THEN 'a 0 to 10'
WHEN DxAge >= '11' AND DxAge <= '20' THEN 'b 11 to 20'
WHEN DxAge >= '21' AND DxAge <= '30' THEN 'a 21 to 30'
WHEN DxAge >= '31' AND DxAge <= '40' THEN 'c 31 to 40'
WHEN DxAge >= '41' AND DxAge <= '50' THEN 'q 41 to 50'
WHEN DxAge >= '51' AND DxAge <= '60' THEN 'a 51 to 60'
WHEN DxAge >= '61' AND DxAge <= '70' THEN 'b 61 to 70'
WHEN DxAge >= '71' AND DxAge <= '80' THEN 'm 71 to 80'
WHEN DxAge >= '81' AND DxAge <= '90' THEN 'r 81 to 90'
ELSE 'Over 90' END),8) as Age_Group
FROM dbo.Pt Pt
INNER JOIN dbo.Dx DX
ON Dx.PtID = Pt.PtID
WHERE
(CASE
WHEN DxAge >= '0' AND DxAge <= '10' THEN 'a'
WHEN DxAge >= '21' AND DxAge <= '30' THEN 'a'
WHEN DxAge >= '51' AND DxAge <= '60' THEN 'a'
END) = 'a' AND Pt.FacID IN ('1') AND Dx.StatFlag IN ('C'))
2)Your Left 8 is going to truncate most of your values for output like 'r 81 to 90' will output as 'r 81 to ' (without the quotes, here to show cutoff).
3) If DxAge = 110 (just on chance) the answer would be 'b 11 to 20' because you are using character comparison and not numeric. Thus it would think the following set is valid for that answer.
11
110
1100
111
1110
112
1120
...
20
I think you may need to think about your logic a bit more.
May 3, 2006 at 3:34 pm
It is not strange if you know the order in which the various parts of a SQL query are processed and become available to later parts. Here is a link to another post where Joe Celko explains how a SELECT statement works.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=263363
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply