Programming Newbie Programming Newbie - 21 days ago 7
SQL Question

A constant expression was encountered in the ORDER BY list

Below is my dynamic query and it's not working.

It threw "A constant expression was encountered in the ORDER BY list, position 2."

I dunno what's wrong.

Original dynamic query:

SET @QueryVendorName = ';WITH
cteForPriceVen AS (select AI.ItemID, AI.ItemPartNumber as ItemPartNumber, AI.ItemDescription, cteForPrice.VendorPrice as Price, cteForPrice.UpdatedDate as UpdatedDate, cteForPrice.IsLocked as IsLocked
from AerospaceItems (nolock) AI
inner join VendorItemPricing (nolock) cteForPrice
on AI.ItemPartNumber = cteForPrice.ItemPartNumber where cteForPrice.VendorName = ''' + @VendorName + ''' AND
cteForPrice.ObsoleteItem = ''' + cast (@ItemType as char(1)) + ''') select * from cteForPriceVen'

SET @OrderQuery = '
WHERE (''' + cast (@Description as varchar(250)) + ''' = '''' OR cteForPriceVen.ItemDescription like ''%' + cast (@Description as varchar(250)) + '%'')
AND (''' + cast (@PartNumber as varchar(99)) + ''' = '''' OR cteForPriceVen.ItemPartNumber like ''%' + cast (@PartNumber as varchar(99)) + '%'')
AND (''' + cast (@PriceFrom as varchar(25)) + ''' = '''' OR Price >= ''' + cast (@PriceFrom as varchar(99)) + ''')
AND (''' + cast (@PriceTo as varchar(25)) + ''' = '''' OR Price <= ''' + cast (@PriceTo as varchar(99)) + ''')
AND (''' + cast (@DateFrom as varchar(25)) + ''' = '''' OR UpdatedDate >= ''' + cast (@DateFrom as varchar(99)) + ''')
AND (''' + cast (@DateTo as varchar(25)) + ''' = '''' OR UpdatedDate <= ''' + cast (@DateTo as varchar(99)) + ''')

ORDER BY
CASE WHEN '''+ @OrderBy +'''=''ItemDescription'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''0'' THEN cteForPriceVen.ItemDescription END ASC,
CASE WHEN '''+ @OrderBy +'''=''ItemDescription'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''1'' THEN cteForPriceVen.ItemDescription END DESC,
CASE WHEN '''+ @OrderBy +'''=''ItemPartNumber'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''0'' THEN cteForPriceVen.ItemPartNumber END ASC,
CASE WHEN '''+ @OrderBy +'''=''ItemPartNumber'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''1'' THEN cteForPriceVen.ItemPartNumber END DESC,
CASE WHEN '''+ @OrderBy +'''=''Price'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''0'' THEN Price END ASC,
CASE WHEN '''+ @OrderBy +'''=''Price'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''1'' THEN Price END DESC,
CASE WHEN '''+ @OrderBy +'''=''UpdatedDate'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''0'' THEN UpdatedDate END ASC,
CASE WHEN '''+ @OrderBy +'''=''UpdatedDate'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''1'' THEN UpdatedDate END DESC'


Extracted Query:

;WITH
cteForPriceVen AS (select AI.ItemID, AI.ItemPartNumber as ItemPartNumber, AI.ItemDescription, cteForPrice.VendorPrice as Price, cteForPrice.UpdatedDate as UpdatedDate, cteForPrice.IsLocked as IsLocked
from AerospaceItems (nolock) AI
inner join VendorItemPricing (nolock) cteForPrice
on AI.ItemPartNumber = cteForPrice.ItemPartNumber where cteForPrice.VendorName = 'Apple' AND
cteForPrice.ObsoleteItem = '0') select * from cteForPriceVen
WHERE ('' = '' OR cteForPriceVen.ItemDescription like '%%')
AND ('' = '' OR cteForPriceVen.ItemPartNumber like '%%')
AND ('' = '' OR Price >= '')
AND ('' = '' OR Price <= '')
AND ('' = '' OR UpdatedDate >= '')
AND ('' = '' OR UpdatedDate <= '')

ORDER BY
CASE WHEN 'ItemDescription'='ItemDescription' AND '0'= '0' THEN cteForPriceVen.ItemDescription END ASC,
CASE WHEN 'ItemDescription'='ItemDescription' AND '0'= '1' THEN cteForPriceVen.ItemDescription END DESC


If I remove second order by line,
CASE WHEN 'ItemDescription'='ItemDescription' AND '0'= '1' THEN cteForPriceVen.ItemDescription END DESC
the query seems working.

Please help me in fixing this.

Answer

The second line:

CASE WHEN 'ItemDescription'='ItemDescription' AND '0'= '1' THEN cteForPriceVen.ItemDescription END DESC

Is equivalent to NULL. You can't order something by NULL.

edit

If this statement is being generated by a dynamic query, what you need to do is fix the way you build the dynamic query:

@orderQuery = ' ORDER BY '
IF @OrderBy = 'ItemDescription'
BEGIN
  orderQuery += ' cteForPriceVen.ItemDescription '
  orderQuery += CASE WHEN @OrderMode = 1 THEN 'ASC' ELSE 'DESC' END
END