Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Invalid Column Name Trouble

Author  Topic 

rizzy
Starting Member

5 Posts
Posted - 2007-10-19 : 15:38:18
Ok I can not figure this out on this stored procedure. The end of the query is coded like this:

ORDER BY CASE WHEN @Sort = 'lowtohigh' THEN SortPrice END ASC,
         CASE WHEN @Sort = 'hightolow' THEN SortPrice END DESC,
         CASE WHEN @Sort = 'name' THEN p.Name END


It comes back and gives me "Invalid column name 'SortPrice'."

If I change the code to:

ORDER BY SortPrice


It works and does not give me the invalid column name. Why would it say its invalid in the very top code but not be invalid in the bottom code?

Thanks for any help anyone can give on this.

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-10-19 : 15:48:56
show us the whole query.
is SortPrice an alias for a computed column?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

rizzy
Starting Member

5 Posts
Posted - 2007-10-19 : 15:53:17
quote:
Originally posted by spirit1

show us the whole query.
is SortPrice an alias for a computed column?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com


Yes it is an alias I select it with this:

CASE WHEN isnull(pv.SalePrice, 0) > 0 THEN pv.SalePrice ELSE pv.Price END AS SortPrice,
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-10-19 : 15:59:21
try doing this
select *
from
(
your query here without the order by
) t
ORDER BY CASE WHEN @Sort = 'lowtohigh' THEN SortPrice END ASC,
CASE WHEN @Sort = 'hightolow' THEN SortPrice END DESC,
CASE WHEN @Sort = 'name' THEN Name END


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

rizzy
Starting Member

5 Posts
Posted - 2007-10-19 : 16:03:36
quote:
Originally posted by spirit1

try doing this
select *
from
(
your query here without the order by
) t
ORDER BY CASE WHEN @Sort = 'lowtohigh' THEN SortPrice END ASC,
CASE WHEN @Sort = 'hightolow' THEN SortPrice END DESC,
CASE WHEN @Sort = 'name' THEN Name END


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com


That gives me the same invalid column name on SortPrice.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-10-19 : 16:07:09
show us the whole query

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

rizzy
Starting Member

5 Posts
Posted - 2007-10-19 : 16:08:59
[code]
SELECT
p.ProductID,
p.Name,
pv.VariantID,
pv.Name AS VariantName,
p.ProductGUID,
p.Summary,
p.Description,
p.SEKeywords,
p.SEDescription,
p.SpecTitle,
p.MiscText,
p.SwatchImageMap,
p.IsFeaturedTeaser,
p.FroogleDescription,
p.SETitle,
p.SENoScript,
p.SEAltText,
p.SizeOptionPrompt,
p.ColorOptionPrompt,
p.TextOptionPrompt,
p.ProductTypeID,
p.TaxClassID,
p.SKU,
p.ManufacturerPartNumber,
p.SalesPromptID,
p.SpecCall,
p.SpecsInline,
p.IsFeatured,
p.XmlPackage,
p.ColWidth,
p.Published,
p.RequiresRegistration,
p.Looks,
p.Notes,
p.QuantityDiscountID,
p.RelatedProducts,
p.UpsellProducts,
p.UpsellProductDiscountPercentage,
p.RelatedDocuments,
p.TrackInventoryBySizeAndColor,
p.TrackInventoryBySize,
p.TrackInventoryByColor,
p.IsAKit,
p.ShowInProductBrowser,
p.IsAPack,
p.PackSize,
p.ShowBuyButton,
p.RequiresProducts,
p.HidePriceUntilCart,
p.IsCalltoOrder,
p.ExcludeFromPriceFeeds,
p.RequiresTextOption,
p.TextOptionMaxLength,
p.SEName,
p.Deleted,
p.CreatedOn,
p.ImageFileNameOverride,
pv.VariantGUID,
pv.Description AS VariantDescription,
pv.SEKeywords AS VariantSEKeywords,
pv.SEDescription AS VariantSEDescription,
pv.Colors,
pv.ColorSKUModifiers,
pv.Sizes,
pv.SizeSKUModifiers,
pv.FroogleDescription AS VariantFroogleDescription,
pv.SKUSuffix,
pv.ManufacturerPartNumber AS VariantManufacturerPartNumber,
pv.Price,
pv.CustomerEntersPrice,
pv.CustomerEntersPricePrompt,
isnull(pv.SalePrice, 0) SalePrice,
cast(isnull(pv.Weight,0) as decimal(10,1)) Weight,
pv.MSRP,
pv.Cost,
CASE WHEN isnull(pv.SalePrice, 0) > 0 THEN pv.SalePrice ELSE pv.Price END AS SortPrice,
isnull(pv.Points,0) Points,
pv.Dimensions,
case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end Inventory,
pv.DisplayOrder as VariantDisplayOrder,
pv.Notes AS VariantNotes,
pv.IsTaxable,
pv.IsShipSeparately,
pv.IsDownload,
pv.DownloadLocation,
pv.Published AS VariantPublished,
pv.IsSecureAttachment,
pv.IsRecurring,
pv.RecurringInterval,
pv.RecurringIntervalType,
pv.SubscriptionInterval,
pv.SEName AS VariantSEName,
pv.RestrictedQuantities,
pv.MinimumQuantity,
pv.Deleted AS VariantDeleted,
pv.CreatedOn AS VariantCreatedOn,
d.Name AS DistributorName,
d.DistributorID,
d.SEName AS DistributorSEName,
m.ManufacturerID,
m.Name AS ManufacturerName,
m.SEName AS ManufacturerSEName,
s.Name AS SalesPromptName,
case when pcl.productid is null then 0 else isnull(ep.Price, 0) end ExtendedPrice
FROM Product p with (NOLOCK)
left join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
join @productfilter pf on pv.ProductID = pf.ProductID and pv.VariantID = pf.VariantID
left join SalesPrompt s with (NOLOCK) on p.SalesPromptID = s.SalesPromptID
left join ProductManufacturer pm with (NOLOCK) on p.ProductID = pm.ProductID
left join Manufacturer m with (NOLOCK) on pm.ManufacturerID = m.ManufacturerID
left join ProductDistributor pd with (NOLOCK) on p.ProductID = pd.ProductID
left join Distributor d with (NOLOCK) on pd.DistributorID = d.DistributorID
left join ExtendedPrice ep with (NOLOCK) on ep.VariantID = pv.VariantID and ep.CustomerLevelID = @CustomerLevelID
left join ProductCustomerLevel pcl with (NOLOCK) on p.ProductID = pcl.ProductID and pcl.CustomerLevelID = @CustomerLevelID
left join (select VariantID, sum(quan) quan from Inventory with (nolock) group by VariantID) i on pv.VariantID = i.VariantID
WHERE pf.rownum >= @pagesize*(@pagenum-1)+1 and pf.rownum <= @pagesize*(@pagenum)
ORDER BY CASE WHEN @Sort = 'lowtohigh' THEN SortPrice END,
CASE WHEN @Sort = 'hightolow' THEN SortPrice END DESC,
CASE WHEN @Sort = 'name' THEN p.Name END
[/code]
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2007-10-22 : 03:14:38
If you use conditional order you cant directly use alias name. Use the entire expression in place of SortPrice

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-22 : 03:53:41
First of all I would do what Spirit suggested, which just to make sure you tried it as SPriti intended:

