Home » Developer & Programmer » Reports & Discoverer » ORDER by DECODE
ORDER by DECODE [message #392066] Mon, 16 March 2009 04:34 Go to next message
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 #392070 is a reply to message #392066] Mon, 16 March 2009 04:47 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
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>
Re: ORDER by DECODE [message #392094 is a reply to message #392070] Mon, 16 March 2009 05:58 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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
ORDER BY <number column>


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 Go to previous messageGo to next message
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
ORDER BY <number column>


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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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 Go to previous messageGo to next message
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 #392235 is a reply to message #392228] Tue, 17 March 2009 02:14 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
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).
Re: ORDER by DECODE [message #392254 is a reply to message #392235] Tue, 17 March 2009 03:21 Go to previous messageGo to next message
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 #392274 is a reply to message #392254] Tue, 17 March 2009 04:44 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If works as expected on my Oracle 10g database and Reports Developer 10g.

./fa/5909/0/
Re: ORDER by DECODE [message #392533 is a reply to message #392066] Wed, 18 March 2009 05:29 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Having got my hands on a brand new crystal ball

./fa/1659/0/

I see in the swirling mists the possibility that you've got the break order property set wrong on one or more items in one or more groups in the Data Model of your report.
Re: ORDER by DECODE [message #421712 is a reply to message #392533] Thu, 10 September 2009 00:09 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
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 #421864 is a reply to message #392066] Fri, 11 September 2009 04:22 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
What's the datatype of the column you're sorting on?
Re: ORDER by DECODE [message #422161 is a reply to message #392066] Mon, 14 September 2009 21:13 Go to previous messageGo to next message
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
Member
Sorry for late reply as i was on leave

Data Type of the column is varchar2

Regards,
Kashif
Re: ORDER by DECODE [message #422188 is a reply to message #421826] Tue, 15 September 2009 00:48 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
sirfkashif
Group 'Test' has no break column

Well, perhaps you could use a trick here: as the lowest child group doesn't need "break column", create a "Group Above" query whose data layout looks like this:

./fa/6777/0/

A dummy column is here just in order to make sure that you wouldn't need to create a break column in the "G1" group.

"&lex_order_by" is a lexical parameter which will be created at runtime (in the After Parameter Form trigger, for example), depending on your user's wishes, such as "order by deptno, ename" etc.
Re: ORDER by DECODE [message #422626 is a reply to message #422188] Thu, 17 September 2009 23:56 Go to previous messageGo to next message
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
Member
Thanks i will check and reply.

Regards,
Kashif
Re: ORDER by DECODE [message #422844 is a reply to message #392066] Sun, 20 September 2009 09:21 Go to previous messageGo to next message
shanmugarajanbu
Messages: 18
Registered: August 2009
Location: chennaI
Junior Member

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.



Re: ORDER by DECODE [message #423399 is a reply to message #392066] Thu, 24 September 2009 00:16 Go to previous message
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
Member
It Worked like a charm Razz

Thanks to all for such a support.

Regards,
Kashif
Previous Topic: Four different columns as one column
Next Topic: Sequential Numero per row in Report 10g
Goto Forum:
  


Current Time: Thu Dec 08 22:10:30 CST 2016

Total time taken to generate the page: 0.05985 seconds