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,...等)使用.
不知何故,如果我在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屋!