select *
from
(
SELECT 
		p.ProductID,
		p.Name,
		pv.VariantID,
		pv.Name AS VariantName,
		p.ProductGUID,
		p.Summary,
		p.Description,
		p.SEKeywords,
		p.SEDescription,
		p.SpecTitle,
		p.MiscText,
		p.SwatchImageMap,
		p.IsFeaturedTeaser,
		p.FroogleDescription,
		p.SETitle,
		p.SENoScript,
		p.SEAltText,
		p.SizeOptionPrompt,
		p.ColorOptionPrompt,
		p.TextOptionPrompt,
		p.ProductTypeID,
		p.TaxClassID,
		p.SKU,
		p.ManufacturerPartNumber,
		p.SalesPromptID,
		p.SpecCall,
		p.SpecsInline,
		p.IsFeatured,
		p.XmlPackage,
		p.ColWidth,
		p.Published,
		p.RequiresRegistration,
		p.Looks,
		p.Notes,
		p.QuantityDiscountID,
		p.RelatedProducts,
		p.UpsellProducts,
		p.UpsellProductDiscountPercentage,
		p.RelatedDocuments,
		p.TrackInventoryBySizeAndColor,
		p.TrackInventoryBySize,
		p.TrackInventoryByColor,
		p.IsAKit,
		p.ShowInProductBrowser,
		p.IsAPack,
		p.PackSize,
		p.ShowBuyButton,
		p.RequiresProducts,
		p.HidePriceUntilCart,
		p.IsCalltoOrder,
		p.ExcludeFromPriceFeeds,
		p.RequiresTextOption,
		p.TextOptionMaxLength,
		p.SEName,
		p.Deleted,
		p.CreatedOn,
		p.ImageFileNameOverride,
		pv.VariantGUID,
		pv.Description AS VariantDescription,
		pv.SEKeywords AS VariantSEKeywords,
		pv.SEDescription AS VariantSEDescription,
		pv.Colors,
		pv.ColorSKUModifiers,
		pv.Sizes,
		pv.SizeSKUModifiers,
		pv.FroogleDescription AS VariantFroogleDescription,
		pv.SKUSuffix,
		pv.ManufacturerPartNumber AS VariantManufacturerPartNumber,
		pv.Price,
		pv.CustomerEntersPrice, 
		pv.CustomerEntersPricePrompt,
		isnull(pv.SalePrice, 0) SalePrice,
		cast(isnull(pv.Weight,0) as decimal(10,1)) Weight,
		pv.MSRP,
		pv.Cost,
		CASE WHEN isnull(pv.SalePrice, 0) > 0 THEN pv.SalePrice ELSE pv.Price END AS SortPrice, 
		isnull(pv.Points,0) Points,
		pv.Dimensions,
		case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end Inventory,
		pv.DisplayOrder as VariantDisplayOrder,
		pv.Notes AS VariantNotes,
		pv.IsTaxable,
		pv.IsShipSeparately,
		pv.IsDownload,
		pv.DownloadLocation,
		pv.Published AS VariantPublished,
		pv.IsSecureAttachment,
		pv.IsRecurring,
		pv.RecurringInterval,
		pv.RecurringIntervalType,
		pv.SubscriptionInterval,
		pv.SEName AS VariantSEName,
		pv.RestrictedQuantities,
		pv.MinimumQuantity,
		pv.Deleted AS VariantDeleted,
		pv.CreatedOn AS VariantCreatedOn,
		d.Name AS DistributorName,
		d.DistributorID,
		d.SEName AS DistributorSEName,
		m.ManufacturerID,
		m.Name AS ManufacturerName,
		m.SEName AS ManufacturerSEName,
		s.Name AS SalesPromptName,
		case when pcl.productid is null then 0 else isnull(ep.Price, 0) end ExtendedPrice
	FROM Product p with (NOLOCK) 
		left join ProductVariant       pv  with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
		join @productfilter            pf                on pv.ProductID = pf.ProductID and pv.VariantID = pf.VariantID 
		left join SalesPrompt           s  with (NOLOCK) on p.SalesPromptID = s.SalesPromptID 
		left join ProductManufacturer  pm  with (NOLOCK) on p.ProductID = pm.ProductID 
		left join Manufacturer          m  with (NOLOCK) on pm.ManufacturerID = m.ManufacturerID 
		left join ProductDistributor   pd  with (NOLOCK) on p.ProductID = pd.ProductID
		left join Distributor           d  with (NOLOCK) on pd.DistributorID = d.DistributorID
		left join ExtendedPrice        ep  with (NOLOCK) on ep.VariantID = pv.VariantID and ep.CustomerLevelID = @CustomerLevelID
        left join ProductCustomerLevel pcl with (NOLOCK) on p.ProductID = pcl.ProductID and pcl.CustomerLevelID = @CustomerLevelID
		left join (select VariantID, sum(quan) quan from Inventory with (nolock) group by VariantID) i  on pv.VariantID = i.VariantID
	WHERE pf.rownum >= @pagesize*(@pagenum-1)+1 and pf.rownum <= @pagesize*(@pagenum)
) AS t
	ORDER BY CASE WHEN @Sort = 'lowtohigh' THEN SortPrice END,
			 CASE WHEN @Sort = 'hightolow' THEN SortPrice END DESC,
			 CASE WHEN @Sort = 'name' THEN p.Name END

