Re: Using $anyvariable in order

[Enrique Munoz <enrique_munoz@KAIZEN.COM.MX>]

Or you can do this:

let $Month = substr(J.effdt, 6,2)
let $orderby = 'ORDER BY ' || $Month

begin-SELECT
J.FOO
J.BAR
substring(J.EFFDT,6,2)
FROM TABLENAME
[$orderby]
end-select


-----Mensaje original-----
De:     Jason Gill [SMTP:Jason.Gill@WELLPOINT.COM]
Enviado el:     jueves 13 de julio de 2000 16:29
Para:   SQR-USERS@list.iex.net
Asunto: Re: Using  $anyvariable in order

>I need to order a report by Department, Month.  I'm deriving month from the
 the effective date as follows
>$Month = substr(J.effdt, 6,2) ..  The order by clause does not appear to
accept  anything but a column >variable.  Is there a way to get around this
constraint ?

Repeat the substr in the order by.  I don't know your database, but in Sybase
it would look like this :

begin-SELECT

J.FOO
J.BAR
substring(J.EFFDT,6,2)
FROM TABLENAME
ORDER BY substring(J.EFFDT,6,2)

end-select


This works for any operation on a column.  I've use LPAD in the ORDER BY to
numerically sort non-zero-padded  numerical data in a character field.

--
Jason Gill
Independent PeopleSoft Consultant
619.723.5881