Error converting data type varchar to bigint.

Question

Post reply

Error converting data type varchar to bigint.

carlton 84646

Ten Centuries

Points: 1026

July 8, 2022 at 4:18 pm

#4060724

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.RowNum

The 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?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 1

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 rn

Please let me know if you need sample data

ratbak Ten Centuries Points: 1358 More actions · Answer 2

Yes, post DDL & sample data.

How to post code problems

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_ukrow

Might be a problem if not varchar in both tables.

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 3

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 OptionsetMetadata

And 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

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 4

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.RowNum

On Azure Synapse SQL DW I get NULLs, where I should be getting data i.e the value TBC, see image

synapse

But on my personal SQL Server I get the value i.e TBC - using the exact same code.

sqlserver

How strange

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 5

carlton 84646

Ten Centuries

Points: 1026

July 8, 2022 at 7:27 pm

#4060773

This is soooooo strange

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 6

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??

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 7

carlton 84646

Ten Centuries

Points: 1026

July 8, 2022 at 7:50 pm

#4060778

Any further thoughts?

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 8

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)

Ed B Hall of Fame Points: 3665 More actions · Answer 9

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,

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 10

I'll try anything at this stage ... going to try it now.

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 11

Getting the following error:

Incorrect syntax near the keyword 'null'.

kaj SSCommitted Points: 1605 More actions · Answer 12

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))

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 13

Still getting NULLs.

I really haven't got a clue at this stage

Thanks for trying

ratbak Ten Centuries Points: 1358 More actions · Answer 14

ratbak

Ten Centuries

Points: 1358

July 8, 2022 at 9:35 pm

#4060851

deleted