Sortierung im Layouter via Query

Rolf Mettler

Hallo Philipp

Die Sortierung im swissLayouter wir ja über die Query gesteuert (oder by). Leider stimmt mir die Sortierung auf meinen Packlisten nicht. Vermutlich weil das Feld alphanumerisch ist. Sobald ich den cast bereits im SELECT einbaue läuft die Querie nicht mehr. Wie ist das zu lösen, dass die Sortierung stimmt?
Ist: 1, 10, 100, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21, ...
Soll: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, ... 20, 21, ... 99, 100, 101, ...

Hier die Query:
select T0.ItemCode, T0.Dscription, T0.DocEntry, T0.LineNum, T0.wght1unit
INTO #Rows
from RDR1 T0
inner join ORDR T1 on T0.DocEntry = T1.DocEntry
inner join OCRD t2 on t1.CardCode=t2.cardcode
Where t1.U_DelCode='[%DelCode]' And t1.U_Versand='[%Versand]' AND t2.Country='[%Country]'

SELECT T0.U_DocEntry, T0.U_LineNum, T0.U_LotNum, SUM(T0.U_LotQty) AS U_LotQty
into #lots
from [@COR_DIA_AKM_LotAT] T0
INNER JOIN #Rows T1 ON T0.U_DocEntry = T1.DocEntry AND T0.U_LineNum = T1.LineNum
GROUP BY T0.U_DocEntry, T0.U_LineNum, T0.U_LotNum

SELECT T3.ItemCode, T3.Dscription, (IsNull(T6.sWeight1,0)*IsNull(T5.WightInMG,0)/1000000) * CAST(T2.U_Quantity As Decimal(16,3)) As WeightItem,T2.U_DocEntry, T3.LineNum,T0.U_PkgNbr, T1.PkgType, CASE WHEN T0.U_Weight IS NULL OR Cast(T0.U_Weight As Decimal(16,2)) = 0 THEN IsNull(T6.sWeight1,0)*IsNull(T5.WightInMG,0)/1000000 ELSE T0.U_Weight END AS Weight, T2.U_Quantity, T4.U_LotNum,T4.U_LotQty
INTO #Packs
FROM [@COR_PKG_PACKAGE] T0
LEFT OUTER JOIN OPKG T1 ON T0.U_pkgCode = T1.PkgCode
INNER JOIN [@COR_PKG_QUANTITY] T2 ON T0.Code = T2.U_Pack
INNER JOIN #Rows T3 ON T2.U_DocEntry = T3.DocEntry AND T2.U_LineNum = T3.LineNum
INNER JOIN OITM T6 ON T3.ItemCode = T6.ItemCode
INNER JOIN #lots T4 ON T2.U_DocEntry = T4.U_DocEntry AND T2.U_lineNum = T4.U_LineNum AND T2.U_LotNum = T4.U_LotNum
LEFT OUTER JOIN OWGT T5 ON T3.Wght1Unit=T5.UnitCode
WHERE T2.U_Quantity > 0 AND T0.U_DeletedF<>'Y' AND T2.U_LotNum is not null AND T2.U_DocEntry = T3.DocEntry
ORDER BY CAST(T0.U_PkgNbr As Integer),T3.ItemCode, T4.U_LotNum

SELECT DISTINCT * FROM #Packs T0

ORDER BY T0.U_PkgNbr

Danke für deine Hilfe

Rolf

Philipp Knecht

Hi Rolf

Es wird nur die letzte Order By - Klausel berücksichtigt.

[quote]
...
...
SELECT DISTINCT * FROM #Packs T0

ORDER BY CAST(T0.U_PkgNbr AS INTEGER)

hth