Odd SQL query isnt working
-
Here's an example of my SQL, simplified as much as i can:
--this version works just fine:
SELECT (
SELECT * FROM (
SELECT ('a') as aa
) as list2
WHERE (SELECT 'a'+list.a)='aa'
) FROM (
SELECT 'a' as a
) as list
--works, returns 'a'
--this one dies when i try to reference list.a in the from clause of the innermost sub-select statement
SELECT (
SELECT * FROM (
SELECT ('a'+list.a) as aa --errors trying to reference list.a
) as list2
WHERE (SELECT 'a'+list.a)='aa'
) FROM (
SELECT 'a' as a
) as list
--says that list.a is an invalid column
so I can reference list.a in the 'where' clause of the innermost subselect, but not the 'from' clause...is there a way to make the second select statement work?
thanks in advance for any help
-
That's definatly odd SQL.
What is it that you're trying to do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
You might have simplified it too much.
The way things look now is that you were going through hoops to make a NullIfNot function.
For all I know, this will give you the results you want:
select case when list.a is null then null when ('a'+list.a)='aa' then 'aa' else null end FROM (SELECT 'a' union all SELECT 'b' union all SELECT 'c' union all SELECT NULL) as list (a) -
It's all in how it's being used:
1st script:
This is a good statement, list.a exists in the statement.
(SELECT 'a'+list.a)='aa')
FROM (SELECT 'a' as a) as list
2nd Script:
This is a bad statement. You are missing a FROM. Where does it get list.a from?
SELECT ('a'+list.a) as aa) as list2 --no FROM
WHERE (SELECT 'a'+list.a)='aa')
FROM (SELECT 'a' as a) as list --this FROM belongs to the subSELECT in the WHERE clause.
-SQLBill
-
its actually grabbing information about how far an employee has gotten in their training form both calculated records (based on employee's past shifts) and the information users enter manually...lots of levels of complexity, but basically what it all boils down to is, yes, i actually do need something like this to work and, no, it isnt trying to do a nullIfNot function
I see what you're saying, Bill, about the select statement not having a FROM clause...what i want is for it to be able to reference the fields in 'list' (here, list.a) in the FROM clause of the innermost subquery of the statement where 'list' is created as the FROM clause of the outermost query. How do i do this? saying
SELECT ('a'+list.a) as aa FROM list
doesn't work (invalid object "list")
-
Maybe what you need is a derived table subquery in your main FROM clause to define the common expression "'a' + list.a", so you can use the field aa in the SELECT field list, the WHERE clause, or anywhere else.
SELECT
a, aa
FROM (
SELECT a, 'a' + a AS aa
FROM (
SELECT 'a' AS a
) list
) list2
WHERE aa = 'aa'
-
Or since the main From clause (which is a subquery) will always be evaluated first you can insert the results into a temporary table and use that. Don't bother asking me why this makes more sense but it tests without error...
declare @dummy table([a] varchar) insert into @dummy select 'a' SELECT ( SELECT * FROM ( SELECT ('a'+list.a) as aa ) as list2 WHERE (SELECT 'a'+list.a)='aa' ) FROM @dummy as listchange it to
) FROM (select a from @dummy) as list
and you're right back where you started.
change it to
) FROM (select a from @dummy) as list(a)
and you get a totally different error. Invalid operator for data type. Operator equals add, type equals varchar.
then change the other line to
SELECT ('a'+convert(varchar,list.a)) as aaand you get a helpful Internal SQL Server error.
go figure.
-
If I understand correctly, you need to do something like this:
SELECT 'a' + list.b
FROM (SELECT 'b'
FROM tablename) list
Notice I changed the list.a to list.b JUST to show the different data and where it's coming from.
The problem is that you eventually HAVE to have a valid table name. UNLESS, you are getting your list from somewhere else - like an inputted list. If that's the case, then you need to use variables. But that would then require a different type of statement.
-SQLBill
-
A temp table would probably work, but unfortunately due to restrictions, i can't make one. I'm also working in a single query, not a stored procedure, so i can't use variables either. in the real version of this, i am referencing real tables to get data, but simplified to selecting constants for the example. This way, code can be ran in query analyzer to see exactly what the problem is without needing tables.
On further testing, i found some more interesting things.
if i run the original query:
SELECT (
SELECT * FROM (
SELECT ('a'+list.a) as aa
) as list2
) FROM (
SELECT 'a' as a
) as list
the server spits back "Invalid column name 'a'."
if i change my reference from list.a to foo.a:
SELECT (
SELECT * FROM (
SELECT ('a'+foo.a) as aa --changed here...i know foo doesn't exist
) as list2
) FROM (
SELECT 'a' as a
) as list
the server gives me a different error saying "The column prefix 'foo' does not match with a table name or alias name used in the query."
changing the order the lists are accessed in:
SELECT (
SELECT * FROM (
SELECT ('a') as aa
) as list2
) FROM (
SELECT 'a'+list2.aa as a
) as list
the server tells me "The column prefix 'list2' does not match with a table name or alias name used in the query."
because the first two errors are different, the server has to see "list" and know that it exists in the first query, it just doesn't know that list has anything in it. I think the problem is the order in which things are parsed by the server...if the original erroring query was parsed like this: (which makes sense based on the errors the server gives with the above queries)
SELECT (
SELECT * FROM (
SELECT ('a'+list.a) as aa --PARSED THIRD
) as list2 --PARSED SECOND
) FROM (
SELECT 'a' as a --PARSED FOURTH
) as list --PARSED FIRST
the server would first creates alias "list" without populating, then creates "list2" without populating. (creates namespaces from the outside in)
it then tries populate "list2" by executing "SELECT ('a'+list.a) as aa" but "list" has not been populated yet, so it errors trying to find list.a. (populate FROM clauses from inside out)
This would explain the error i was getting...
the hard part now is trying to figure out how to rewrite my query so it gets along with the order the server wants to parse things...unless of corse there's a way to force the server to populate the outermost FROM clause before the innermost?
-
Can you perhaps post some sample data and desired output?
Maybe there's another way of doing this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
The rules for the scope of alias definitions in SQL queries are not that complicated, even if you can rearrange the query to produce a variety of error messages. All names used in the field list of a SELECT statement must be defined in it's FROM clause. It is as simple as that, and every one of your examples violates that rule.
You can use subqueries in the field list of a query, but they define only a single value for each row and any aliases defined in the subquery are not visible in any other part of the query. The alias used to name the result can be used, but only in the ORDER BY clause.
A subquery in the FROM clause is called a derived table subquery. You can use expressions to create new columns there, and they can then be used in the field list, WHERE clause, joins, and ORDER BY. I think that is what you need to try.
-
screw it. writing this into a single query is taking too much time, so i'm giving up and writing this into a stored procedure that'll create a temp table. thanks all for your help.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply