RanjeetRain
asked on
Custom order by
This might be easy for you, but not for me. I have a query like:
SELECT s.section_name, F.PROMPT
FROM knta_sections s, KNTA_PARAMETER_SET_FIELDS F
WHERE F.Parameter_Set_Context_id
AND f.section_id = s.section_id
AND F.PROMPT IN ('Project Lead:', 'Budget:', 'Detailed CBA:', 'TG2 Revised CBA:')
This returns me the desired output. However, the sorting order isn't great. I would like the output in the same order as the input list supplied to PROMPT. i.e.
'Project Lead:', 'Budget:', 'Detailed CBA:', 'TG2 Revised CBA:'
Can you help? Thanks in advance.
SELECT s.section_name, F.PROMPT
FROM knta_sections s, KNTA_PARAMETER_SET_FIELDS F
WHERE F.Parameter_Set_Context_id
AND f.section_id = s.section_id
AND F.PROMPT IN ('Project Lead:', 'Budget:', 'Detailed CBA:', 'TG2 Revised CBA:')
This returns me the desired output. However, the sorting order isn't great. I would like the output in the same order as the input list supplied to PROMPT. i.e.
'Project Lead:', 'Budget:', 'Detailed CBA:', 'TG2 Revised CBA:'
Can you help? Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good idea, actonwang. Thanks for the same. However, its seems too much of hassle to populate values in a temporary table and all. I would like to explore the possibility of getting this done by manually passing the values in the list in SQL itself, or as a result of a subquery. Thanks again.
no problem.
Actually temp table is not that difficult if your sql statement is inside an transaction. It will look more natural or error prone than decode.
use:
create global temporary table temp (
value varchar2(10),
order number(5)
);
Actually temp table is not that difficult if your sql statement is inside an transaction. It will look more natural or error prone than decode.
use:
create global temporary table temp (
value varchar2(10),
order number(5)
);
i am wondering how the order is generated? just by the order you type them in?
ASKER
Yes, that's what I want end result to look like.
i'd use temp table. Draft a script to do this.
PL/SQL also can do this but it is overkill.
If less values invovled, use decode.
PL/SQL also can do this but it is overkill.
If less values invovled, use decode.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good ideas. So far MikeOM's idea looks the best. Let me see if someone can better that. Otherwise I'll close. Thanks for the help.
ASKER
Sorry about keeping this open so long. Thanks everyone for your suggestions.
ASKER