Ein konstanter Ausdruck gefunden wurde in der ORDER BY-Liste
Unten ist meine dynamische Abfrage und es funktioniert nicht.
Er warf "Ein konstanter Ausdruck gefunden wurde in der ORDER BY-Liste, position 2."
Ich weiß nicht, was falsch ist.
Ursprünglichen dynamischen Abfrage:
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'
Extrahiert Abfrage:
;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
Wenn ich entfernen Sie die zweite Ordnung von Linie, CASE WHEN 'ItemDescription'='ItemDescription' AND '0'= '1' THEN cteForPriceVen.ItemDescription END DESC
die Abfrage scheint zu arbeiten.
Bitte helfen Sie mir dies zu beheben.
'ItemDescription'='ItemDescription'
und'0'= '0'
wird immer wahr sein,'0'= '1'
wird immer falsch sein. Ich denke, Sie sollten etwas tun, wieItemDescription='ItemDescription'
statt.- Hier zwei Probleme. Ihre case-Ausdrücke sind Zeichenfolgen anstelle der Spalten. Auch, warum Sie den NOLOCK-Hinweis? Sie sind ok mit fehlenden und/oder doppelte Zeilen? blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere
Du musst angemeldet sein, um einen Kommentar abzugeben.
In der zweiten Zeile:
Entspricht
NULL
. Sie können nicht etwas bestellen, indemNULL
.Bearbeiten
Wenn diese Anweisung erzeugt durch eine dynamische Abfrage, was Sie tun müssen, ist befestigen Sie die Art der Erstellung der dynamischen Abfrage:
Diese Zeile einfach keinen Sinn:
sollte es nicht so etwas wie: