Share via

SQL Code Error converting data type varchar to bigint

Carlton Patterson 741 Reputation points
2022-07-08T17:17:33.457+00:00

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.

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 field's to BIGINT.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Carlton Patterson 741 Reputation points
    2022-07-08T17:31:37.933+00:00

    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

    Not sure if that will help.

    Let me know if you need sample data?

    0 comments
  2. Naomi 7,366 Reputation points
    2022-07-08T18:04:24.937+00:00

    Did you figure out which column is giving the problem? E.g. does this run OK? 

    ;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  
        
        ,C1.customertypecode,C1.address1_addresstypecode,C1.accountclassificationcode,C1.ts_easeofworking   
        
        ,C1.preferredappointmenttimecode,C1.xpd_relationshipstatus,C1.ts_relationship  
  FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.RowNum = C2.RowNum
  3. Carlton Patterson 741 Reputation points
    2022-07-08T19:41:08.153+00:00

    Hi Viorel,

    The tables and views DO contain NULLs

    Sample Data 

    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

    Sample data 

    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
  4. Erland Sommarskog 107.2K Reputation points
    2022-07-09T11:28:45.03+00:00

    If this is a serverless pool, I assume that you have created your table with CREATE EXTERNAL TABLE AS SELECT (CETAS). I would guess that you somehow got the datatypes incorrectly inferred there.

    Use this query to check what data types you actually have: 

       SELECT o.name AS tblname, c.name AS colanme, t.name AS typename  
   FROM   sys.objects o  
   JOIN   sys.columns c  ON o.object_id = c.object_id  
   JOIN   sys.types t ON t.user_type_id = c.user_type_id  
   WHERE  o.name IN ('OptionsetMetadata', 'account')  
   ORDER  BY o.name, c.column_id

    Would you mind sending a screen shot of your output? I'm really intrigured

    219026-image.png