i know people use ROW_NUMBER() function to do the pagination but my below two query is bit complex. so i need suggestion like how to use pagination there ?
i used ROW_NUMBER() OVER(ORDER BY IsNull(A.OEReference, B.OEReference) ASC) as Line in one but not sure am i right or wrong.
IF IsNull(@GroupID,'') = ''
SELECT IsNull(PartGroupName, 'UnMapped') AS PartGroupName,
CASE IsNull(PartGroupName, '')
WHEN '' THEN ''
ELSE IsNull(IsNull(K.GroupID, IsNull(C.PartGroupID,'')),'')
END AS PartGroupID,
IsNull(A.OEReference, B.OEReference) AS OEReference,
IsNull(SaleDone,0) AS [SALE/OUR],
IsNull(WarrantyDone, 0) AS [WARRANTY/WARRANTYOUR]
FROM @Sale A FULL OUTER JOIN @Warranty B ON A.OEReference = B.OEReference
LEFT OUTER JOIN BBAPart K ON IsNull(A.OEReference,B.OEReference) = K.Stock_code
LEFT OUTER JOIN BBAPartGroup C ON K.GroupID=C.PartGroupID AND C.IsDeleted = 0
ORDER BY IsNull(PartGroupName, 'UnMapped')
ELSE
SELECT IsNull(PartGroupName, 'UnMapped') AS PartGroupName,
CASE IsNull(PartGroupName, '')
WHEN '' THEN ''
ELSE IsNull(IsNull(K.GroupID, IsNull(C.PartGroupID,'')),'')
END AS PartGroupID,
IsNull(A.OEReference, B.OEReference) AS OEReference,
IsNull(SaleDone,0) AS [SALE/OUR],
IsNull(WarrantyDone, 0) AS [WARRANTY/WARRANTYOUR]
FROM @Sale A FULL OUTER JOIN @Warranty B ON A.OEReference = B.OEReference
LEFT OUTER JOIN BBAPart K ON IsNull(A.OEReference,B.OEReference) = K.Stock_code
LEFT OUTER JOIN BBAPartGroup C ON K.GroupID=C.PartGroupID AND C.IsDeleted = 0
WHERE PartGroupID IN (SELECT Data FROM dbo.stringtotable(@GroupID, ',') )
ORDER BY IsNull(PartGroupName, 'UnMapped')
END
Aucun commentaire:
Enregistrer un commentaire