Databases
StackOverflow2013
Postsdbo
POPLSQL order by issue
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPLSQL order by issue
    primarykey
    data
    text
    <h3>Rolled back to revision one, then edited somewhat. See revised <a href="https://stackoverflow.com/questions/805568/sql-order-by-issue-continued-please-help">question</a>.</h3> <p>I have an interesting issue with the below SELECT. </p> <p>Its about ORDER BY clause; I am trying to sort using a variable called "p_sortby".</p> <p>Order by can be used by column name or column position, (1, 2, … etc. ).<br> 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. </p> <p>Can you all please advise me on this kind of issue about how to solve. </p> <pre><code>/*I am sorry as I cannot paste the format correct here*/. </code></pre> <p>Could you all please edit the SELECT and put the required format.</p> <pre><code>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) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. This table or related slice is empty.
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI started the formatting of the code. You can do that by selecting your code and clicking on the binary looking button above the edit box. That preserves whitespace, etc.
      singulars
      1. POPLSQL order by issue
      1. USTom H
    2. CORohit - next time, format the code with 4 spaces of indent, and leave the text of the question unindented. I sorted out the mess so that @devio gets proper credit for his excellent answer. But the onus is on you, as the asker, to format the question for legibility. Please remember to look at the pre-view. You can also get help on the markup language. It is best to avoid horizontal scrolling; it is OK if some long lines force it, but people should be able to see the bulk of your code.
      singulars
      1. POPLSQL order by issue
      1. USJonathan Leffler
    3. CO@Rohit: also note that it is best to edit your question to clarify, rather than adding 'answers from questionner' as below.
      singulars
      1. POPLSQL order by issue
      1. USJonathan Leffler
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload