Help Implementing Sample SQL Code

• carlton 84646

  Ten Centuries

  Points: 1026

  More actions

  June 27, 2022 at 4:35 pm

  #4055658

  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?