SQL & PL/SQL

Hi all,


I have an interesting issue with the below SELECT.

Its about using ORDER BY clause to sort.(am doing it with a variable "p_sortby".)

Order by can be used by column name or column position, (1, 2, … etc. ).
Somehow, if I use position in the PL/SQL, it doesn’t work.
So I have to use column name which we can’t simply passing a varchar2 string there,
we need to use real column name. I noticed that it only applies to varchar2 type column though.
It doesn’t apply to for instance, a number column.

Can you all please advise me on this kind of issue about how to solve.

I am editing the code section because now the question is very simplified as to what is the issue?

Let’s say p_sortby = ‘memberCount’ in this case, I passed it as first argument in decode(),

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 clm_test_relationship grel join clm_test 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 clm_test 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, it works.. That’s my original issue about why it doesn’t apply on memberCount. 

Edited by: user_7000011 on Apr 29, 2009 9:35 PM