String size causing error
-
I get the following error with the following code. This code is stripped from a larger piece of code to show the issue.
This only works if both the literals are exactly the same length. If you add one character to either of the strings, you get the error.
Msg 8167, Level 16, State 1, Line 11
The type of column "DEDType7" conflicts with the type of other columns specified in the UNPIVOT list.
SELECT Title
FROM
(
SELECT TOP 1
'abcbbbaaaaaa' AS DEDType6
,'abcbbbffffs' AS DEDType7
FROM [Benefits].[TestInvoice] Invoice
) AS up
UNPIVOT
(
Title FOR Titles IN (DEDType6, DEDType7)
) AS up2Why would the length of the string make a difference?
Thanks,
Tom
- tshad - Friday, April 13, 2018 1:43 AM
Can you please post the DDL (create table) script for the object Benefits.TestInvoice and if applicable, any source tables.
😎 -
When you pivot, the data you pivot into a column needs to all be the same datatype, as well as have the same length/scale/precision. You have a varchar(12) ('abcbbbaaaaaa') and a varchar(11) ('abcbbbffffs') there, hence a conflict.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
You don't need the actual table for the select as I am not actually getting any data from it. I just need to read a table for the unpivot. I don't actually care about the results - just the error on the strings.
I have the same problems with my fingers 🙂
Having the same varchar size was what I figured but couldn't figure why that would be necessary - a string is a string.
I figured I would have to do a convert or cast to do it since this data will not be coming a table and that solved it.
SELECT Title
FROM
(
SELECT TOP 1
CAST('abcbbbaaaaaa' AS VARCHAR(32)) AS DEDType6
,CAST('abcbbbffffs' AS VARCHAR(32)) AS DEDType7
FROM [Benefits].[TestInvoice] Invoice
) AS up
UNPIVOT
(
Title FOR Titles IN (DEDType6, DEDType7)
) AS up2Thanks a lot.
- tshad - Friday, April 13, 2018 9:43 AM
No need for a table if you are not using it, table reference has nothing to do with un-pivoting.
😎SELECT Title
FROM
(
SELECT TOP 1
CAST('abcbbbaaaaaa' AS VARCHAR(32)) AS DEDType6
,CAST('abcbbbffffs' AS VARCHAR(32)) AS DEDType7
) AS up
UNPIVOT
(
Title FOR Titles IN (DEDType6, DEDType7)
) AS up2;Just wondering why you are using UNPIVOT when you could simply use a CTE with UNION ALL which is much more efficient?
;WITH SAMPLE_DATA(Title) AS
(
SELECT 'abcbbbaaaaaa' UNION ALL
SELECT 'abcbbbffffs'
)
SELECT
SD.Title
FROM SAMPLE_DATA SD; -
Ignoring the issue I was having which now works, the reason I am unpivoting is because I am getting an upload every month from a spreadsheet that I am uploading to a couple of flat files. I am then moving them to a set of normalized files. Part of these files have a set of payments and deductions. The number of columns are different depending on who is sending the files. There could be 10 or 15 of these columns - with their own set of descriptions for the payments or deductions.
I created a table that is just the description of the payment/deduction and the amount.
So I unpivot the select statement to get a list of amounts and descriptions that I can insert into the table. The unpivot needed to be a two column unpivot (description and amount).
Now sure how the CTE would be more efficient. But would like to see how it would. Always glad to look at other ideas.
In essence what I did was something like this sample that I used for a template. Mine was a bit more complex but not by much.
DECLARE @CustomerPhones2 TABLE
(
CustomerID INT PRIMARY KEY, -- FK
FirstName VARCHAR(20),
Phone1 VARCHAR(32),
PhoneType1 CHAR(4),
Phone2 VARCHAR(32),
PhoneType2 CHAR(4),
Phone3 VARCHAR(32),
PhoneType3 CHAR(4)
);
INSERT @CustomerPhones2 VALUES
(1, 'Tom','705-491-1111', 'cell', '705-491-1110', 'home', NULL,NULL),
(2, 'Larry','613-492-2222', 'home', NULL, NULL, NULL, NULL),
(3, 'Mary','416-493-3333', 'work', '416-493-3330', 'cell',
'416-493-3339', 'home');SELECT * FROM @CustomerPhones2
SELECT CustomerID, FirstName, Phone, PhoneType
FROM
(
SELECT CustomerID, FirstName, Phone, PhoneType,
idp = SUBSTRING(Phones, LEN(Phones) - PATINDEX('%[^0-9]%', REVERSE(Phones)) + 2, 32),
idpt = SUBSTRING(PhoneTypes, LEN(PhoneTypes) - PATINDEX('%[^0-9]%', REVERSE(PhoneTypes)) + 2, 32)
FROM
(
SELECT CustomerID, FirstName, Phone1, Phone2, Phone3,
PhoneType1, PhoneType2, PhoneType3
FROM @CustomerPhones2
) AS cp
UNPIVOT
(
Phone FOR Phones IN (Phone1, Phone2, Phone3)
) AS p
UNPIVOT
(
PhoneType FOR PhoneTypes IN (PhoneType1, PhoneType2, PhoneType3)
) AS pt
) AS x
WHERE idp = idpt;Here is the result.
In my scenario, the Phone would be the amount and the PhoneType would be the description.I can take the select/unpivot query and put an insert in front of it and create the records in my table. In this case, 6 records would be created.
This sample had only 3 phone and phone types in it. Just to make it handle the most columns I could expect I have 13 of them.
Tom
-
The query can be simplified using cross apply, here is an example
😎USE TEEST;GOSET NOCOUNT ON;DECLARE @CustomerPhones2 TABLE(CustomerID INT PRIMARY KEY, -- FKFirstName VARCHAR(20),Phone1 VARCHAR(32),PhoneType1 CHAR(4),Phone2 VARCHAR(32),PhoneType2 CHAR(4),Phone3 VARCHAR(32),PhoneType3 CHAR(4));INSERT @CustomerPhones2 VALUES(1, 'Tom','705-491-1111', 'cell', '705-491-1110', 'home', NULL,NULL),(2, 'Larry','613-492-2222', 'home', NULL, NULL, NULL, NULL),(3, 'Mary','416-493-3333', 'work', '416-493-3330', 'cell','416-493-3339', 'home');SELECTCP.CustomerID,CP.FirstName,PT.PHONE,PT.PHONE_TYPEFROM @CustomerPhones2 CPCROSS APPLY(SELECT CP.Phone1,CP.PhoneType1 UNION ALLSELECT CP.Phone2,CP.PhoneType2 UNION ALLSELECT CP.Phone3,CP.PhoneType3) PT(PHONE,PHONE_TYPE)WHERE PT.PHONE IS NOT NULL; -
That looks great and does look simpler. It also looks easier for someone else to make changes to.
And I think it also handles the issue of the strings having to be the same length, correct?
I'll change my full query to this and see how it works.
Thanks
-
You are very welcome!
😎Not only is it simpler, it is much more efficient and faster than using the UNPIVOT command.
-
I just applied it to my real query and it does allow me to get rid of the casting for the string literals and the substring piece that tied the phone and phonetypes together.
Works great.
Thanks again.
-
Also, you said it was more efficient. How would we check that other than run it. In this case, both are fast so I can't really tell.
-
I guess yu can look at the execution plans.
If you look at just the last query for both samples, it does look like the new one does less work:
New Query:
Old query:
- tshad - Monday, April 16, 2018 10:00 AMI guess yu can look at the execution plans.
If you look at just the last query for both samples, it does look like the new one does less work:
New Query:
Old query:
You are right, the execution plan is a good indicator although not always telling the whole truth.
😎Execution efficiency can vary between versions and platforms, only by doing non-intrusive testing can one determine what works best on a specific platform.
There are several articles around which would be easy to find, suggest you have a look. -
I will.
Also, I never saw Cross Apply used this way. I can't find anywhere where it shows defining the columns outside of the cross apply in the alias. I see that done in CTE's but can't find where is is done for Cross Applys.
Thanks,
Tom
- tshad - Monday, April 16, 2018 1:42 PM
The column aliases are defined after the sub-query alias.
😎SELECT[COLUMN LIST]FROM[ALIAS]
CROSS APPLY([SELECT STATEMENT]) [ALIAS]([COLUMN ALIAS]);
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply