[sqr-users] Re: Order by

[David Donnelly <Dave@isisbio.com>]

Radhika,

The CASE statement works because it expresses a different way to categorize 
the salary values in your query.  You could also use DECODE (with Oracle) 
or maybe even

         order by (salary>30000), empname, addr1  [I didn't try this]

which would be treated as a logical variable, with other databases.

The ORDER BY clause is evaluated BEFORE the sql is executed ... before any 
rows are fetched.  Your logic seems to be attempting to change it with 
every row fetched.  That won't work. The solution you used is a good one 
because it does what you want: converts the salary value to a binary 
expression.

Note that you CAN say

         order by [$type], empname, addr1

but $type still has to be set before the query is started, and changing its 
value as rows are fetched will not have any effect.  You can also say

         let $orderby = 'order by ''' || $type || ''', empname, addr1'
         ...
         begin-sql
         variables ...
         from ...
         [$orderby]

This is handy when you might have a different number of variables in the 
ORDER BY from run to run; it permits you to include or exclude the 
necessary variable and its associated comma.

I suspect from your question, and especially your use of different types 
and comparisons for &sal and $sal, that you are new to SQR.  It would be of 
great help to you, and would probably save you a LOT of debugging time in 
the future, to ask your supervisor or mentor to explain the variable types, 
and what happens inside Oracle or other database systems when you try to 
mix columns and variables of different types.

Best regards,
Dave


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users