ORDER BY - QUESTION

[Analyst2004]

I take 4 pair of inputs from user in a SQL query with '&Variable'. The input is part of DECODE statement(in the Where -Clause) and it pulls the data from the Table as per the DECODE condition.

However, my result set is not in the order of the user-input. I want to sort it as per the data user provided while executing the query.
SELECT *
FROM XYZ
WHERE
field1 = DECODE(( SELECT COUNT(*) FROM XYZ A WHERE EXISTS (SELECT filed1 FROM XYZ B
WHERE field1 = '&n1' )),0,'&a1','&&n1')
OR
field1 = DECODE(( SELECT COUNT(*) FROM XYZ A WHERE EXISTS (SELECT filed1 FROM XYZ B
WHERE field1 = '&m' )),0,'&b','&&m')


I am running this in TOAD.
Any Clues on How to get it in ORDER BY as per user input ?

 

[Turkbear]

Hi, Have you tried :

SELECT *
FROM XYZ
WHERE
field1   = DECODE(( SELECT COUNT(*) FROM XYZ A WHERE EXISTS (SELECT filed1 FROM XYZ B
WHERE field1 = '&n1' )),0,'&a1','&&n1')
OR
field1   = DECODE(( SELECT COUNT(*) FROM XYZ A WHERE EXISTS (SELECT filed1 FROM XYZ B
WHERE field1 = '&m' )),0,'&b','&&m')
-- New Stuff - substitute your variables here 
ORDER BY &whatever,&whatever

Just a thought..

 

[Analyst2004]

Thanks Turkbear for the suggestion,
but with each DECODE stmt, you notice a new variable, (which however represents the same column in the table).
BUT - since I want the user inputS in same SQL stmt, I have to use multiple input variables, SO the QUESTION still remains unanswered.

more suggestions ??

 

[sem]

Can you explain the meaning of " ORDER BY as per user input"? In fact the order of conditions really evaluated by database engine may differ from that specified in query.
If you need to display results from "partial" filters, you may use UNION ALL instead of OR condition. But again, what do you expect when both conditions are true?


SELECT XYZ.*, 0 ORD
FROM XYZ
WHERE
field1 = DECODE(( SELECT COUNT(*) FROM XYZ A WHERE EXISTS (SELECT filed1 FROM XYZ B
WHERE field1 = '&n1' )),0,'&a1','&&n1')
UNION ALL
SELECT XYZ.*, 1 ORD
FROM XYZ
WHERE
field1 = DECODE(( SELECT COUNT(*) FROM XYZ A WHERE EXISTS (SELECT filed1 FROM XYZ B
WHERE field1 = '&m' )),0,'&b','&&m')
ORDER BY ORD

Regards, Dima

http://seminihin.narod.ru

 

[Analyst2004]

Order by Input - Here means that I want to arrange the rows as per the data supplied by the user.
I just want the output to be just as the user entered the input values, and not sort it any default order.
I cannot use union all as I have multiple OR-conditions and Union will make the stmt too long.