Home » SQL & PL/SQL » SQL & PL/SQL » SQL,PLSQL mutiple order by with decode issue (oracle 10g 2)
SQL,PLSQL mutiple order by with decode issue [message #401135] Fri, 01 May 2009 14:53 Go to next message
rohit3312@gmail.com
Messages: 7
Registered: February 2009
Junior Member
Thanks a lot.

When i run the below select with gl.group_name_key LIKE '%GROUP%' --It gives me the sorted data for records with GROUP.

**Now-lets replace the 'parentGroupName' in the DECODE Function below with 'description'and run the SQL--the sorting doesnot work now.


But it doesnot sort the data for the description column selected.



Please help me with writing this scenario-and i dont want to go with procedures
because i have to change my entire logic again. Waiting for your help. Thanks again a lot.


SELECT *
    FROM (SELECT distinct gl.group_id,
                 gl.group_name,
                 gl.group_description,
                 gl.status_code,
                 gl.member_count,
                 (SELECT grpp.group_name
                      FROM test_group_relationship grel JOIN test_group 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   test_group AS gl
             WHERE  gl.group_org_id   = '3909'
               AND (gl.group_name_key LIKE '%GROUP%')
         ) AS data_set 
    ORDER BY UPPER(SUBSTR(group_name, 1, 1)),
             SUBSTR(group_name, 1, 1) DESC,
             UPPER(group_name),
             group_name DESC,
             group_name_key, 
             DECODE('parentGroupName',
                          'name',            group_name_key,
                          'description',     group_description_key,
                          'memberCount',     LPAD(member_count, 4),
                          'status',          LPAD(status_code, 4),
                          'parentGroupName', parent_group_name)
             NULLS FIRST;



CREATE TABLE TEST_GROUP
(
GROUP_ID VARCHAR2(30 BYTE),
GROUP_ORG_ID VARCHAR2(30 BYTE),
GROUP_NAME NVARCHAR2(250),
PARENT_GROUP_ID VARCHAR2(30 BYTE),
GROUP_DESCRIPTION NVARCHAR2(2000),
MEMBER_COUNT NUMBER,
IN_USE_FLAG CHAR(1 BYTE),
GROUP_NAME_KEY NVARCHAR2(250),
GROUP_DESCRIPTION_KEY NVARCHAR2(2000),
STATUS_CODE NUMBER
)

CREATE TABLE TEST_GROUP_RELATIONSHIP
(
PARENT_GROUP_ID VARCHAR2(50 BYTE),
CHILD_GROUP_ID VARCHAR2(50 BYTE),
UPDATED_DT DATE DEFAULT SYSDATE,
UPDATED_BY VARCHAR2(50 BYTE),
CREATED_DT DATE DEFAULT SYSDATE,
CREATED_BY VARCHAR2(50 BYTE)
)
In brief,our application runs the above select and it displays the SELECTED FIELDS.
now,when the user CLICKS on the HEADING of the DESCRIPTION column---then it should SORT BASED on the description field.
I am now confused and its best if i can do this with SQLs only.


[Updated on: Fri, 01 May 2009 15:00] by Moderator

Report message to a moderator

Re: SQL,PLSQL mutiple order by with decode issue [message #401138 is a reply to message #401135] Fri, 01 May 2009 15:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SELECT   *
FROM     (SELECT DISTINCT gl.group_id,
                          gl.group_name,
                          gl.group_description,
                          gl.status_code,
                          gl.member_count,
                          (SELECT grpp.group_name
                           FROM   test_group_relationship grel
                                  JOIN test_group 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   test_group AS gl
          WHERE  gl.group_org_id = '3909'
                 AND (gl.group_name_key LIKE '%GROUP%')) AS data_set
ORDER BY Upper(Substr(group_name,1,1)),
         Substr(group_name,1,1) DESC,
         Upper(group_name),
         group_name DESC,
         group_name_key,
         Decode('parentGroupName','name',group_name_key,
                                  'description',group_description_key,
                                  'memberCount',Lpad(member_count,4),
                                  'status',Lpad(status_code,4),
                                  'parentGroupName',parent_group_name) NULLS FIRST; 


You need to help us by following the Posting Guidelines as stated below.

http://www.orafaq.com/forum/t/88153/0/
Re: SQL,PLSQL mutiple order by with decode issue [message #401140 is a reply to message #401135] Fri, 01 May 2009 15:05 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Show us the output of both queries. And please do not just "say" what the other query is. I am confused by your naming of the columns, so I do not know what you mean by "description." Show us both queries and the uutput.
Re: SQL,PLSQL mutiple order by with decode issue [message #401153 is a reply to message #401138] Fri, 01 May 2009 18:13 Go to previous messageGo to next message
rohit3312@gmail.com
Messages: 7
Registered: February 2009
Junior Member
Please ignore the above SELECT..

I have put in details below.

My SELECT is :-

SELECT *
    FROM (SELECT distinct gl.group_id,
                 gl.group_name,
                 gl.group_description,
                 gl.status_code,
                 gl.member_count,
                 (SELECT grpp.group_name
                      FROM test_group_relationship grel JOIN test_group 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   test_group AS gl
             WHERE  gl.group_org_id   = '3909'
               AND (gl.group_name_key LIKE '%GROUP%')
         ) AS data_set 
    ORDER BY DECODE( :p_sortby,
                      'name',            'constant',
                      'description',     group_description,
                      'memberCount',     LPAD(member_count, 4),
                      'status',          LPAD(status_code, 4),
                      'parentGroupName', parent_group_name 
               ) NULLS FIRST,
         UPPER(SUBSTR(group_name, 1, 1)),
         SUBSTR(group_name, 1, 1) DESC,
         UPPER(group_name),
         group_name DESC,
         group_name_key





So if you set your sort key parameter to 'name', the first expression in the ORDER BY will be a constant, and you will just get the ordering as determined by the remaining expressions using group_name.

But if you set the sort key parameter to something else, it will first sort by the appropriate expression, then by the group_name expressions.

The issue is:-

Each of the arguments in decode have a particular SORT oder which
i need to write for it to work.

the p_sortby parameter that tells how you want the data sorted.

Sort order for the values in each column:=

All blanks first,then sorted by lowercase and then followed by upper case ok.


For example:- when :p_sortby =description then
the below sample data shows the way i want the data.

Description column:- Has Blanks first,then sorted by lowercase and then followed by upper case ok.

Sample data:-

GROUP_NAME	                                                            GROUP_DESCRIPTION
--------------------------------------------------------------------------  ----------------------------------------------
another add group test from matt	
Add group error flow 1 - (duplicate group name) - modified	
Group 33	
Test Group 1
Add group error flow 1 - (duplicate group name)	                               hello
Administrative Group	                                                     Administrative Group
Add Group Basic Flow	                                                    Some description that is required 


**I was also trying out ORDER BY with CASE statement.
/*The belowTHEN clause works if i just have only 1 argument .It doesnot work if i have all of them*/

/*It gives a keyword missing error*/


ORDER BY 
    CASE
        
		WHEN :p_sortby = 'name' THEN  UPPER(SUBSTR(group_name, 1, 1))
    --bSTR(group_name, 1, 1) DESC,UPPER(group_name), group_name_key, group_name DESC
        --WHEN :p_sortby = 'description' THEN     UPPER(SUBSTR(group_description, 1, 1)),SUBSTR(group_description, 1, 1) DESC,
                        --- UPPER(group_description),
                        ----group_description DESC,
                            ---group_description_key ,NULLS FIRST;                 
        WHEN :p_sortby ='memberCount' THEN   LPAD(member_count, 4)
          --EN :p_sortby = 'status' THEN    LPAD(status_code, 4),NULLS FIRST;
          --HEN :p_sortby =           'parentGroupName'THEN parent_group_name ,NULLS FIRST ;
           
         
         END
         

What is the correct SYNTAX for ORDER by with CASE?
Can i not put MUTIPLE arguments after a THEN clause to sort it as wanted
Re: SQL,PLSQL mutiple order by with decode issue [message #401155 is a reply to message #401135] Fri, 01 May 2009 18:26 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you weren't ordering some of columns DESC I'd say just concatenate them together.
As it is - if it really needs to be that complicated I think you're going to need multiple case statements. One per column you're ordering by.
Re: SQL,PLSQL mutiple order by with decode issue [message #401202 is a reply to message #401155] Sat, 02 May 2009 09:01 Go to previous message
rohit3312@gmail.com
Messages: 7
Registered: February 2009
Junior Member
when variable sort_key:= 'description' or 'memberCount'....each of the values, then the ORDER BY LOGIC is different for each of them.

As an example,when 'description' is selected-the sorting in the ORDER BY cluase should do as it does for 'name'.

Example like this:-
 UPPER(SUBSTR(group_description, 1, 1)),
             SUBSTR(group_description, 1, 1) DESC,
             UPPER(group_description),
             group_description DESC,
             group_description_key,


In brief,each of the options that the sort_key variable gets-the ORDER BY logic is different and i have to implement it in the same SQL only now.

Please help me very much to write a order by separately for each option- order by CASE with the options-can you please help me.I need your help very much
Previous Topic: Delete duplicate rows but keep original
Next Topic: Sub Queries
Goto Forum:
  


Current Time: Sat Dec 10 14:50:46 CST 2016

Total time taken to generate the page: 0.09389 seconds