microsoft.public.sqlserver.programming

Hugo Kornelis

3/25/2007 12:03:00 PM

On Fri, 23 Mar 2007 10:21:05 -0700, Ganesh wrote:

>Hi There,
>
>I've same records in both table, but it doesn't return any records
>
>TP_Relationship.PocID is userdefineddatatype int
>TP_Location.POCId is int not userdefineddatatype int
>
>could it be a proble if i compare userdefineddata type with datatype
>
>SELECT TP_Location.POCId, TP_Location.AddressId, TP_Relationship.POCId,
>TP_Relationship.*
>FROM TP_Location INNER JOIN
> TP_Relationship ON TP_Location.POCId =
>TP_Relationship.POCId

Hi Ganesh,

For comparisons, it doesn't matter if a column is defined as Int or as a
user-defined datatype defined on top of Int, as the repro below shows:

USE tempdb;
go
CREATE TYPE MyInt
FROM Int NOT NULL;
go
CREATE TABLE TestIt
(MyIntCol MyInt,
IntCol Int NOT NULL);
INSERT INTO TestIt (MyIntCol, IntCol) VALUES (1, 1);
INSERT INTO TestIt (MyIntCol, IntCol) VALUES (2, 3);
SELECT * FROM TestIt WHERE MyIntCol = IntCol;
SELECT * FROM TestIt WHERE MyIntCol < IntCol;
SELECT * FROM TestIt WHERE MyIntCol > IntCol;
go
DROP TABLE TestIt;
DROP TYPE MyInt;
go

If you don't get the results you expect, there has to be another
problem, but you didn't give us enough information to troubleshoot that
problem. It would hellp if you post the following:

1. The CREATE TYPE statements used for yoour user-defined types.

2. The structure of all relevant tables, posted as CREATE TABLE
statements. Make sure to include all constraints, indexes and
properties, as they might be relevant to the answer. If a table has lots
of columns that are not relevant to the question, you can omit them.

3. A few well-chosen rows of sample data to illustrate the problem. Try
to include special cases in the sample data, but try to keep it compact
as well (don't post hundreds of rows!!). Becuase nobody here likes
typing, make sure to post the sample data as INSERT statements, so that
we can use copy and paste to recreate the test data on our side.

4. The expected output from the sample data you posted. You can post
this in a tabular format. (For readability, use spaces rather than the
TAB character and make sure that the colums are properly aligned when
displayed in a non-proportiaonal font).

5. The exact query you used and the output or error messages you got
when running the query. To prevent typos, please use copy and paste for
the query, the results, and the error messages.

More information, and some hints on how to assemble the CREATE TABLE and
INSERT statements with minimal effort, are on www.aspfaq.com/5006.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...