Andrew Andrew - 6 months ago 24
SQL Question

Concatenate in order by using decode in Oracle

I have below query in which i want to Concatenate the combine column result of

FC,FM,MS
and sort based on color such that
'R'
should come first then
'Y'
and then
'G'
. I tried below query but the sorting doesn't work in proper order.

select bp.NAME as BIZNAME,
max(case when bbp.BID = 10001 then os.OBJECT_STATUS end) FC,
max(case when bbp.BID = 10002 then os.OBJECT_STATUS end) FM,
max(case when bbp.BID = 10007 then os.OBJECT_STATUS end) MS
from PR_BP bbp
JOIN PR_BP_BPP bp ON bp.BP_ID = bbp.BP_ID
JOIN BRD b ON bbp.BID = b.BID
JOIN OS_VIEW os ON bbp.BP_ID = os.OBJECT_ID
where os.OBJECT_TYPE = 'PR_BP_BPP' AND bbp.BP_ACTIVE = 'Y'
group by bp.NAME
ORDER BY decode(FC ||FM|| MS,'R',1,'Y',2,'G',3)

Answer

Just use CASE EXPRESSION , I prefer it as it is easier to read:

ORDER BY CASE WHEN FC = 'R' THEN 1
              WHEN FC = 'Y' THEN 2
              WHEN FC = 'G' THEN 3
         END,
         CASE WHEN FM = 'R' THEN 1
              WHEN FM = 'Y' THEN 2
              WHEN FM = 'G' THEN 3
         END,
         CASE WHEN MS = 'R' THEN 1
              WHEN MS = 'Y' THEN 2
              WHEN MS = 'G' THEN 3
         END

That is if I understood what you want to do, I'm not I followed the logic you intended to do, I think what it does is checking when FC||FM||MS is equal to R/Y/G , which I believe is not

EDIT: If you want to order first by if one of them is 'R', then if one of the columns is 'Y' ...

ORDER BY CASE WHEN 'R' IN(FC,FM,MS) THEN 1
              ELSE 2
         END,
         CASE WHEN 'Y' IN(FC,FM,MS) THEN 1
              ELSE 2
         END,
         CASE WHEN 'G' IN(FC,FM,MS) THEN 1
              ELSE 2
         END
Comments