PLSQL order by issue

Question

Rolled back to revision one, then edited somewhat. See revised question.

I have an interesting issue with the below SELECT.

Its about ORDER BY clause; I am trying to sort using a variable called "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 sorry as I cannot paste the format correct here*/.

Could you all please edit the SELECT and put the required format.

select distinct gl.group_id, gl.group_name
     from test_group gl
    where gl.group_org_id = p_orgid
      and (   gl.group_name_key like '%' || p_name || '%' 
              or p_name is null
              or p_name = ''
          )
      and (   gl.group_description_key like '%' || p_description || '%' 
              or p_description is null
              or p_description = ''
          )
      and (   gl.status_code = p_statuscode
              or p_statuscode is null
              or p_statuscode = 99
          )
      and gl.group_id in (
                  select gm.group_id
                      from test_group_member gm join test_org_person op
                                on gm.person_id = op.o_person_id
                           join test_person pp
                                on op.o_person_id = pp.person_id
                      where (   upper(pp.firstname) like
                                 '%' || upper(p_adminfirstname) || '%'
                                or p_adminfirstname is null
                                or p_adminfirstname = ''
                            )
                        and (   upper(pp.lastname) like
                                '%' || upper(p_adminlastname) || '%'
                                or p_adminlastname is null
                                or p_adminlastname = ''
                            )
                        and (   upper(op.emplid) like
                                '%' || upper(p_adminemployeeid) || '%'
                                or p_adminemployeeid is null
                                or p_adminemployeeid = ''
                            )
                        and gm.isadmin = 1)
     and gl.group_id in (
              select gm.group_id
                  from test_group_member gm join test_org_person op
                           on gm.person_id = op.o_person_id
                       join test_person pp
                           on op.o_person_id = pp.person_id
                  where (   upper(pp.firstname) like
                            '%' || upper(p_memberfirstname) || '%'
                            or p_memberfirstname is null
                            or p_memberfirstname = ''
                        )
                    and (   upper(pp.lastname) like
                            '%' || upper(p_memberlastname) || '%'
                            or p_memberlastname is null
                            or p_memberlastname = ''
                        )
                    and (   upper(op.emplid) like
                            '%' || upper(p_memberemployeeid) || '%'
                            or p_memberemployeeid is null
                            or p_memberemployeeid = ''
                        )
                    and gm.isadmin = 0)

Answer 1

Try to select the ORDER BY clause as a separate column using the DECODE() function:

SELECT DECODE(p_sortby, 'ID', gl.group_id, 'NAME', group_name) AS sort, ...
...
ORDER BY 1

Edit:

I'm not sure what you mean by "doesn't work". If you mean that member_count is not sorted as you expect, use TO_CHAR(gl.member_count, '000000') to enforce a formatted string conversion. (adjust format mask to expected number of digits)

Answer 2

If you want to do that, you will have to do dynamic SQL with PL/SQL.

DECLARE
  sql_str VARCHAR2(500);
  sortby  VARCHAR2(30) := 'your_column_name';
BEGIN
 -- Dynamic PL/SQL block invokes subprogram:
  sql_str := 'select * from your_table where a = 1 order by :a';

  OPEN CURSOR v_your_cursor for sql_str USING sortby;

  -- Then iterate your cursor.
END;
/

Answer 3

Hi Pablo,

Thanks a lot for your inputs. i have to sort using ORDER BY with a variable p_sortby where p_sortby can take any column_name or column position.

Answer 4

Hi Pablo, If your suggested way wouldn't work in PLSQL,then what else can i do it handle it. Please help me because i cannot use the option of creating Block,cursor,etc-It would involve a huge change in the architecture as we are using SQLs only.

Answer 5

Seems like dynamic SQL is what you want.

When you do things like:

... and (gl.group_name_key like '%' || p_name || '%' or p_name is null or p_name = '')

the optimizer can't use an index on the group_name_key column. Are you sure you want to do it this way?