Set Based results question.....
-
I'm a recent convert to TSQL... trying to determine why the "where" part of the "select" statement shown below, the part which narrows the results down to the "flagged" records, blows up. I realize it's attempting to filter on a derived column (ConcStatus) comprised of a CASE statement, but what's the problem with doing that? I can't find any reference to doing this in the BOL or my textbooks…
Here's the select statement… It works fine until the WHERE part is included…
SELECT Permnum,stulink,schoolnum,FirstName,Lastname,Grade,Gender,Concschool,Concishome,ConcStatus =Case
WHEN (concschool = '')and (concishome = 'X') then 'FLAG'
WHEN (schoolnum = concschool)and (concishome = 'X') then 'FLAG'
WHEN (schoolnum = concschool)and (concishome <> 'X') then 'FLAG'
Else 'OK'
End
Where ConcStatus = 'flag'
FROM CONCTEST
ORDER BY permnum asc
Thanks in advance all!
-
Tom - your "from" clause should come before your "where" clause...
FROM CONCTEST Where ConcStatus = 'flag'
...
but what is your goal - I'm confused about your select which is filtering on all rows where "concStatus = 'flag'" anyway...so why the case statement ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
ConcStatus is a column alias, assigned to an expression in the SELECT portion. You can't reference this alias in the WHERE, ORDER BY or GROUP BY.
If you need it in the WHERE, you need to re-enter the entire expression:
WHERE (concschool = '' and concishome = 'X') OR
(schoolnum = concschool and concishome = 'X') OR
(schoolnum = concschool and concishome <> 'X')
At which point, it might become clear that the last 2 of those expressions need to be looked at again. Same expression, different by only an equals versus a not-equals. Hmmm.
-
Except I think that this is also an actual column name in the table(
)...which's why I was confused since if they're being filtered by "concStatus = 'flag'" then this column is going to say 'flag' anyway...
**ASCII stupid question, get a stupid ANSI !!!** -
Just try the below Sql. Just giving a shot that it 'may' work
SELECT Permnum,stulink,schoolnum,FirstName,Lastname,Grade,Gender,Concschool,Concishome,Case WHEN (concschool = '')and (concishome = 'X') then 'FLAG'
WHEN (schoolnum = concschool)and (concishome = 'X') then 'FLAG'
WHEN (schoolnum = concschool)and (concishome <> 'X') then 'FLAG'
Else 'OK'
End
FROM CONCTEST
Where ConcStatus = 'flag'
ORDER BY permnum asc
-
Please look at the original SQL:
ConcStatus =Case
Notice the = ? That is old-style SQL syntax for assigning an alias to an output column in the resultset. Therefore ConcStatus is an alias and can't be referenced in the WHERE.
-
Thanks.. what should it be ???
-
Tried that... got the same message... puzzling...
What I'm trying to do here is find rows in the table where the Concschool field (school a student is concurrently enrolled in), the "conishome" field (field showing whether or not the school the student is concurrently enrolled is his "home" school) and the schoolnum field (the school owning the row in the table) are configured incorrectly... per my case statement. I did it in the "CASE" manner so the records would show with an extra colum with the word "FLAG" in it, because that allows sorting by student to show records from other schools around the bad one, which helps to determine what the flagged row's configuration of the three fields in question should be...
All this effects school enrollment counts.. so pretty important...
Thanks!
-
You don't use expressions in the SELECT to set flags, if the only purpose of those flags is to filter.
Filtering is done in the WHERE, so place the filter expression there:
SELECT Permnum,
stulink,
schoolnum,
FirstName,
Lastname,
Grade,
Gender,
Concschool,
Concishome,
'FLAG' As ConcStatus
FROM CONCTEST
WHERE (concschool = '' and concishome = 'X') OR
(schoolnum = concschool and concishome = 'X') OR
(schoolnum = concschool and concishome <> 'X')
ORDER BY permnum asc
[Edit] If you need the hard-coded word 'FLAG' to appear in the result set, just hard-code it and append a column alias.
-
Thanks .. this did produce a result set, but also removed the flag column, which is a problem... Here's what I'm trying in my ham handed way to do....
I need to find rows in the table where the Concschool field (field showing the school a student is concurrently enrolled in), the "conishome" field (field showing whether or not the school the student is concurrently enrolled is his "home" school) and the schoolnum field (the school owning the row in the table) are configured incorrectly... per my case statement. I did it in the "CASE" manner so the records would show with an extra colum with the word "FLAG" in it, because that allows sorting by student to show records from other schools around the bad one, which helps to determine what the probem is with the flagged row's by comparing it to the other schools the student is concurrently enrolled in
All this effects school enrollment counts.. so pretty important...
Thanks!
-
But then you had a filter, looking for only the word 'Flag' ?
If the intention was to show some records as 'Flag' and some records as 'OK', why the filter to remove the 'OK' records ?
-
PW - not sure if your post was addressed to me, but thought I'd explain what I meant when I said that I believe this column is being used both as an alias as well as being an actual column in the table......nothing illustrates better than an example, so here's one from "Northwind"...
SELECT CustomerID, CompanyName = CASE WHEN ContactName LIKE 'A%' THEN 'A-Company' WHEN ContactName LIKE 'B%' THEN 'B-Company' ELSE 'MY-Company' END FROM Customers WHERE CompanyName LIKE 'A%'....
My confusion stemmed from the fact that since the rows were being filtered on the column = 'flag' anyway, it seemed redundant to say "then 'flag'" instead of just having the "ok" flag...oops - hope I haven't sounded more confused than I feel...
**ASCII stupid question, get a stupid ANSI !!!** -
You're right... I just dropped that in during some testing .. .forgot to take it out before posting.
What I'm trying to do here is find rows in the table where the Concschool field (school a student is concurrently enrolled in), the "conishome" field (field showing whether or not the school the student is concurrently enrolled is his "home" school) and the schoolnum field (the school owning the row in the table) are configured incorrectly... per my case statement. I did it in the "CASE" manner so the records would show with an extra colum with the word "FLAG" in it, because that allows sorting by student to show records from other schools around the bad one, which helps to determine what the flagged row's configuration of the three fields in question should be...
All this effects school enrollment counts.. so pretty important...
-
Tom - some sample data with expected resultsets would help...
also, what is the criteria against which the "ConcStatus = 'flag'" is currently being set ?!
**ASCII stupid question, get a stupid ANSI !!!** -
>>You're right... I just dropped that in during some testing .. .forgot to take it out before posting.
I'm now thoroughly confused.
The original question was why you couldn't filter on this derived column, but now you're saying the filter was just dropped in by accident.
So what exactly is the problem then ?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply