niestety od dłuższego czasu męczę się z prostym zapytaniem SQL, wszystko działa oprócz sortowania.
kod
DECLARE skip INT; SET skip = (pPageCurrent-1)*pPageCount; SELECT SQL_CALC_FOUND_ROWS l.OrderId, l.Amount, l.Date AS AuctionDate, l.Reference, l.ServiceId, l.VisitorId, l.ClickId, l.StatusId, l.StatusDate, l.ProgramId, l.PartnerId, l.DenyReason, mp.ProgramName, pc.Date AS ClickDate, afs.SiteName FROM PaymentOrders AS l INNER JOIN AffiliateData AS afd ON afd.UserShortGuid = l.PartnerId AND (pAffiliateId IS NULL OR afd.UserShortGuid = pAffiliateId) INNER JOIN PaymentOrdersClicks AS pc ON pc.ClickId = l.ClickId INNER JOIN MerchantPrograms AS mp ON mp.ProgramId = l.ProgramId LEFT JOIN AffiliateSites AS afs ON (afs.SiteShortGuid = l.AffiliateSiteId) WHERE (pSearchText IS NULL OR l.Reference LIKE CONCAT('%', pSearchText, '%') OR pc.IpAdress LIKE CONCAT('%', pSearchText, '%') OR mp.ProgramName LIKE CONCAT('%', pSearchText, '%')) AND (pServiceId IS NULL OR mp.ServiceId = pServiceId) AND (pProgramId IS NULL OR l.ProgramId = pProgramId) AND (pStatusId IS NULL OR FIND_IN_SET(l.StatusId, pStatusId)) AND (l.Date BETWEEN pDateFrom AND pDateTo) ORDER BY CASE pSortOrder = FALSE WHEN pSortColumn='amount' THEN l.Amount WHEN pSortColumn='statusid' THEN l.StatusId WHEN pSortColumn='auctiondate' THEN l.Date WHEN pSortColumn='sitename' THEN afs.SiteName WHEN pSortColumn='programname' THEN mp.ProgramName WHEN pSortColumn='clickdate' THEN pc.Date WHEN pSortColumn='statusdate' THEN l.StatusDate END, CASE pSortOrder = TRUE WHEN pSortColumn='amount' THEN l.Amount WHEN pSortColumn='statusid' THEN l.StatusId WHEN pSortColumn='auctiondate' THEN l.Date WHEN pSortColumn='sitename' THEN afs.SiteName WHEN pSortColumn='programname' THEN mp.ProgramName WHEN pSortColumn='clickdate' THEN pc.Date WHEN pSortColumn='statusdate' THEN l.StatusDate END DESC LIMIT PPAGECOUNT OFFSET SKIP; SELECT FOUND_ROWS() AS Count;
wynik zwraca poprawnie, jednak sortowanie po kolumnach nie działa, w czym może być problem?
dziękuje za pomoc