| ORDER by DECODE [message #392066] |
Mon, 16 March 2009 04:34  |
Maverick27
Messages: 84
Registered: October 2008
|
Member |
|
|
Somebody pls. advise why the following ORDER By statement is not doing what's it's supposed to do in Oracle Reports.
ORDER BY DECODE(mg.vc_group_desc,'Accessories',1,2) ASC;
Instead of ordering by 'Accessories' first, it is ordering by 'Lighting'
|
|
|
|
|
|
| Re: ORDER by DECODE [message #392094 is a reply to message #392070] |
Mon, 16 March 2009 05:58  |
Maverick27
Messages: 84
Registered: October 2008
|
Member |
|
|
| Littlefoot wrote on Mon, 16 March 2009 04:47 | If that's the case, why DECODE? ORDER BY would naturally put 'Accessories' before 'Lighting':SQL> with test as
2 (select 'Lighting' group_desc from dual
3 union
4 select 'Accessories' from dual
5 )
6 select group_desc
7 from test
8 order by group_desc;
GROUP_DESC
-----------
Accessories
Lighting
SQL>
|
i tried this that's y i used DECODE function
here's my SQL Statment:
note that SQL starts with Group code
SELECT DISTINCT mg.vc_group_code,
mg.vc_group_desc Group_Desc,
msg.vc_sub_group_code Sub_Group_Code,
msg.vc_sub_group_desc Sub_Group_Desc,
CASE
WHEN msgp.vc_sub_group_prd_desc = 'N/A' THEN '-'
WHEN msgp.vc_sub_group_prd_desc is NOT NULL AND msgp.vc_field1 is NULL THEN msgp.vc_sub_group_prd_desc
WHEN msgp.vc_sub_group_prd_desc is NOT NULL AND msgp.vc_field1 is NOT NULL THEN msgp.vc_sub_group_prd_desc ||' '||'-'||' '||msgp.vc_field1
END Product_descn
FROM mst_group mg,
mst_sub_group msg,
mst_sub_group_product msgp,
mst_product_type mpt,
mst_item mi
WHERE mg.vc_comp_code = msg.vc_comp_code AND
mg.vc_group_code = msg.vc_group_code AND
msg.vc_comp_code = msgp.vc_comp_code AND
msg.vc_sub_group_code = msgp.vc_sub_group_code AND
msgp.vc_comp_code = mpt.vc_comp_code AND
msgp.vc_sub_group_prd_code = mpt.vc_sub_group_prd_code AND
mpt.vc_comp_code = mi.vc_comp_code AND
mpt.vc_product_type_code = mi.vc_product_type_code AND
mpt.vc_sub_group_prd_code = mi.vc_sub_group_prd_code(+) AND
msg.vc_comp_code = '01'
|
|
|
|
| Re: ORDER by DECODE [message #392103 is a reply to message #392066] |
Mon, 16 March 2009 06:51 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Are you trying to order by vc_group_code if vc_group_desc = 'Accessories' and order by vc_group_desc otherwise?
If so your DECODE is wrong.
ORDER BY <function that returns a number>
Is not equivalent to
ORDER BY <number to indicate column from select list>
it IS equivalent to
What you should have done is something like this:
ORDER BY DECODE(mg.vc_group_desc,'Accessories', mg.vc_group_code , mg.vc_group_desc) ASC;
Also - I suspect your outer-join is wrong. Normally you specify the outer join on every column of the table you're doing the outer-join to.
EDIT: typo
[Updated on: Mon, 16 March 2009 06:52] Report message to a moderator |
|
|
|
| Re: ORDER by DECODE [message #392104 is a reply to message #392103] |
Mon, 16 March 2009 07:00 |
Maverick27
Messages: 84
Registered: October 2008
|
Member |
|
|
| cookiemonster wrote on Mon, 16 March 2009 06:51 | Are you trying to order by vc_group_code if vc_group_desc = 'Accessories' and order by vc_group_desc otherwise?
If so your DECODE is wrong.
ORDER BY <function that returns a number>
Is not equivalent to
ORDER BY <number to indicate column from select list>
it IS equivalent to
What you should have done is something like this:
ORDER BY DECODE(mg.vc_group_desc,'Accessories', mg.vc_group_code , mg.vc_group_desc) ASC;
Also - I suspect your outer-join is wrong. Normally you specify the outer join on every column of the table you're doing the outer-join to.
EDIT: typo
|
Nope - still not working...
|
|
|
|
| Re: ORDER by DECODE [message #392115 is a reply to message #392066] |
Mon, 16 March 2009 08:22 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
*sigh*
considering that my post was a rough guide to a possible solution based on a guess about what you're trying to do that's hardly surprising.
You do realise that haven't at any point told us exactly what order you want the data in?
Your first post consists of an ORDER BY that doesn't do what you think it does and insufficient information work out what you think it does.
Your 2nd post doesn't actually mention order at all.
I guessed what you think the order by does at the top of my first post but you haven't bothered to confirm if I'm right or not.
If you want further help then you really need to explain in English what order you want.
You might also need to provide a test case if your requirement isn't straight forward.
|
|
|
|
| Re: ORDER by DECODE [message #392117 is a reply to message #392066] |
Mon, 16 March 2009 08:30 |
Maverick27
Messages: 84
Registered: October 2008
|
Member |
|
|
Sorry CookieMonster,
U sound really p*ssed off...
I want to ORDER Group DESC - alphabeticlly - that's all.
Simple ORDER BY does not work, so i used DECODE, & it does'nt work for either.
What do u think the problem is with SQL statement - see attachment ??
ORDER BY CASE statement...
Mave
|
|
|
|
| Re: ORDER by DECODE [message #392146 is a reply to message #392066] |
Mon, 16 March 2009 10:50 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I'm not really p*ssed off - exasperated would be a better term.
After all I'd gone to a certain amount of effort to try and work out what your problem was and suggest a possible solution,
and your response was far too brief to get us anywhere useful.
Unfortunately your requirements are still not completely clear
| Quote: |
I want to ORDER Group DESC - alphabeticlly - that's all.
|
That sentance could mean one of two things.
1) You want to order by the group column in descending order (since DESC is the oracle shorthand for descending)
So:
ORDER BY mg.vc_group_desc DESC
2) You want to order by the group column in ascending order
(since desc is also part of your column name - but since you didn't put the full column name it's hard to say for sure)
So:
ORDER BY mg.vc_group_desc
I personally discounted either of those possibilities because you can't replace them with CASE or DECODE.
If one of those possibilities is what you're after and you think it doesn't work then you really need to show us exactly what
you tried and what result you got. Because if you think a simple order by doesn't work in oracle then either
A) you've got a REALLY broken version of oracle (which I doubt) in which case you'd be better off using metalink.
or
B) there's something else going on that you haven't told us about.
If your requirement is more complex then I'm still baffled as to what it is.
As for the query in your last post:
1) please don't put queries in images. A lot of users of this site can't download them
just copy and paste the query and the result and put it in code tags.
2) It looks ok at a glance - to work out what the issue is I'd need to know your oracle version
as well as having create table statements for all your tables so I could replicate the query.
3) While it looks syntatically correct it seems pointless.
You're saying put the record(s) with mg.vc_group_desc = 'Acessories' first,
put the record(s) with mg.vc_group_desc = 'Torches' after those,
then any other records will appear in a random order.
|
|
|
|
| Re: ORDER by DECODE [message #392175 is a reply to message #392117] |
Mon, 16 March 2009 16:52 |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Are you looking for something like this?SQL> SELECT job, ename
2 FROM EMP
3 ORDER BY DECODE(job, 'MANAGER', 1,
4 'ANALYST', 2,
5 'CLERK', 3,
6 'SALESMAN', 4
7 );
JOB ENAME
--------- ----------
MANAGER JONES
MANAGER BLAKE
ANALYST SCOTT
ANALYST FORD
CLERK SMITH
CLERK ADAMS
CLERK JAMES
SALESMAN ALLEN
SALESMAN TURNER
SALESMAN MARTIN
KING
MILLER
WARD
CLARK
14 rows selected.
SQL>
If so, could you post sample data (a few values you'd like to sort and the way you'd like to sort them)? This includes CREATE TABLE and INSERT INTO statements (so that we could easily reproduce it).
|
|
|
|
| Re: ORDER by DECODE [message #392228 is a reply to message #392175] |
Tue, 17 March 2009 01:54 |
Maverick27
Messages: 84
Registered: October 2008
|
Member |
|
|
| Littlefoot wrote on Mon, 16 March 2009 16:52 | Are you looking for something like this?SQL> SELECT job, ename
2 FROM EMP
3 ORDER BY DECODE(job, 'MANAGER', 1,
4 'ANALYST', 2,
5 'CLERK', 3,
6 'SALESMAN', 4
7 );
JOB ENAME
--------- ----------
MANAGER JONES
MANAGER BLAKE
ANALYST SCOTT
ANALYST FORD
CLERK SMITH
CLERK ADAMS
CLERK JAMES
SALESMAN ALLEN
SALESMAN TURNER
SALESMAN MARTIN
KING
MILLER
WARD
CLARK
14 rows selected.
SQL>
If so, could you post sample data (a few values you'd like to sort and the way you'd like to sort them)? This includes CREATE TABLE and INSERT INTO statements (so that we could easily reproduce it).
|
Thanks Folks for taking a keen interest in my prob., simple as it is to achieve, i'm still not able to get the damn thing to work.
See attachment of my latest attempt. Result is showing 'Lighting' on top !
|
|
|
|
|
|
| Re: ORDER by DECODE [message #392254 is a reply to message #392235] |
Tue, 17 March 2009 03:21 |
Maverick27
Messages: 84
Registered: October 2008
|
Member |
|
|
| Littlefoot wrote on Tue, 17 March 2009 02:14 | It appears that you have a problem, but you are trying so hard not to solve it.
Which part of my previous post saying| LF | If so, could you post sample data (a few values you'd like to sort and the way you'd like to sort them)? This includes CREATE TABLE and INSERT INTO statements (so that we could easily reproduce it).
|
did you not understand?
Besides, you were already told not to post images - they are useless (in this case, most of the time).
|
Hi:
The prob. is with Oracle Report Writer, the SQL (ORDER BY DECODE) gives desired results in SQL Plus & TOAD.
This is the reason y i posted in this thread for Reports.
Any thougtht y it works in TOAD & not in Report Writer ?!
Mave
|
|
|
|
|
|
|
|
| Re: ORDER by DECODE [message #421712 is a reply to message #392533] |
Thu, 10 September 2009 00:09 |
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
|
Member |
|
|
@Maverick27
| Quote: | The prob. is with Oracle Report Writer, the SQL (ORDER BY DECODE) gives desired results in SQL Plus & TOAD.
|
I am facing exact same problem thats why i had to revoke an old thread.
@cookiemonster
To your question yes i have set the break order of a column in a matrix report and i know that property is restricting my desired output but isn't it a required property because otherwise it gives
Rep-1259:- Group 'Test' has no break column
Any help will be appreciated.
Regards,
Kashif
|
|
|
|
| Re: ORDER by DECODE [message #421742 is a reply to message #392066] |
Thu, 10 September 2009 04:07 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Break order is required on at least one column in any group above the bottom level.
Why don't you tell us what it is you're trying to do and what the exact problem is and we'll see if we can help.
|
|
|
|
| Re: ORDER by DECODE [message #421826 is a reply to message #421742] |
Thu, 10 September 2009 20:59 |
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
|
Member |
|
|
I have a column in database on which a Matrix Report Column is displaying value
now the sort order is by default ascending.
Now the End user wants to modify the report output by displaying the report output according to his choice,
which i achieved by using order by decode query and output was as he desired on pl/sql developer,
now when i used that query on existing report, output was unaffected because
i am unable to change the break order property which is forcing the output to sort in ascending order otherwise it gives
Rep-1259:- Group 'Test' has no break column
Regards,
Kashif
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ORDER by DECODE [message #422844 is a reply to message #392066] |
Sun, 20 September 2009 09:21 |
|
|
hi,
write the code in select clause as
DECODE(mg.vc_group_desc,'Accessories',1,2) dummy_order
then ORDER BY clause as dummy_order
and one more thing this dummy_order column should be in 1st column in data model then only order will come correctly.
Regards,
Shanmugaraj G.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