Kristen
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-22 : 03:55:43
You should really REALLY NOT have those NOLOCK hints. It suggests design problems, and sooner or later they will REALLY spoil your day ... and the problem will be virtually impossible to reproduce

Kristen
Go to Top of Page

rizzy
Starting Member

5 Posts
Posted - 2007-10-22 : 08:41:11
quote:
Originally posted by Kristen

First of all I would do what Spirit suggested, which just to make sure you tried it as SPriti intended:

select *
from
(
SELECT 
		p.ProductID,
		p.Name,
		pv.VariantID,
		pv.Name AS VariantName,
		p.ProductGUID,
		p.Summary,
		p.Description,
		p.SEKeywords,
		p.SEDescription,
		p.SpecTitle,
		p.MiscText,
		p.SwatchImageMap,
		p.IsFeaturedTeaser,
		p.FroogleDescription,
		p.SETitle,
		p.SENoScript,
		p.SEAltText,
		p.SizeOptionPrompt,
		p.ColorOptionPrompt,
		p.TextOptionPrompt,
		p.ProductTypeID,
		p.TaxClassID,
		p.SKU,
		p.ManufacturerPartNumber,
		p.SalesPromptID,
		p.SpecCall,
		p.SpecsInline,
		p.IsFeatured,
		p.XmlPackage,
		p.ColWidth,
		p.Published,
		p.RequiresRegistration,
		p.Looks,
		p.Notes,
		p.QuantityDiscountID,
		p.RelatedProducts,
		p.UpsellProducts,
		p.UpsellProductDiscountPercentage,
		p.RelatedDocuments,
		p.TrackInventoryBySizeAndColor,
		p.TrackInventoryBySize,
		p.TrackInventoryByColor,
		p.IsAKit,
		p.ShowInProductBrowser,
		p.IsAPack,
		p.PackSize,
		p.ShowBuyButton,
		p.RequiresProducts,
		p.HidePriceUntilCart,
		p.IsCalltoOrder,
		p.ExcludeFromPriceFeeds,
		p.RequiresTextOption,
		p.TextOptionMaxLength,
		p.SEName,
		p.Deleted,
		p.CreatedOn,
		p.ImageFileNameOverride,
		pv.VariantGUID,
		pv.Description AS VariantDescription,
		pv.SEKeywords AS VariantSEKeywords,
		pv.SEDescription AS VariantSEDescription,
		pv.Colors,
		pv.ColorSKUModifiers,
		pv.Sizes,
		pv.SizeSKUModifiers,
		pv.FroogleDescription AS VariantFroogleDescription,
		pv.SKUSuffix,
		pv.ManufacturerPartNumber AS VariantManufacturerPartNumber,
		pv.Price,
		pv.CustomerEntersPrice, 
		pv.CustomerEntersPricePrompt,
		isnull(pv.SalePrice, 0) SalePrice,
		cast(isnull(pv.Weight,0) as decimal(10,1)) Weight,
		pv.MSRP,
		pv.Cost,
		CASE WHEN isnull(pv.SalePrice, 0) > 0 THEN pv.SalePrice ELSE pv.Price END AS SortPrice, 
		isnull(pv.Points,0) Points,
		pv.Dimensions,
		case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end Inventory,
		pv.DisplayOrder as VariantDisplayOrder,
		pv.Notes AS VariantNotes,
		pv.IsTaxable,
		pv.IsShipSeparately,
		pv.IsDownload,
		pv.DownloadLocation,
		pv.Published AS VariantPublished,
		pv.IsSecureAttachment,
		pv.IsRecurring,
		pv.RecurringInterval,
		pv.RecurringIntervalType,
		pv.SubscriptionInterval,
		pv.SEName AS VariantSEName,
		pv.RestrictedQuantities,
		pv.MinimumQuantity,
		pv.Deleted AS VariantDeleted,
		pv.CreatedOn AS VariantCreatedOn,
		d.Name AS DistributorName,
		d.DistributorID,
		d.SEName AS DistributorSEName,
		m.ManufacturerID,
		m.Name AS ManufacturerName,
		m.SEName AS ManufacturerSEName,
		s.Name AS SalesPromptName,
		case when pcl.productid is null then 0 else isnull(ep.Price, 0) end ExtendedPrice
	FROM Product p with (NOLOCK) 
		left join ProductVariant       pv  with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
		join @productfilter            pf                on pv.ProductID = pf.ProductID and pv.VariantID = pf.VariantID 
		left join SalesPrompt           s  with (NOLOCK) on p.SalesPromptID = s.SalesPromptID 
		left join ProductManufacturer  pm  with (NOLOCK) on p.ProductID = pm.ProductID 
		left join Manufacturer          m  with (NOLOCK) on pm.ManufacturerID = m.ManufacturerID 
		left join ProductDistributor   pd  with (NOLOCK) on p.ProductID = pd.ProductID
		left join Distributor           d  with (NOLOCK) on pd.DistributorID = d.DistributorID
		left join ExtendedPrice        ep  with (NOLOCK) on ep.VariantID = pv.VariantID and ep.CustomerLevelID = @CustomerLevelID
        left join ProductCustomerLevel pcl with (NOLOCK) on p.ProductID = pcl.ProductID and pcl.CustomerLevelID = @CustomerLevelID
		left join (select VariantID, sum(quan) quan from Inventory with (nolock) group by VariantID) i  on pv.VariantID = i.VariantID
	WHERE pf.rownum >= @pagesize*(@pagenum-1)+1 and pf.rownum <= @pagesize*(@pagenum)
) AS t
	ORDER BY CASE WHEN @Sort = 'lowtohigh' THEN SortPrice END,
			 CASE WHEN @Sort = 'hightolow' THEN SortPrice END DESC,
			 CASE WHEN @Sort = 'name' THEN p.Name END

Kristen


I set it up like this and it gives me an error:

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

It gives that for each line that uses the with keyword.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-22 : 10:51:24
That's a different error that you reported before. Any idea why that is?

That error is not possible, as far as I can see, if you use that exact syntax.

But it may be an associated error from something else, in which case just to narrow that down I suggest you take out all the WITH (NOLOCK) and see what error is then reported - that will probably lead you to a typo somewhere else in the statement.

And apart from that those WITH (NOLOCK) really should not be used in any code against a production database.

If you are still getting errors that are not obvious I would put the word "AS" before each table alias - in case there is a hard-to-find-typo being disguised by one of them:

FROM Product AS p
etc.

Kristen
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site