June 28, 2016 at 3:54 am
Hi Experts,
I am getting error in running this query, It has to do with data type conflict. But I am not quite sure about the problem.
Kindly shed a light on it.
Thanks!
Select * from
(SELECT Dept,IName, ICode, Qty, GrossWeight
FROM Data_Details
UNION
SELECT Dept, ICode,IName, SUM(Qty) AS TotalQ, SUM(GrossWeight) AS GrossWt from Data_Details
group by ICode,IName,Dept) source
Order by ICode,
CASE WHEN Quantity = 'TotalQ'
then 1 else 0 END, Qty
error: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'TotalQ' to data type int.
June 28, 2016 at 4:01 am
I don't see where the "Quantity" column comes from. Is that the actual query you're running or a simplified version?
However, the message indicates that you're comparing the column "Quantity" (which is likely a numeric column) to the string literal 'TotalQ'. For data type precedence, the string literal is converted to numeric to perform the comparison and the conversion fails.
-- Gianluca Sartori
June 28, 2016 at 4:04 am
Good spot Twin.Devil
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 28, 2016 at 4:10 am
BWFC (6/28/2016)
To do this:
CASE WHEN Quantity = 'TotalQ'You need to take the quotes off 'TotalQ', like this:
CASE WHEN Quantity = TotalQ
This will not work as in when UNION is used, There is no TotalQ column left. and Quantity Column where is this is coming from ????
or may be its typeo "Qty" column is refered as "Quantity"????
Can you please Shed some light on it 1st.
It would alot easy if you could also share your tables structure & some sample data along with it and for this case Original version of query as well.
June 28, 2016 at 4:12 am
spaghettidba (6/28/2016)
I don't see where the "Quantity" column comes from. Is that the actual query you're running or a simplified version?However, the message indicates that you're comparing the column "Quantity" (which is likely a numeric column) to the string literal 'TotalQ'. For data type precedence, the string literal is converted to numeric to perform the comparison and the conversion fails.
+1
and the Icode and Iname seem to have different order in the two queries, if it is not intentional.
June 28, 2016 at 4:14 am
Sorry Quantity is the correct column name, misspelled.:-D
Select * from
(SELECT Dept,IName, ICode, Quantity, GrossWeight
FROM Data_Details
UNION
SELECT Zone, ICode,IName, SUM(Quantity) AS TotalQ, SUM(GrossWeight) AS GrossWt from Data_Details
group by ICode,IName,Dept) source
Order by ICode,
CASE WHEN Quantity = TotalQ
then 1 else 0 END, Quantity
Error now is: Invalid column name 'TotalQ'. After I removed ' '
June 28, 2016 at 4:22 am
June 28, 2016 at 4:37 am
The query is more confusing now. Are you trying to compare columns between the two queries in the union? This in not possible.
June 28, 2016 at 4:42 am
maybe.....??
USE [tempdb]
GO
CREATE TABLE [dbo].[Data_Details](
[ICode] [int] NULL,
[Quantity] [int] NULL
)
GO
INSERT [dbo].[Data_Details] ([ICode], [Quantity]) VALUES (1, 23)
INSERT [dbo].[Data_Details] ([ICode], [Quantity]) VALUES (1, -15)
INSERT [dbo].[Data_Details] ([ICode], [Quantity]) VALUES (1, 17)
INSERT [dbo].[Data_Details] ([ICode], [Quantity]) VALUES (2, 52)
INSERT [dbo].[Data_Details] ([ICode], [Quantity]) VALUES (2, -68)
INSERT [dbo].[Data_Details] ([ICode], [Quantity]) VALUES (2, 100)
SELECT x.ICode,x.info, x.Quantity FROM (
SELECT ICode, Quantity, 1 as replevel, 'detail' as info
FROM Data_Details
UNION
SELECT ICode, SUM(Quantity) AS TotalQty, 2 , 'TotalQty'
FROM Data_Details
GROUP BY ICode) x
ORDER BY x.Icode,x.replevel
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 28, 2016 at 4:43 am
The inner query has errors. Work on it in isolation from the whole query.
SELECT Dept, IName, ICode, Quantity, GrossWeight
FROM Data_Details
UNION
SELECT Zone, ICode, IName, SUM(Quantity) AS TotalQ, SUM(GrossWeight) AS GrossWt
from Data_Details
group by ICode,IName,Dept
should probably be
SELECT Dept, IName, ICode, Quantity, GrossWeight
FROM Data_Details
UNION ALL
SELECT Dept, IName, ICode, SUM(Quantity) AS TotalQ, SUM(GrossWeight) AS GrossWt
from Data_Details
group by Dept, IName, ICode
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 28, 2016 at 5:57 am
After changing the order of group by in inner query ,now it is running ok. But it is still showing error: Conversion failed when converting the varchar value 'TotalQ' to data type int. What could be the problem ? :crazy:
June 28, 2016 at 6:23 am
Newbi (6/28/2016)
After changing the order of group by in inner query ,now it is running ok. But it is still showing error: Conversion failed when converting the varchar value 'TotalQ' to data type int. What could be the problem ? :crazy:
Check the column order in the two unioned queries.
The first query is Dept, IName, ICode
the second query is Zone, ICode, IName.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 28, 2016 at 6:25 am
Newbi (6/28/2016)
After changing the order of group by in inner query ,now it is running ok. But it is still showing error: Conversion failed when converting the varchar value 'TotalQ' to data type int. What could be the problem ? :crazy:
Also, if your query errors, post the query not just the error - so folks don't have to guess what caused the error.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 28, 2016 at 6:36 am
Sorry for all the confusion.:crying:
This is the final query which I make consistency in naming the columns.
Select * from
(SELECT Zone,ItemName, ItemCode, Quantity, GrossWeight
FROM PickLists_Details
UNION
SELECT Zone, ItemCode,ItemName, SUM(Quantity) AS TotalQ, SUM(GrossWeight) AS GrossWt
from PickLists_Details
group by Zone,ItemName,ItemCode) source
Order by ItemCode,
CASE WHEN Quantity = TotalQ
then 1 else 0 END, Quantity
June 28, 2016 at 6:43 am
Newbi (6/28/2016)
Sorry for all the confusion.:crying:This is the final query which I make consistency in naming the columns.
Select * from
(SELECT Zone,ItemName, ItemCode, Quantity, GrossWeight
FROM PickLists_Details
UNION
SELECT Zone, ItemCode,ItemName, SUM(Quantity) AS TotalQ, SUM(GrossWeight) AS GrossWt
from PickLists_Details
group by Zone,ItemName,ItemCode) source
Order by ItemCode,
CASE WHEN Quantity = TotalQ
then 1 else 0 END, Quantity
TotalQ does not exist as a column - SUM(Quantity) goes into the Quantity column.
Do you know the difference between UNION and UNION ALL? If not, look it up.
The first three columns of the two queries are not in the same order. Are they meant to be?
I suspect you are completely lost with UNION, it may not even be the best approach for what you are trying to do. If you can provide an explanation then someone will help.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 48 total)
You must be logged in to reply to this topic. Login to reply