PLSQL 按问题排序 [英] PLSQL order by issue
问题描述
我对下面的 SELECT 有一个有趣的问题.
I have an interesting issue with the below SELECT.
关于 ORDER BY 子句;我正在尝试使用名为p_sortby"的变量进行排序.
Its about ORDER BY clause; I am trying to sort using a variable called "p_sortby".
Order by 可用于列名或列位置,(1, 2, … etc.).
不知何故,如果我在 PL/SQL 中使用位置,它不起作用.所以我必须使用列名,我们不能简单地在那里传递一个 varchar2 字符串,我们需要使用真实的列名.我注意到它只适用于 varchar2 类型的列.它不适用于例如数字列.
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*/.
请大家编辑 SELECT 并输入所需的格式.
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)
推荐答案
尝试使用 DECODE() 函数选择 ORDER BY 子句作为单独的列:
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
我不确定您所说的不起作用"是什么意思.如果您的意思是 member_count 未按预期排序,请使用 TO_CHAR(gl.member_count, '000000') 强制进行格式化字符串转换.(将格式掩码调整为预期的位数)
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)
这篇关于PLSQL 按问题排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!