• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

INVALID COLUMN NAME? : In SQL SERVER QUERY ANALYZER - What's wrong with my query? Can't figure it out!

What went wrong?  How can I fix it?

Here's the error:  
   Server: Msg 207, Level 16, State 3, Line 7
   Invalid column name 'ctMarketingSKU'.

Here is the code:


create table #promo (ctMarketingSKU varchar (50));

Insert into #promo  (ctMarketingSKU) values ('MXPAFLXDTSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPAFLXPGSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPALPSAREC');

select
      case
            when p.swdatecreated is null then 'Not Activated'
            else 'Activated'
      end as status
      , #promo.ctMarketingSKU
      , p.swdatecreated as ActivationDate
      , c.ctorgid as ORGid
      , c.swname as Company
      , pe.swfirstname as ContactFirst
      , pe.swlastname as ContactLast
      , pe.swjobtitle as Title
      , pe.swextemailaddress
      , c.swmainphonearea as AreaCode
      , c.swmainphone as Phone#
      , a.swcity as City
      , a.swstate as State
      -- aditional fields
      , price.ctMarketingSKU
      , price.ctModel
      , price.ctSKUType
      , price.ctOpenPrice
      , price.ctChannelPrice
      , price.ctActive
      , price.ctUserCount
      , price.ctProductFamily
      , price.ctRevenueGroup
      , price.ctLicenseType
      , price.ctSubsAdvantage
      , price.swDateCreated
from #promo
left join sw_inst_product p (nolock)
      on #promo.ctMarketingSKU = p.ctMarketingSKU
-- additional tables start
left outer join sw_prod_release r
      on r.swprodreleaseid = p.swprodreleaseid
left outer join ct_Prod_release_skus sku
      on sku.swprodreleaseid = r.swprodreleaseid
left outer join ct_sap_price price
      on price.ctMarketingSKU = sku.ctMarketingSKU
-- additional tables end
left join sw_customer c (nolock)
      on p.swcustomerid = c.swcustomerid
left join sw_address a (nolock)
      on c.swcustomerid = a.swobjectid
left join ct_inst_prod_subscription s (nolock)
      on p.swinstprodid = s.swinstprodid
left join sw_person pe (nolock)
      on s.swpersonid = pe.swpersonid
where
      a.swtype is null or a.swtype = 'Physical Location'
order by #promo.ctMarketingSKU


0
iquagmire
Asked:
iquagmire
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need a GO between the create table #promo and the resto of the script...
also, you need to use, in the query, a table alias for the #promo table name.


i suggest to use a table variable instead, assuming you use sql 2000 or higher:


declare @promo table (ctMarketingSKU varchar (50));

Insert into @promo  (ctMarketingSKU) values ('MXPAFLXDTSAREC');
Insert into @promo  (ctMarketingSKU) values ('MXPAFLXPGSAREC');
Insert into @promo  (ctMarketingSKU) values ('MXPALPSAREC');

select
      case
            when p.swdatecreated is null then 'Not Activated'
            else 'Activated'
      end as status
      , tmp_promo.ctMarketingSKU
      , p.swdatecreated as ActivationDate
      , c.ctorgid as ORGid
      , c.swname as Company
      , pe.swfirstname as ContactFirst
      , pe.swlastname as ContactLast
      , pe.swjobtitle as Title
      , pe.swextemailaddress
      , c.swmainphonearea as AreaCode
      , c.swmainphone as Phone#
      , a.swcity as City
      , a.swstate as State
      -- aditional fields
      , price.ctMarketingSKU
      , price.ctModel
      , price.ctSKUType
      , price.ctOpenPrice
      , price.ctChannelPrice
      , price.ctActive
      , price.ctUserCount
      , price.ctProductFamily
      , price.ctRevenueGroup
      , price.ctLicenseType
      , price.ctSubsAdvantage
      , price.swDateCreated
from @promo tmp_promo
left join sw_inst_product p (nolock)
      on tmp_promo.ctMarketingSKU = p.ctMarketingSKU
-- additional tables start
left outer join sw_prod_release r
      on r.swprodreleaseid = p.swprodreleaseid
left outer join ct_Prod_release_skus sku
      on sku.swprodreleaseid = r.swprodreleaseid
left outer join ct_sap_price price
      on price.ctMarketingSKU = sku.ctMarketingSKU
-- additional tables end
left join sw_customer c (nolock)
      on p.swcustomerid = c.swcustomerid
left join sw_address a (nolock)
      on c.swcustomerid = a.swobjectid
left join ct_inst_prod_subscription s (nolock)
      on p.swinstprodid = s.swinstprodid
left join sw_person pe (nolock)
      on s.swpersonid = pe.swpersonid
where
      a.swtype is null or a.swtype = 'Physical Location'
order by tmp_promo.ctMarketingSKU





0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now