Error converting data type varchar to bigint.
-
Hello Community,
I'm getting the following SQL error in Azure Synapse
Error converting data type varchar to bigint.
;WITH CTE1 AS
(
SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dbo].[account]
),CTE2 AS
(
SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dbo].[OptionsetMetadata]
)
SELECT C1.Id,C1.SinkCreatedOn,C1.SinkModifiedOn,C1.statecode,C1.statuscode
,CASE WHEN C1.ts_primarysecondaryfocus<>ISNULL(C2.ts_primarysecondaryfocus,'')THEN C2.ts_primarysecondaryfocus ELSE C1.ts_primarysecondaryfocus END AS ts_primarysecondaryfocus
,C1.customertypecode,C1.address1_addresstypecode,C1.accountclassificationcode,C1.ts_easeofworking
,CASE WHEN C1.ts_ukrow<>ISNULL(C2.ts_ukrow,'')THEN C2.ts_ukrow ELSE C1.ts_ukrow END AS ts_ukrow
,C1.preferredappointmenttimecode,C1.xpd_relationshipstatus,C1.ts_relationship
FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.RowNum = C2.RowNumThe problem is, I don't know why Azure Synapse is trying to convert the Data Type to BIGINT.
Also, I don't know how to change the field to BIGINT in Azure Synapse.
I have posted this here because Azure Synapse SQL language is T-SQL
Any thoughts?
-
I should mention the table/view OptionsetMetadata was created as follows:
CREATE VIEW OptionsetMetadata
AS
with cte as (
select OptionsetName,
LocalizedLabel,
row_number()over(partition by optionsetname order by cast([option] as int)) as rn
from dataverse_montagu_org5a2bcccf.dbo.OptionsetMetadata
)
select
max(case when OptionSetName = 'participationtypemask' then LocalizedLabel end) as participationtypemask,
max(case when OptionSetName = 'instancetypecode' then LocalizedLabel end) as instancetypecode,
max(case when OptionSetName = 'donotpostalmail' then LocalizedLabel end) as donotpostalmail,
max(case when OptionSetName = 'donotfax' then LocalizedLabel end) as donotfax,
max(case when OptionSetName = 'donotphone' then LocalizedLabel end) as donotphone,
max(case when OptionSetName = 'ispartydeleted' then LocalizedLabel end) as ispartydeleted,
max(case when OptionSetName = 'donotemail' then LocalizedLabel end) as donotemail,
max(case when OptionSetName = 'ts_primarysecondaryfocus' then LocalizedLabel end) as ts_primarysecondaryfocus,
max(case when OptionSetName = 'customertypecode' then LocalizedLabel end) as customertypecode,
max(case when OptionSetName = 'address1_addresstypecode' then LocalizedLabel end) as address1_addresstypecode,
max(case when OptionSetName = 'accountclassificationcode' then LocalizedLabel end) as accountclassificationcode,
max(case when OptionSetName = 'ts_ukrow' then LocalizedLabel end) as ts_ukrow,
max(case when OptionSetName = 'preferredappointmenttimecode' then LocalizedLabel end) as preferredappointmenttimecode,
max(case when OptionSetName = 'address2_freighttermscode' then LocalizedLabel end) as address2_freighttermscode,
max(case when OptionSetName = 'ts_introducertype' then LocalizedLabel end) as ts_introducertype,
max(case when OptionSetName = 'accountcategorycode' then LocalizedLabel end) as accountcategorycode,
max(case when OptionSetName = 'paymenttermscode' then LocalizedLabel end) as paymenttermscode,
max(case when OptionSetName = 'preferredappointmentdaycode' then LocalizedLabel end) as preferredappointmentdaycode,
max(case when OptionSetName = 'businesstypecode' then LocalizedLabel end) as businesstypecode,
max(case when OptionSetName = 'industrycode' then LocalizedLabel end) as industrycode
from cte
group by rnPlease let me know if you need sample data
-
Yes, post DDL & sample data.
What data types are account.ts_ukrow & OptionsetMetadata.ts_ukrow? What data types are account.ts_primarysecondaryfocus & OptionsetMetadata.ts_primarysecondaryfocus ?
The CASE statements wrapping ISNULL() using empty string for those jumps out at me.
,CASE WHEN C1.ts_primarysecondaryfocus<>ISNULL(C2.ts_primarysecondaryfocus,'') THEN C2.ts_primarysecondaryfocus
ELSE C1.ts_primarysecondaryfocus
END AS ts_primarysecondaryfocus
,CASE WHEN C1.ts_ukrow <> ISNULL(C2.ts_ukrow,'') THEN C2.ts_ukrow
ELSE C1.ts_ukrow
END AS ts_ukrowMight be a problem if not varchar in both tables.
-
Here you go.
BTW, I think the problem is with NULLs
CREATE TABLE OptionsetMetadata (
participationtypemask varchar(50),
instancetypecode varchar(50),
donotpostalmail varchar(50),
donotfax varchar(50),
donotphone varchar(50),
ispartydeleted varchar(50),
donotemail varchar(50),
ts_primarysecondaryfocus varchar(50),
customertypecode varchar(50),
address1_addresstypecode varchar(50),
accountclassificationcode varchar(50),
ts_ukrow varchar(50),
preferredappointmenttimecode varchar(50),
address2_freighttermscode varchar(50),
ts_introducertype varchar(50),
accountcategorycode varchar(50),
paymenttermscode varchar(50),
preferredappointmentdaycode varchar(50),
businesstypecode varchar(50),
industrycode varchar(100))
INSERT OptionsetMetadata VALUES
('Sender','Not Recurring','Allow','Allow','Allow','No','Allow','Tier 1','Competitor','Bill To','Default Value','UK','Morning','Default Value','Primary','Preferred Customer','Net 30','Sunday','Default Value','Accounting'),
('To Recipient','Recurring Master','Do Not Allow','Do Not Allow','Do Not Allow','Yes','Do Not Allow','Tier 2','Consultant','Ship To',NULL,'RoW','Afternoon',NULL,'Boutique','Standard','2% 10, Net 30','Monday',NULL,'Agriculture and Non-petrol Natural Resource Extraction'),
('CC Recipient','Recurring Instance',NULL,NULL,NULL,NULL,NULL,'TBC','Customer','Primary',NULL,NULL,'Evening',NULL,'T2 Generalists',NULL,'Net 45','Tuesday',NULL,'Broadcasting Printing and Publishing'),
('BCC Recipient','Recurring Exception',NULL,NULL,NULL,NULL,NULL,NULL,'Investor','Other',NULL,NULL,NULL,NULL,NULL,NULL,'Net 60','Wednesday',NULL,'Brokers'),
('Required attendee','Recurring Future Exception',NULL,NULL,NULL,NULL,NULL,NULL,'Partner',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Thursday',NULL,'Building Supply Retail'),
('Optional attendee',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Influencer',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Friday',NULL,'Business Services'),
('Organizer',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Press',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Saturday',NULL,'Consulting'),
('Regarding',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Prospect',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Consumer Services'),
('Owner',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Reseller',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Design, Direction and Creative Management'),
('Resource',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Supplier',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Distributors, Dispatchers and Processors'),
('Customer',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Vendor',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Doctor''s Offices and Clinics'),
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Other',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Durable Manufacturing'),
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Eating and Drinking Places')
SELECT * FROM OptionsetMetadataAnd Sample Data for account table
CREATE TABLE account (
Id varchar(50),
SinkCreatedOn datetime2,
SinkModifiedOn datetime2,
statecode int,
statuscode int,
ts_primarysecondaryfocus varchar(255),
customertypecode varchar(255),
address1_addresstypecode varchar(255),
accountclassificationcode int,
ts_easeofworking varchar(255),
ts_ukrow varchar(255),
preferredappointmenttimecode varchar(255),
ts_address1addresstype int,
xpd_relationshipstatus int,
ts_relationship varchar(255),
xpd_remindermonths int,
ts_importance varchar(255),
address2_freighttermscode int,
ts_irrvsmomperformancemeasure varchar(255),
ts_introducertype varchar(255),
accountcategorycode varchar(255),
paymenttermscode varchar(255),
preferredappointmentdaycode varchar(255),
businesstypecode int,
industrycode varchar(255),
ts_sizeoffirm varchar(255),
ts_interaction varchar(255),
address1_shippingmethodcode varchar(255),
ts_portfoliocompanystatus varchar(255),
customersizecode int,
xpd_previousinvestorstatus varchar(255),
ts_recommendationoffirm varchar(255),
preferredcontactmethodcode int,
accountratingcode int,
ts_investorstatus int)
INSERT account VALUES
('0bf6d52f-707f-e811-a95c-00224800cc97',CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),0,1,'','','',1,'','','',717750001,NULL,'',NULL,'',1,'','','','','',1,'','','','','',1,'','',1,1,717750001),
('0df6d52f-707f-e811-a95c-00224800cc97',CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),0,1,'','','',1,'','','',717750001,NULL,'',NULL,'',1,'','','','','',1,'','','','','',1,'','',1,1,717750001),
('0ff6d52f-707f-e811-a95c-00224800cc97',CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),0,1,'','','',1,'','','',717750001,NULL,'',NULL,'',1,'','','','','',1,'','','','','',1,'','',1,1,717750001),
('11f6d52f-707f-e811-a95c-00224800cc97',CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:25:52.0000000', 121),0,1,'','','',1,'','','',717750001,NULL,'',NULL,'',1,'','','','','',1,'','','','','',1,'','',1,1,717750001),
('c4a24708-0989-e811-a95d-00224800cc97',CONVERT(DATETIME2, '2022-05-19 16:22:37.0000000', 121),CONVERT(DATETIME2, '2022-05-19 16:22:37.0000000', 121),0,1,'','','',1,'','','',717750001,930580000,'',NULL,'',1,'','','','','',1,'','','','','',1,'','',1,1,NULL)
SELECT * FROM account -
Something very strange is going on....
When I execute the code
;WITH CTE1 AS
(
SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dbo].[account]
),CTE2 AS
(
SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dbo].[OptionsetMetadata]
)
SELECT C1.Id,C1.SinkCreatedOn,C1.SinkModifiedOn,C1.statecode,C1.statuscode
,CASE WHEN CAST(C1.ts_primarysecondaryfocus AS VARCHAR(100))<>ISNULL(CAST(C2.ts_primarysecondaryfocus as VARCHAR(100)),'')THEN C2.ts_primarysecondaryfocus ELSE C1.ts_primarysecondaryfocus END AS ts_primarysecondaryfocus
,C1.customertypecode,C1.address1_addresstypecode,C1.accountclassificationcode,C1.ts_easeofworking
,CASE WHEN CAST(C1.ts_ukrow AS VARCHAR(100)) <>ISNULL(C2.ts_ukrow,'')THEN C2.ts_ukrow ELSE C1.ts_ukrow END AS ts_ukrow
,C1.preferredappointmenttimecode,C1.xpd_relationshipstatus,C1.ts_relationship
FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.RowNum = C2.RowNumOn Azure Synapse SQL DW I get NULLs, where I should be getting data i.e the value TBC, see image
But on my personal SQL Server I get the value i.e TBC - using the exact same code.
How strange
-
This is soooooo strange
-
I should point out that account and OptionsetMetadata are both views on Azure Synapse, but I'm not sure if that would make a difference??
-
Any further thoughts?
-
I don't know... I don't work with anything having to do with Azure.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Is it possible that the optimiser is confused by the absence of else in the case statements in the OptionsetMetadata view? Possibly it thinks there will be a null returned, even if the data make it impossible, and it thinks one or more of those nulls is a bigint. I know this is a long shot, but does changing the case statements like this help?
max(case when OptionSetName = 'participationtypemask' then LocalizedLabel else cast null as varchar(100)) end) as participationtypemask,
-
I'll try anything at this stage ... going to try it now.
-
Getting the following error:
Incorrect syntax near the keyword 'null'.
-
A simple typo I think. If you have just used copy/paste from Ed B's suggested code, the CAST needs a starting paranteses, i.e. CAST(NULL AS VARCHAR(100))
-
Still getting NULLs.
I really haven't got a clue at this stage
Thanks for trying
-
deleted
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply