Data Type Comparison - 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 2005 Forums

Transact-SQL (2005)

Data Type Comparison

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-04-18 : 13:52:22

I have 3 tables with following columns
Table A
ID int
Value varchar(max)
Example Data
1 3,620.00
2 11/1/2011 2:22:09 AM
3 4.14
4 B--
5 NULL
6
7 Mar 6 2010 12:00AM
8 56

Table B
ID int
Number float
Date datetime
Char varchar(max)
Flag varchar(1)
Example Data
ID Number Date Char Flag
1 3620
2 11/1/2011 2:22:09
3 4.14
4 B--
5 NULL
6
7 2010-03-06 00:00:00.000
8 56

Table C
Dt_Id int
ID int
Example Data
Dt_Id ID
1 1
2 2
1 3
3 4
2 5
3 6
2 7
1 8

Dt_Id 1 represents Numeric data type
2 represents DateTime data type
3 represents Char data type

Flow of process, 1) Data is first inserted into Table A,
2) Join table A with C on column ID to identify which column to update in Table B.If dt_Id =1 then insert into clmn Number, if dt_id=2 then clmn Date else clmn Char

My problem now is I would like to identify if the value already exist in either 1 of the 3 clmn [Number,Date,Char] for a specific ID.If exist update Flag to 'D' in table B.This is what I have done so far.

I have tried to just SELECT those values that already exist, but it gives me an error.If execute the below by commenting a single where condition at a time value is returned correctly, but not able to execute it a whole.Here is what I get

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
Results
ID Dt_ID
1 1

SELECT a.ID,c.Dt_ID
FROM dbo.A a
INNER JOIN dbo.B b
ON a.ID=b.ID
INNER JOIN dbo.C c
ON b.ID=c.ID AND c.ID=a.ID
WHERE
(c.Dt_ID=1 AND ISNULL(CONVERT(FLOAT,Replace(a.VALUE, ',', '')),0)=ISNULL(b.Number,0)) OR
(c.Dt_ID=2 AND ISNULL(CONVERT(DATETIME,a.VALUE),GETDATE())=ISNULL(b.[Date],GETDATE())) OR
(c.Dt_ID=3 AND ISNULL(CONVERT(VARCHAR(MAX),a.VALUE),0)=ISNULL(b.[Char],0))

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-04-18 : 14:59:50

Anybody has any Ideas? Im stuck on this and cant proceed.Plz Advice

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-04-18 : 15:31:21

Here is the tsql code for the schema and sample data.

CREATE TABLE [dbo].[A](
[ID] [int] NULL,
[VALUE] [varchar](max) NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[B](
[ID] [int] NULL,
[Number] [float] NULL,
[Date] [datetime] NULL,
[Char] [varchar](max) NULL,
[Flag] [varchar](1) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[C](
[Dt_Id] [int] NULL,
[Id] [int] NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[A] (ID,VALUE)
SELECT 1,'3,260.00'
UNION ALL
SELECT 2,'11/1/2011 2:22:09 AM'
UNION ALL
SELECT 3,'4.14'
UNION ALL
SELECT 4,'B--'
UNION ALL
SELECT 5,NULL
UNION ALL
SELECT 6,''
UNION ALL
SELECT 7,'Mar 6 2010 12:00AM '
UNION ALL
SELECT 8,'56'
GO

INSERT INTO [dbo].[B] (ID,Number,[Date],[Char],Flag)
SELECT 1,3260,NULL,NULL,NULL
UNION ALL
SELECT 2,NULL,'2011-11-01 02:22:09.000',NULL,NULL
UNION ALL
SELECT 3,4.14,NULL,NULL,NULL
UNION ALL
SELECT 4,NULL,NULL,'B--',NULL
UNION ALL
SELECT 5,NULL,NULL,NULL,NULL
UNION ALL
SELECT 6,NULL,NULL,NULL,NULL
UNION ALL
SELECT 7,NULL,'2010-03-06 00:00:00.000',NULL,NULL
UNION ALL
SELECT 8,56,NULL,NULL,NULL
GO

INSERT INTO [dbo].[C] (Dt_Id,ID)
SELECT 1,1
UNION ALL
SELECT 2,2
UNION ALL
SELECT 1,3
UNION ALL
SELECT 3,4
UNION ALL
SELECT 2,5
UNION ALL
SELECT 3,6
UNION ALL
SELECT 2,7
UNION ALL
SELECT 1,8
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources