Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If I understand you correctly, you are asking if it is possible to vary the whole ORDER BY list dynamically based on a bound parameter, including ASC/DESC variations, etc. </p> <p>Your best bet is to run different queries with different ORDER BYs depending on how you want to sort. It is much simpler and will run faster.</p> <p>If you really want to do it the way you are asking, you can do it to a certain extent using the same technique you are already using with the ORDER BY DECODE(choice1, expression1, choice2, expression2,...), except that the expressions would have to be a lot more complex. Each expression would have to produce a concatenated string of fixed-size fields in order of sort priority, for example:</p> <pre><code>ORDER BY DECODE('description', UPPER(SUBSTR(group_description, 1, 1)) || SUBSTR(group_description, 1, 1) || RPAD(UPPER(group_description),40) || RPAD(group_description_key,10) ,'name', UPPER(SUBSTR(group_name, 1, 1)), || LPAD(1000-ASCII(group_name)), -- first char DESC || RPAD(UPPER(group_name),20) || RPAD(group_name_key,10) </code></pre> <p>Also, it would be difficult to mix ASC and DESC fields. You can switch direction for numeric fields using something like LPAD(1000000 - n,7) where the 1000000 is a number higher than any possible n. You can also play some other tricks like I did in my example with the first char of group_name.</p> <p>This technique will prevent the database from using indexes to speed up the ordering (unless you add some crazy function-based indexes).</p> <p>Again, I recommend you abandon this approach and just send the database a whole different query depending on your desired ORDER BY clause. If you are using PL/SQL, you can also use dynamic SQL to build the query with the desired ORDER BY clause and execute that.</p> <p>EDIT: My PL/SQL is a little rusty but here is how you can do it in PL/SQL with dynamic SQL. </p> <pre><code>FUNCTION QueryGroups(sort_key in varchar2) RETURN REF CURSOR IS sql_block VARCHAR2(2000); order_by VARCHAR2(2000); ret REF CURSOR; BEGIN order_by := CASE sort_key WHEN 'name' THEN q'{UPPER(SUBSTR(group_name, 1, 1)), SUBSTR(group_name, 1, 1) DESC, UPPER(group_name), group_name DESC, group_name_key}'; WHEN 'description' THEN q'{UPPER(SUBSTR(group_description, 1, 1)), SUBSTR(group_description, 1, 1) DESC, UPPER(group_description), group_description DESC, group_description_key}'; END CASE; sql_block = q'{SELECT * FROM (SELECT distinct gl.group_id, gl.group_name, gl.group_description, gl.status_code, gl.member_count, (SELECT grpp.group_name FROM test_group_relationship grel JOIN test_group 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 test_group AS gl WHERE gl.group_org_id = '3909' AND (gl.group_name_key LIKE '%GROUP%') ) AS data_set }' || order_by; OPEN ret for sql_block; return ret; END QueryGroups; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSQL ORDER BY issue
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCarlos A. Ibarra
    1. USCarlos A. Ibarra
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks.I can understand your point in the above approach. In addition,i was trying to do this using order by with CASE,but it does not run and gives 'keyword' missing error.
      singulars
      1. PO
      1. This table or related slice is empty.
    2. COORDER BY CASE WHEN :p_sortby = 'name' THEN UPPER(SUBSTR(group_name, 1, 1)), SubSTR(group_name, 1, 1) DESC,UPPER(group_name), group_name_key, group_name DESC /*The above THEN clause works if i just have only 1 argument after THEN .It doesn't work if i have all of the arguments*/ /*It gives a keyword missing error*/ I feel my syntax is wrong when we pass many arguments.Can you please correct me? if i want to pass a DIFFERENT SQL by the value in the order by variable--how would i write it? Just one example of writing it will help me do the other ones
      singulars
      1. PO
      1. This table or related slice is empty.
    3. COYou can't do it like that. The thing after the THEN must be a single expression returning a single string. Same as the expressions in the DECODE. It cannot be a list of columns. Read my answer carefully. See how I concatenate using ||. But best of all, don't try to do it this way at all. Instead, build the query and its ORDER BY as a string and execute it using dynamic SQL.
      singulars
      1. PO
      1. USCarlos A. Ibarra
 

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