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,523 questions
{count} votes

4 answers

Sort by: Most helpful
  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 No comments

  2. Naomi 7,361 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 99,461 Reputation points MVP
    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