2 Select Statement - 2 Data Types

[vpekulas]

Hi,

I believe that my SQL query is correct, the problem is that datatypes are different. How can I change them ? Or make the second SELECT statement an array ?


SELECT fldNAME, ID
FROM TABLE_CATE
WHERE ID
IN
(SELECT fldCATES
FROM TABLE_USER
WHERE ID = 2)


Thanks for your help !

http://www.Europeum.net

 

[vpekulas]

I forgot to mention the ID column is INT
and the fldCATES is a TEXT and has values
such as "1,5,6,8,2"

http://www.Europeum.net

 

[tlbroadbent]

Use the CAST function.

SELECT fldNAME, ID
FROM TABLE_CATE
WHERE ID
IN
(SELECT CAST(fldCATES As Int)
FROM TABLE_USER
WHERE ID = 2)
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[dnoeth]

This is not a datatype problem, it's your SQL:
You can't convert a string to a list of integers.
And you need to convert it, because your data model is a mess:
Instead of a row with a string containing a comma delimited list of values you need 1 row per value.

Dieter

 

[r937]

dieter is right, you should probably redesign your tables

a comma-separated list of values violates first normal form

google will find lots and lots of articles about "first normal form" or you could look at some of the articles listed here:

http://r937.com/links.cfm?links=sql

too bad sql does not support array or list functions, eh


rudy

 

[vpekulas]

That's exactly what I need to convert, string in form of "1,5,6" to an array of integers.
Is there an equivalent of Split() function in SQL ?

http://www.Europeum.net

 

[r937]

no

bite the bullet, redesign the table

 

[vpekulas]

That's not very nice ...

http://www.Europeum.net

 

[tlbroadbent]

Obviously, I didn't read your post carefully enough. My solution was far to simple.

While I agree that the table design isn't normalized, sometimes we are stuck with something we didn't design. If redesign is not practical, perhaps the following solution will work.

DECLARE @sql varchar(2000), @str varchar(80)
SET @str=''
SELECT @str=@str+fldCates
FROM TABLE_USER
WHERE ID = 2

SET @sql = 'SELECT fldNAME, ID FROM TABLE_CATE WHERE ID IN (' + @str + ')'
EXECUTE(@sql)

If this doesn't work for you, you should post your question in a forum for your RDMS. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[tlbroadbent]

I omitted a very important part of the script.

--Add a comma between strings
SELECT @str=@str+fldCates+','
FROM TABLE_USER
WHERE ID = 2

--Remove trailing comma
SELECT @str = substring(@str,1,len(@str)-1)) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.