Help Implementing Sample SQL Code

  • Hi community,

    I am trying to implement sample sql code:

    SELECT [LocalizedLabel] as [<COLUMN_NAME>] 
    FROM [<DATABASE_NAME>].[dbo].[<TABLE_NAME>_partitioned]
    LEFT JOIN [<DATABASE_NAME>].[dbo].[OptionsetMetadata]
    ON ([<DATABASE_NAME>].[dbo].[OptionsetMetadata].[Option] = [<DATABASE_NAME>].[dbo].[<TABLE_NAME>_partitioned].[<COLUMN_NAME>] AND [<DATABASE_NAME>].[dbo].[OptionsetMetadata].[OptionSetName] = <COLUMN_NAME>)

    The code is taken from https://docs.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-choice-labels

    I have modified the code to include my details as follows:

    SELECT [LocalizedLabel] as [mynewcolumn] 
    FROM [originationdb].[dbo].[account]
    LEFT JOIN [originationdb].[dbo].[OptionsetMetadata]
    ON ([originationdb].[dbo].[OptionsetMetadata].[Option] = [originationdb].[dbo].[account].[mynewcolumn] AND [originationdb].[dbo].[OptionsetMetadata].[OptionSetName] = mynewcolumn)

    But I get the error

    Invalid column name 'mynewcolumn'.

    I don't understand because the site states enter a new column name.

    The sample data is as follows:

    CREATE TABLE OptionsetMetadata (
    EntityName varchar(50),
    OptionSetName varchar(50),
    Option int,
    IsUserLocalizedLabel bit,
    LocalizedLabelLanguageCode int,
    LocalizedLabel varchar(100))

    INSERT OptionsetMetadata VALUES
    ('activityparty','participationtypemask',1,CONVERT(bit, 'False'),1033,'Sender'),
    ('activityparty','participationtypemask',2,CONVERT(bit, 'False'),1033,'To Recipient'),
    ('activityparty','participationtypemask',3,CONVERT(bit, 'False'),1033,'CC Recipient'),
    ('activityparty','participationtypemask',4,CONVERT(bit, 'False'),1033,'BCC Recipient'),
    ('activityparty','participationtypemask',5,CONVERT(bit, 'False'),1033,'Required attendee'),
    ('activityparty','participationtypemask',6,CONVERT(bit, 'False'),1033,'Optional attendee'),
    ('activityparty','participationtypemask',7,CONVERT(bit, 'False'),1033,'Organizer'),
    ('activityparty','participationtypemask',8,CONVERT(bit, 'False'),1033,'Regarding'),
    ('activityparty','participationtypemask',9,CONVERT(bit, 'False'),1033,'Owner'),
    ('activityparty','participationtypemask',10,CONVERT(bit, 'False'),1033,'Resource'),
    ('activityparty','participationtypemask',11,CONVERT(bit, 'False'),1033,'Customer'),
    ('activityparty','instancetypecode',0,CONVERT(bit, 'False'),1033,'Not Recurring'),
    ('activityparty','instancetypecode',1,CONVERT(bit, 'False'),1033,'Recurring Master'),
    ('activityparty','instancetypecode',2,CONVERT(bit, 'False'),1033,'Recurring Instance'),
    ('activityparty','instancetypecode',3,CONVERT(bit, 'False'),1033,'Recurring Exception'),
    ('activityparty','instancetypecode',4,CONVERT(bit, 'False'),1033,'Recurring Future Exception'),
    ('activityparty','donotpostalmail',1,CONVERT(bit, 'False'),1033,'Do Not Allow'),
    ('activityparty','donotpostalmail',0,CONVERT(bit, 'False'),1033,'Allow'),
    ('activityparty','donotfax',1,CONVERT(bit, 'False'),1033,'Do Not Allow'),
    ('activityparty','donotfax',0,CONVERT(bit, 'False'),1033,'Allow'),
    ('activityparty','donotphone',1,CONVERT(bit, 'False'),1033,'Do Not Allow'),
    ('activityparty','donotphone',0,CONVERT(bit, 'False'),1033,'Allow'),
    ('activityparty','ispartydeleted',1,CONVERT(bit, 'False'),1033,'Yes'),
    ('activityparty','ispartydeleted',0,CONVERT(bit, 'False'),1033,'No'),
    ('activityparty','donotemail',1,CONVERT(bit, 'False'),1033,'Do Not Allow'),
    ('activityparty','donotemail',0,CONVERT(bit, 'False'),1033,'Allow'),
    ('account','ts_primarysecondaryfocus',717750000,CONVERT(bit, 'False'),1033,'Tier 1'),
    ('account','ts_primarysecondaryfocus',717750001,CONVERT(bit, 'False'),1033,'Tier 2'),
    ('account','ts_primarysecondaryfocus',717750002,CONVERT(bit, 'False'),1033,'TBC'),
    ('account','customertypecode',1,CONVERT(bit, 'False'),1033,'Competitor'),
    ('account','customertypecode',2,CONVERT(bit, 'False'),1033,'Consultant'),
    ('account','customertypecode',3,CONVERT(bit, 'False'),1033,'Customer'),
    ('account','customertypecode',4,CONVERT(bit, 'False'),1033,'Investor'),
    ('account','customertypecode',5,CONVERT(bit, 'False'),1033,'Partner'),
    ('account','customertypecode',6,CONVERT(bit, 'False'),1033,'Influencer'),
    ('account','customertypecode',7,CONVERT(bit, 'False'),1033,'Press'),
    ('account','customertypecode',8,CONVERT(bit, 'False'),1033,'Prospect'),
    ('account','customertypecode',9,CONVERT(bit, 'False'),1033,'Reseller'),
    ('account','customertypecode',10,CONVERT(bit, 'False'),1033,'Supplier'),
    ('account','customertypecode',11,CONVERT(bit, 'False'),1033,'Vendor'),
    ('account','customertypecode',12,CONVERT(bit, 'False'),1033,'Other'),
    ('account','address1_addresstypecode',1,CONVERT(bit, 'False'),1033,'Bill To'),
    ('account','address1_addresstypecode',2,CONVERT(bit, 'False'),1033,'Ship To'),
    ('account','address1_addresstypecode',3,CONVERT(bit, 'False'),1033,'Primary'),
    ('account','address1_addresstypecode',4,CONVERT(bit, 'False'),1033,'Other'),
    ('account','accountclassificationcode',1,CONVERT(bit, 'False'),1033,'Default Value'),
    ('account','ts_ukrow',717750000,CONVERT(bit, 'False'),1033,'UK'),
    ('account','ts_ukrow',717750001,CONVERT(bit, 'False'),1033,'RoW'),
    ('account','preferredappointmenttimecode',1,CONVERT(bit, 'False'),1033,'Morning'),
    ('account','preferredappointmenttimecode',2,CONVERT(bit, 'False'),1033,'Afternoon'),
    ('account','preferredappointmenttimecode',3,CONVERT(bit, 'False'),1033,'Evening'),
    ('account','address2_freighttermscode',1,CONVERT(bit, 'False'),1033,'Default Value'),
    ('account','ts_introducertype',717750000,CONVERT(bit, 'False'),1033,'Primary'),
    ('account','ts_introducertype',717750001,CONVERT(bit, 'False'),1033,'Boutique'),
    ('account','ts_introducertype',717750002,CONVERT(bit, 'False'),1033,'T2 Generalists'),
    ('account','accountcategorycode',1,CONVERT(bit, 'False'),1033,'Preferred Customer'),
    ('account','accountcategorycode',2,CONVERT(bit, 'False'),1033,'Standard'),
    ('account','paymenttermscode',1,CONVERT(bit, 'False'),1033,'Net 30'),
    ('account','paymenttermscode',2,CONVERT(bit, 'False'),1033,'2% 10, Net 30'),
    ('account','paymenttermscode',3,CONVERT(bit, 'False'),1033,'Net 45'),
    ('account','paymenttermscode',4,CONVERT(bit, 'False'),1033,'Net 60'),
    ('account','preferredappointmentdaycode',0,CONVERT(bit, 'False'),1033,'Sunday'),
    ('account','preferredappointmentdaycode',1,CONVERT(bit, 'False'),1033,'Monday'),
    ('account','preferredappointmentdaycode',2,CONVERT(bit, 'False'),1033,'Tuesday'),
    ('account','preferredappointmentdaycode',3,CONVERT(bit, 'False'),1033,'Wednesday'),
    ('account','preferredappointmentdaycode',4,CONVERT(bit, 'False'),1033,'Thursday'),
    ('account','preferredappointmentdaycode',5,CONVERT(bit, 'False'),1033,'Friday'),
    ('account','preferredappointmentdaycode',6,CONVERT(bit, 'False'),1033,'Saturday'),
    ('account','businesstypecode',1,CONVERT(bit, 'False'),1033,'Default Value'),
    ('account','industrycode',1,CONVERT(bit, 'False'),1033,'Accounting'),
    ('account','industrycode',2,CONVERT(bit, 'False'),1033,'Agriculture and Non-petrol Natural Resource Extraction'),
    ('account','industrycode',3,CONVERT(bit, 'False'),1033,'Broadcasting Printing and Publishing'),
    ('account','industrycode',4,CONVERT(bit, 'False'),1033,'Brokers'),
    ('account','industrycode',5,CONVERT(bit, 'False'),1033,'Building Supply Retail'),
    ('account','industrycode',6,CONVERT(bit, 'False'),1033,'Business Services'),
    ('account','industrycode',7,CONVERT(bit, 'False'),1033,'Consulting'),
    ('account','industrycode',8,CONVERT(bit, 'False'),1033,'Consumer Services'),
    ('account','industrycode',9,CONVERT(bit, 'False'),1033,'Design, Direction and Creative Management'),
    ('account','industrycode',10,CONVERT(bit, 'False'),1033,'Distributors, Dispatchers and Processors'),
    ('account','industrycode',11,CONVERT(bit, 'False'),1033,'Doctor''s Offices and Clinics'),
    ('account','industrycode',12,CONVERT(bit, 'False'),1033,'Durable Manufacturing'),
    ('account','industrycode',13,CONVERT(bit, 'False'),1033,'Eating and Drinking Places'),
    ('account','industrycode',14,CONVERT(bit, 'False'),1033,'Entertainment Retail'),
    ('account','industrycode',15,CONVERT(bit, 'False'),1033,'Equipment Rental and Leasing'),
    ('account','industrycode',16,CONVERT(bit, 'False'),1033,'Financial'),
    ('account','industrycode',17,CONVERT(bit, 'False'),1033,'Food and Tobacco Processing'),
    ('account','industrycode',18,CONVERT(bit, 'False'),1033,'Inbound Capital Intensive Processing'),
    ('account','industrycode',19,CONVERT(bit, 'False'),1033,'Inbound Repair and Services'),
    ('account','industrycode',20,CONVERT(bit, 'False'),1033,'Insurance'),
    ('account','industrycode',21,CONVERT(bit, 'False'),1033,'Legal Services'),
    ('account','industrycode',22,CONVERT(bit, 'False'),1033,'Non-Durable Merchandise Retail'),
    ('account','industrycode',23,CONVERT(bit, 'False'),1033,'Outbound Consumer Service'),
    ('account','industrycode',24,CONVERT(bit, 'False'),1033,'Petrochemical Extraction and Distribution'),
    ('account','industrycode',25,CONVERT(bit, 'False'),1033,'Service Retail'),
    ('account','industrycode',26,CONVERT(bit, 'False'),1033,'SIG Affiliations'),
    ('account','industrycode',27,CONVERT(bit, 'False'),1033,'Social Services'),
    ('account','industrycode',28,CONVERT(bit, 'False'),1033,'Special Outbound Trade Contractors'),
    ('account','industrycode',29,CONVERT(bit, 'False'),1033,'Specialty Realty'),
    ('account','industrycode',30,CONVERT(bit, 'False'),1033,'Transportation'),
    ('account','industrycode',31,CONVERT(bit, 'False'),1033,'Utility Creation and Distribution')

    SELECT * FROM OptionsetMetadata

     

    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,
    ts_capabilitiesoffirm varchar(255),
    mpe_flowlastrunoutcome varchar(255))

    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,'',''),
    ('c6a24708-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,NULL,'',NULL,'',1,'','','','','',1,'','','','','',1,'','',1,1,NULL,'',''),
    ('c8a24708-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,NULL,'',NULL,'',1,'','','','','',1,'','','','','',1,'','',1,1,NULL,'',''),
    ('caa24708-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,NULL,'',NULL,'',1,'','','','','',1,'','','','','',1,'','',1,1,NULL,'',''),
    ('cca24708-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,NULL,'',NULL,'',1,'','','','','',1,'','','','','',1,'','',1,1,NULL,'',''),
    ('cea24708-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,NULL,'',NULL,'',1,'','','','','',1,'','','','','',1,'','',1,1,NULL,'',''),
    ('d0a24708-0989-e811-a95d-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,930580001,'',930580000,'',1,'','','','','',1,'','','','','',1,'','',1,1,NULL,'',''),
    ('d8a24708-0989-e811-a95d-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,NULL,'','')

    SELECT * FROM account

     

    Any thoughts on where I'm going wrong?

     

  • ok, I see where I've gone wrong.

    I changed the code to:

    SELECT [LocalizedLabel] as [mynewcolumn] 
    FROM [originationdb].[dbo].[OptionsetMetadata]
    LEFT JOIN [originationdb].[dbo].[OptionsetMetadata]
    ON ([originationdb].[dbo].[OptionsetMetadata].[Option] = [originationdb].[dbo].[account].[mynewcolumn] AND [originationdb].[dbo].[OptionsetMetadata].[OptionSetName] = mynewcolumn)

    But now I'm getting the following error:

    1 The objects "originationdb.dbo.OptionsetMetadata" and "originationdb.dbo.OptionsetMetadata" in the FROM clause have the same exposed names. Use correlation names to distinguish them. S

     

     

  • Really would love to get some help with this one guys/girls

  • Since you're referencing the same table twice, you need to use aliases to give each table a different name so SQL can tell which version of the table each column comes from, for exampe:

    SELECT OM1.[LocalizedLabel] as [mynewcolumn]

    FROM [originationdb].[dbo].[OptionsetMetadata] OM1

    LEFT JOIN [originationdb].[dbo].[OptionsetMetadata] OM2

    ON (OM2.[Option] = [mynewcolumn] AND OM2.[OptionSetName] = mynewcolumn)

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Hi Scott,

    Thanks for reaching out.

    I tried your code, but I'm getting the following error:

     

    3 Invalid column name 'mynewcolumn'.

  • To separately illustrate the problem, consider this code:

    SELECT columnA

    FROM dbo.table1

    LEFT OUTER JOIN dbo.table1 ON id = parent_id

    Which columnA do you want, the one from the first table or the second?

    Similarly, which id / parent_id do you want, the one from the first table or the second?

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  •  

    SELECT [mynewcolumn]
    FROM [originationdb].[dbo].[OptionsetMetadata] OM1
    CROSS APPLY (
    SELECT OM1.[LocalizedLabel] as [mynewcolumn]
    ) AS CA1
    LEFT JOIN [originationdb].[dbo].[OptionsetMetadata] OM2
    ON (OM2.[Option] = [mynewcolumn] AND OM2.[OptionSetName] = mynewcolumn)

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Hi Scott,

    The one from the second table

     

    Thanks

  • I'm now getting the error:

     

    Conversion failed when converting the varchar value 'Sender' to data type int.

  • Adjust the table aliases as needed for the SQL above.  I moved the code into a code box to make it easier to view.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • [Option] is an int.  I suspect you may be comparing the wrong columns in the JOIN?!

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • I changed IsUserLocalizedLabel is VARCHAR, but now I'm getting the error:

     

    3 Invalid column name 'mynewcolumn'.

  • The columns that I really want to do a join on are:

    instancetypecode

    donotpostalmail

    donotfax

    donotphone

    ispartydeleted

    donotemail

    ts_primarysecondaryfocus

    customertypecode

    address1_addresstypecode

    accountclassificationcode

    ts_ukrow

  • Any more thoughts Scott

  • ok, I changed the Option column to varchar, the code:

     

    SELECT [mynewcolumn]
    FROM [originationdb].[dbo].[OptionsetMetadata] OM1
    CROSS APPLY (
    SELECT OM1.[LocalizedLabel] as [mynewcolumn]
    ) AS CA1
    LEFT JOIN [originationdb].[dbo].[OptionsetMetadata] OM2
    ON (OM2.[Option] = [mynewcolumn] AND OM2.[OptionSetName] = mynewcolumn)

    Returns

    Sender

    To Recipient

    CC Recipient

    BCC Recipient

    Required attendee

    Optional attendee

    Organizer

    Regarding

    Owner

    Resource

    Customer

    Not Recurring

    Recurring Master

    Recurring Instance

    Recurring Exception

    Recurring Future Exception

    Do Not Allow

    Allow

    Do Not Allow

    Allow

    Do Not Allow

    Allow

    Yes

    No

    Do Not Allow

    Allow

    Tier 1

    Tier 2

    TBC

    Competitor

    Consultant

    Customer

    Investor

    Partner

    Influencer

    Press

    Prospect

    Reseller

    Supplier

    Vendor

    Other

    Bill To

    Ship To

    Primary

    Other

    Default Value

    UK

    RoW

    Morning

    Afternoon

    Evening

    Default Value

    Primary

    Boutique

    T2 Generalists

    Preferred Customer

    Standard

    Net 30

    2% 10, Net 30

    Net 45

    Net 60

    Sunday

    Monday

    Tuesday

    Wednesday

    Thursday

    Friday

    Saturday

    Default Value

    Accounting

    Agriculture and Non-petrol Natural Resource Extraction

    Broadcasting Printing and Publishing

    Brokers

    Building Supply Retail

    Business Services

    Consulting

    Consumer Services

    Design, Direction and Creative Management

    Distributors, Dispatchers and Processors

    Doctor's Offices and Clinics

    Durable Manufacturing

    Eating and Drinking Places

    Entertainment Retail

    Equipment Rental and Leasing

    Financial

    Food and Tobacco Processing

    Inbound Capital Intensive Processing

    Inbound Repair and Services

    Insurance

    Legal Services

    Non-Durable Merchandise Retail

    Outbound Consumer Service

    Petrochemical Extraction and Distribution

    Service Retail

    SIG Affiliations

    Social Services

    Special Outbound Trade Contractors

    Specialty Realty

    Transportation

    Utility Creation and Distribution

     

    Very strange

     

     

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

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