Home » SQL & PL/SQL » SQL & PL/SQL » SQL order by Issue (Oracle 10g 2)
SQL order by Issue [message #400863] Thu, 30 April 2009 02:03 Go to next message
rohit3312@gmail.com
Messages: 7
Registered: February 2009
Junior Member
This issue is related to about an ORDER BY clause.

Here, now the ORDER BY clause is selected as a separate column using the DECODE() function.

Let's say sortby = 'memberCount' in this case, I passed it as first argument in decode(); memberCount is a COLUMN in the grptest table.

select distinct gl.group_id,
decode('memberCount', 'name', gl.group_name_key,'description',
gl.group_description_key, 'memberCount', gl.member_count) as p_sortby,
gl.group_name,
gl.group_description,
gl.status_code,
gl.member_count,
(select grpp.group_name
from grptest_relationship grel join grptest grpp
on grel.parent_group_id = grpp.group_id
where grel.child_group_id = gl.group_id) as parent_group_name,
gl.group_name_key,
gl.group_description_key
from grptest gl
where gl.group_org_id = '3909'
and (gl.group_name_key like '%' || 'GROUP' || '%')
Order by 2;

It doesn't work.

But if I pass 'name' as first argument in decode above, it works. That's my original issue about why it doesn't apply on memberCount.
Re: SQL order by Issue [message #400867 is a reply to message #400863] Thu, 30 April 2009 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not multipost your question but please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: SQL order by Issue [message #400868 is a reply to message #400863] Thu, 30 April 2009 02:19 Go to previous messageGo to next message
rohit3312@gmail.com
Messages: 7
Registered: February 2009
Junior Member
select distinct gl.group_id,
decode('memberCount', 'name', gl.group_name_key,'description', gl.group_description_key, 'memberCount', gl.member_count) as p_sortby,
gl.group_name,
gl.group_description,
gl.status_code,
gl.member_count,
(select grpp.group_name
from grptest_relationship grel join grptest grpp
on grel.parent_group_id =
grpp.group_id
where grel.child_group_id =
gl.group_id)
as parent_group_name,
gl.group_name_key,
gl.group_description_key
from grptest gl
where gl.group_org_id = '3909'
and ( gl.group_name_key like
'%' || 'GROUP' || '%'
)
Order by 2;

Re: SQL order by Issue [message #400872 is a reply to message #400868] Thu, 30 April 2009 02:40 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I don't have access to Oracle to test this. Can you check if works for you?

order by decode(gl.memberCount, 'name', gl.group_name_key,'description', gl.group_description_key, 'memberCount', gl.member_count)

[Updated on: Thu, 30 April 2009 02:44]

Report message to a moderator

Re: SQL order by Issue [message #400873 is a reply to message #400863] Thu, 30 April 2009 02:50 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
rohit3312@gmail.com wrote on Thu, 30 April 2009 09:03
This issue is related to about an ORDER BY clause.

Here, now the ORDER BY clause is selected as a separate column using the DECODE() function.

So it is rather DECODE function "issue". What about having a look into Oracle documentation, available e.g. online on http://tahiti.oracle.com/.
DECODE function is described in SQL Reference; for 10gR2 it is located here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions040.htm#i1017437
Quote:
Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2.

Assuming that MEMBERCOUNT is probably a NUMBER, if it is placed as the first result, then all other results are converted to numbers. This probably fails on non-numeric VARCHAR2 column values.

I am not aware about the column having "dynamic" type. So, even when starting with NAME, ordering by MEMBERCOUNT is done using string comparison rules (so '2' > '10').

The correct way is to explicitly convert that numbers into strings having the same order as numbers (e.g. left padding with sufficient amount of zeros). Then, even your "issue" with MEMBERCOUNT would be solved.

By the way,
Quote:
It doesn't work.
is not known Oracle behaviour.
Re: SQL order by Issue [message #400874 is a reply to message #400873] Thu, 30 April 2009 03:42 Go to previous messageGo to next message
rohit3312@gmail.com
Messages: 7
Registered: February 2009
Junior Member
HI all,

Thanks a lot for the inputs and suggestions.But,still i am confused on how could we handle this.

MEMBERCOUNT is a numeric field .if it is placed as the first result, then all other results are converted to numbers. This probably fails on non-numeric VARCHAR2 column values.

I am not aware about the column having "dynamic" type. So, even when starting with NAME, ordering by MEMBERCOUNT is done using string comparison rules (so '2' > '10').

The correct way is to explicitly convert that numbers into strings having the same order as numbers (e.g. left padding with sufficient amount of zeros). Then, even your "issue" with MEMBERCOUNT would be solved.

How do i do this in my query?can you please help
Re: SQL order by Issue [message #400898 is a reply to message #400874] Thu, 30 April 2009 05:31 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Can you firstly precise, what your "issue" is? "It doesn't work." is not valid Oracle error. So, is it the fact, that numbers are sorted with string comparison rules, or the query fails with error, or something totally else? Am I supposed to decipher it? Also posting quotes without any distinguishing makes your latest post nearly non-understandable.
rohit3312@gmail.com wrote on Thu, 30 April 2009 10:42
How do i do this in my query?can you please help

Use TO_CHAR with proper (wide enough left padded with zeros) format mask to convert MEMBERCOUNT to string. This is basic SQL dependent on exact column definition (you did not provide), so read SQL reference book for more details.
Previous Topic: how oracle update query works internaly
Next Topic: search by like operator
Goto Forum:
  


Current Time: Fri Dec 09 23:26:21 CST 2016

Total time taken to generate the page: 0.12405 seconds