CONFUSED- PLS Help-Error when querying two tables

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

General SQL Server Forums

New to SQL Server Programming

CONFUSED- PLS Help-Error when querying two tables

Author

Topic

faith2011
Starting Member

9 Posts

Posted - 2015-02-19 : 10:44:42

I am trying to join two tables and keep getting an error message that states...

The data types text and text are incompatible in the equal to operator.

I need to know how to effectively query the two tables without re-importing the data. The import took hours to run.

Both fields have a data type of TEXT.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-19 : 10:50:26

Cast the data to the varchar(max) type when comparing

e.g.


select 42
where cast(cast('abc' as text) as varchar(max)) = cast(cast('abc' as text) as varchar(max))

faith2011
Starting Member

9 Posts

Posted - 2015-02-19 : 10:57:11

SELECT first4.*, second.*
FROM first4 INNER JOIN
first4 ON second.unid = first.unid

unid is the field and it is a text type in both tables..

I am getting the same error message when trying the cast query...

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-19 : 11:12:54

post the query that causes the error.

Does this work:

[code]
SELECT first4.*, second.*
FROM first4 INNER JOIN
first4 ON cast(second.unid as varchar(max)) = cast(first.unid as varchar(max))

faith2011
Starting Member

9 Posts

Posted - 2015-02-19 : 11:53:10

I have changed it several times and still getting an error message..

first4 in the FROM clause have the same exposed names. Use correlation names to distinguish them.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-19 : 12:04:37

Please post the exact query that you ran and the error message(s) you received.

faith2011
Starting Member

9 Posts

Posted - 2015-02-19 : 12:24:02

SELECT first4.*, second.*
FROM first4 INNER JOIN
first4 ON cast(second.unid as varchar(max)) = cast(first4.id as varchar(max)

The objects "first4" and "first4" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-19 : 12:31:04

That's not valid syntax. In particular the alias "second" is used in the SELECT but never defined. You're also missing a right parenthesis on the end.

faith2011
Starting Member

9 Posts

Posted - 2015-02-19 : 12:36:38

I copied and query and didn't grab the last parenthesis..(my fault)
Also, i went by the query that was posted earlier as an option. Unfortunately, it does not work. I am no longer getting the error message regarding the data types.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-19 : 12:57:43

You need something like this:


SELECT First.*, Second.*
FROM first4 as First
JOIN first4 as Second
  ON CAST(Second.unid as varchar(max)) = CAST(First.unid as varchar(max))

Note the alias "Second" in the JOIN clause. Also, *important!* in the last query you posted, you referred to "first4.id", *not* "first.unid" as in your earlier posting.

faith2011
Starting Member

9 Posts

Posted - 2015-02-19 : 13:15:39

I changed it from first.unid to first4.id because I changed the data. ...

SELECT First.*, Second.*
FROM first4 as First
JOIN first4 as Second
ON CAST(Second.unid as varchar(max)) = CAST(First.id as varchar(max))

Msg 207, Level 16, State 1, Line 4
Invalid column name 'unid'.

We are joining on the same table (first4)???

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-19 : 13:41:22

Because that's what's in the query you posted


SELECT first4.*, second.*
FROM first4 INNER JOIN
first4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources