Querying a Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Querying a Query

Author

Topic

ronstone
Starting Member

32 Posts

Posted - 2004-12-07 : 09:26:04

Hear me out!

I have a table that has lets say, generic columns for most of the datatypes in sql.

pseudo-code:

create table UserData
(ListId int -- id for list
Nvarchar1 nvarchar,
Nvarchar2 nvarchar,
Nvarchar3 nvarchar ...
Bit1 bit,
Bit2 bit,
Bit3 bit, ...
Float1 float,
Float2 float,
Float3 ...
)

etc. It's used to stored data from lists. There can be multiple lists, that use each of the columns for whatever fields are in that particular list.

So, one list, may use the Nvarchar1 column for a phone number, while another list uses Nvarchar1 column for a year.

(This is the Sharepoint WSS Userdata table btw)

In one situation, I need to query for entires with the year, below 2006. I want to query: WHERE convert(int, nvarchar1) <= 2005 AND ListName = "MyList".

The behavior I am seeing, is that even though I specify the ListName, SQL Server still tries to pass other list data through the year conditional statement, thus throwing an exception.

So, if another list has a phone number (444)234-2343 in nvarchar1, obviously, you can't convert to int.

I inserted the rows into a temp table where the list items = MyList, and then queried the temp table for the date. Sure that works, but not elegant.

There has to be a way to do this in a single query statement.

Somehow I need to get the rows for all MyList listname, then query that.

SELECT Columns
FROM (
SELECT *
From UserData WHERE ListName = "MyList"
)

I'm thinking maybe a join... Can anybody whip a solution out for me?

Tks
Ron

ronstone
Starting Member

32 Posts

Posted - 2004-12-07 : 09:39:16

Stupid, I got it.

Select q2.*
FROM
(
select statement
where list = mylist
) as q2
where convert(int,q2.nvarchar1) < 2005

argh

Nope, when I add the where convert, I still get the syntax error converting the nvarchar to column of datatype int.

WHY?? My subquery returns only items that belong to the list, that only has years in the nvarchar column, but SQL server looks at all of the items...

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-07 : 17:11:22

1) Your subquery isn't even valid SQL (No FROM clause).
2) How do you know that the subquery only returns valid integers?

Can you supply the ACTUAL SQL statement that you are using?

HTH

=================================================================
Hear the sledges with the bells - Silver bells!
What a world of merriment their melody foretells!
How they tinkle, tinkle, tinkle,
In the icy air of night!
While the stars that oversprinkle
All the heavens, seem to twinkle
With a crystalline delight;
Keeping time, time, time,
In a sort of Runic rhyme,
To the tintinnabulation that so musically wells
From the bells, bells, bells, bells,
Bells, bells, bells
From the jingling and the tinkling of the bells.

Happy Holidays!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources