RE: [sqr-users] Order by

[James Womeldorf <jwomeldo@fastenal.com>]

If your database is Oracle you might try using CASE.  I believe the
following syntax will work:

begin-select
empname &ename
A.addr1 &addr1
A.addr2
CASE WHEN a.sal > 30000 THEN 'SA' ELSE 'SE' END &type
    let $type = &type
from table1 A
order by CASE WHEN a.sal > 30000 THEN 'SA' ELSE 'SE' END,A.empname,A.addr1 

Jim

-----Original Message-----
From: sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org]On Behalf Of
Larry Roux
Sent: Friday, August 27, 2004 3:04 PM
To: sqr-users@sqrug.org
Subject: Re: [sqr-users] Order by


Firstly, you ar ealways better off using aliases
ie:
begin-procedure sample
begin-select
empname &ename
A.addr1 &addr1
A.addr2
A.sal  &sal
      let $sal=&sal
      if $sal>30,000
         let $type = 'SA'
     else
        let $type = 'SE'
     end-if
from table1 A

order by $type,A.empname,A.addr1 

secondly, you can't order by a variable set in the select clause. 




Larry Roux
Syracuse University
lroux@syr.edu


>>> radhika_05@hotmail.com 8/27/2004 3:49:06 PM >>>
Hi,

     I am having a problem using order by for a string variable ($type)
in 
the following program(sample).

begin-procedure sample
begin-select
empname &ename
addr1 &addr1
addr2
sal  &sal
      let $sal=&sal
      if $sal>30,000
         let $type = 'SA'
     else
        let $type = 'SE'
     end-if
from table1

order by $type,empname,addr1    ! when i am giving $type in order by,
it's 
not giving error but it's not giving desired output also

end-select
end-procedure

Any suggestions?

Thanks

_________________________________________________________________
Don't just search. Find. Check out the new MSN Search! 
http://search.msn.click-url.com/go/onm00200636ave/direct/01/ 


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

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

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