text data type used in JOIN query 
Author Message
 text data type used in JOIN query

hi,

would you know how to link two tables using a JOIN command
wherein the fields to be linked has different data types?
VARCHAR & TEXT data types to be exact. pls use details
below.

GIVEN DATA TYPE:
table1.field1 = 'text' data type
table2.field1 = 'varchar' data type

QUERY:
SELECT a.field1, b.field1, b.field2 FROM
table1 a JOIN table2 b ON cast(a.field1 as varchar)=
b.field1

the query doesn't work for me. it's returning the wrong
resultset compared to looking at the actual value of each
table.

thanks!



Sat, 12 Nov 2005 08:33:08 GMT
 text data type used in JOIN query

the query that u gave works fine. any way give this a try

select *
from t1 a
join t2 b on cast(a.f1 as varchar(10)) = b.f2


Quote:
> hi,

> would you know how to link two tables using a JOIN command
> wherein the fields to be linked has different data types?
> VARCHAR & TEXT data types to be exact. pls use details
> below.

> GIVEN DATA TYPE:
> table1.field1 = 'text' data type
> table2.field1 = 'varchar' data type

> QUERY:
> SELECT a.field1, b.field1, b.field2 FROM
> table1 a JOIN table2 b ON cast(a.field1 as varchar)=
> b.field1

> the query doesn't work for me. it's returning the wrong
> resultset compared to looking at the actual value of each
> table.

> thanks!



Sat, 12 Nov 2005 09:14:20 GMT
 text data type used in JOIN query
hi Chandra,

we have the same query. it doesn't work for me still.
TEXT is a BLOB type. i think it can't just be converted using CAST or
CONVERT statements. do you have another workaround for this?

does anybody have another workaround?

thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 13 Nov 2005 07:09:33 GMT
 text data type used in JOIN query
Ray,

I'd recommend this:

  ON substring(a.field1,1,8000) = b.field1

Steve Kass
Drew University

Quote:

>hi,

>would you know how to link two tables using a JOIN command
>wherein the fields to be linked has different data types?
>VARCHAR & TEXT data types to be exact. pls use details
>below.

>GIVEN DATA TYPE:
>table1.field1 = 'text' data type
>table2.field1 = 'varchar' data type

>QUERY:
>SELECT a.field1, b.field1, b.field2 FROM
>table1 a JOIN table2 b ON cast(a.field1 as varchar)=
>b.field1

>the query doesn't work for me. it's returning the wrong
>resultset compared to looking at the actual value of each
>table.

>thanks!



Sun, 13 Nov 2005 07:59:21 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Data Transfer from Long data type to SQL Server Text data type column

2. Stored Procedures - Using data type text

3. Using text Data Type in a group By clause

4. Trigger error using TEXT data type columns

5. Using the Text Data Type with a Trigger

6. Using VB5 to read/write text data type

7. Problem in using insert...execute with text data type

8. Using data type text field as local variable

9. using TEXT data type

10. Using ADODB.Parameters and SQL Text Data type

11. Problem with TEXT data types when generating report using Informix


 
Powered by phpBB® Forum Software