April 18, 2011 at 11:54 am
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))
April 18, 2011 at 1:00 pm
Anybody has any Ideas? Im stuck on this and cant proceed.Plz Advice
April 18, 2011 at 1:10 pm
As one who is an "Old Hand", you should be well aware that to get tested help from those volunteers that want to help you, you should post your table definition(s), sample data in an easily consumable format.
To do so, follow the "how to" and sample T-SQL code in the article which you can view by clicking on the first link in my signature block.
April 18, 2011 at 1:30 pm
Hi BitBucket, Totally agree its not easy for one to help if one does not have the schema and sample. I have attached it.
Kindly advice.Thank You
April 18, 2011 at 1:54 pm
NOt quite what I asked for. Remember many people do NOT like to open an attachment, not knowing what "worm" or other malicious code could be hidden in it.
now here is what I expected to see:
CREATE TABLE [dbo].[A]([ID] [int] NULL,[VALUE] [varchar](max) NULL )
ON [PRIMARY]
SELECT * FROM Dbo.A
GO
CREATE TABLE [dbo].([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]. (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
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))
April 18, 2011 at 2:03 pm
Thats really wierd. Coz Im getting the following error message
Results Tab
IDDt_ID
11
Message
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
Ive even dropped all 3 tables and recreated, populated with data.Still the same
April 18, 2011 at 2:23 pm
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))
Unless your data is incredibly specific this will never work. You are only going to allow values in this field that can be varchar(max), Float AND DateTime. Do you really have 3 datatypes in a single varchar(max) column? If so, you are going to have to separate that first. It looks like you have mixed datatypes in every column of all 3 tables.
There are a lot of challenges with the data structures as you presented them. If at all possible you should consider renaming your columns. Reserved words (Value, Number, Date, Char etc) should not be used as column names. It is also a good idea to name your columns something that gives an indication of what it is. 'Char' and 'Flag' do not really give any clue what they are.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2011 at 7:06 am
Our data is incredibely specific to only those data types. I have taken care of the insertion to Table B based on DT_ID column from Table C in another part of this huge sp and that works fine. My problem is to identify if a duplicate value already exist for a specified ID in Table B, if yes mark Flag clm = 'D'
I dont have much flexibility to change the table structure as they are tied to front end application and back end reporting structure.
Im aware of not using reserved words as column names, but I was hoping to make it easier for others to understand. The script that I have included is not the original table name and clmns, I have created those to mimic the actual schema.
The SELECT script works fine if I were to separate all 3 where clauses into different SELECT statment, when I combine them together and use an 'OR' to join them. It errors out on me.
Any advise how can I get the SELECT to work, or if there are any other ways to get this done
April 19, 2011 at 8:18 am
Best guess is you will have to do 3 queries and union them.
something like this
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))
union all
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=2 AND ISNULL(CONVERT(DATETIME,a.VALUE),GETDATE())=ISNULL(b.[Date],GETDATE()))
union all
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=3 AND ISNULL(CONVERT(VARCHAR(MAX),a.VALUE),0)=ISNULL(b.[Char],0))
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2011 at 8:37 am
Thanks Sean, thats what I have started doing.Wanted to make sure that I did not miss on any other alternatives on doing this.
April 19, 2011 at 8:46 am
Happy to help. I don't think you any other alternative because that column contains multiple datatypes. You could also try using each of those queries as an insert to a temp table and then select all from the temp table. Just have to compare execution plans and timing to figure out which is faster for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 20, 2011 at 7:56 am
There is an alternative - 2 of them actually. The first is to not do this kind of multi-datatype-column design. 😎
The other is to use my favorite 4 letter TSQL word: CASE. The issue here is that the query optimizer can do just about ANYTHING IT WANTS to your query as long as it algebraically-speaking gives you the same output (which may NOT be the actual output you desire, btw). So in this case it evaluates a data conversion before you actually want it to, and no amount of refactoring or parentheses will prevent this. But the CASE used in a where clause will short circuit and get you the desired outcome in a single pass over the table:
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 CASE WHEN c.Dt_ID = 1 THEN ISNULL(CONVERT(FLOAT,Replace(a.VALUE, ',', '')),0)
ELSE NULL
END = ISNULL(b.Number,0)
OR CASE WHEN c.Dt_ID = 2 THEN ISNULL(CONVERT(DATETIME,a.VALUE),GETDATE())
ELSE NULL
END = ISNULL(b.[Date],GETDATE())
OR CASE WHEN c.Dt_ID = 3 THEN ISNULL(CONVERT(VARCHAR(MAX),a.VALUE),0)
ELSE NULL
END = ISNULL(b.[Char],0)
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 20, 2011 at 8:00 am
Hi Kevin, Im going to give you a code a try .Thanks
April 20, 2011 at 2:57 pm
Kevin, thank you so much. It works great. I've learnt something new today thanks to you.
April 20, 2011 at 7:48 pm
LOOKUP_BI-756009 (4/20/2011)
Kevin, thank you so much. It works great. I've learnt something new today thanks to you.
Glad to help!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply