Re: Using $anyvariable in order

[Jenny Mckeon <MCKEON@SHANDS.UFL.EDU>]

Thanks, that would work well except for one wrinkle.  I'm using fields from  
both  current and prior rows in this SQR, and the month is in the current row, 
whereas the department is in the prior row.  I'm doing my control breaking in 
the called procedure (which selects the prior row), passing it the current 
row's effective date as (I think what is called) a bind variable.  
Apparently you can't use a column from another select (a bind variable) in the 
order by.
The two selects are attached.

Jenny Mckeon
Shands Healthcare, Info. Svcs.
Application Systems Analyst, Senior
(352) 395-0680, ext. 8-5232
internet:mckeon@shands.ufl.edu

>>> Jason.Gill@WELLPOINT.COM 07/13/00 06:29PM >>>
>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


!***********************************************************************
begin-procedure Main
!***********************************************************************
display  'in procedure main  '
Begin-Select
J.emplid
  DISPLAY &j.emplid
J.empl_Rcd#
J.deptid
  let $todept = &j.deptid
J.Effdt
  let $acteffdt = &j.effdt
   do Get-Month
J.Effseq
  display &j.Effseq
J.Action
  let $action = &j.action
     do Select-Prior-Row
>From PS_JOB J
where (J.Action not in ('REH'))
and J.Effdt = (select Max(J1.Effdt)
     from PS_JOB J1
     Where J1.Emplid = J.Emplid
     and J1.Effdt between $Fromdate and $Thrudate)
  and J.Effseq = (Select Max(J2.effseq)
         from PS_JOB J2
         where J2.Emplid    = J.Emplid
         and   J2.Empl_Rcd# = J.Empl_Rcd#
         and J2.Effdt = J.Effdt)
and Exists
    (select 'X' from PS_JOB JX
where JX.emplid = J.emplid
and  JX.Deptid like '0103%'
and  JX.deptid <> J.deptid
and  JX.effdt = (select max(JZ.effdt) from PS_JOB JZ
                 where JZ.emplid = JX.emplid
                   and JZ.effdt <  J.effdt)
  and JX.Effseq = (Select Max(JX1.effseq)
         from PS_JOB JX1
         where JX1.Emplid    = JX.Emplid
         and   JX1.Empl_Rcd# = JX.Empl_Rcd#
         and JX1.Effdt = JX.Effdt))


End-Select
End-Procedure

!***********************************************************************
begin-procedure Select-Prior-Row
!***********************************************************************
display  'in procedure select prior row '
Begin-Select   Loops=1
JP.DEPTID     ()  on-break print=never
             level=1
             save=$oldept
             Before=dept-change
             skiplines = 1
   print $month  (+1,1) on-break print=change/top-page
             level=2
             save = $oldmonth
             After=dept-totals
             skiplines=1
JP.Jobcode       (,7)   on-break print=change/top-page
                        level=3
Pos.Descr        (,18)
P.name       (,44,33)
JP.emplid    (,69,10)
   DISPLAY &JP.EMPLID
JP.empl_Rcd#
JP.Effdt
JP.Effseq
JP.Empl_status  (,82)
JP.Action
Pos.Std_hrs_default
  let #FTE = &Pos.std_hrs_default / 40
  do Format-Number(#FTE, $out, 'b9.99')
  print $out                (0,89)
JP.Std_hours
  display 'std hrs'
  display &jp.std_hours
  let #FTE = &JP.std_hours / 40
  do Format-Number(#FTE, $out, 'b9.99')
  print $out                (0,95)
  print $action             (0,103)
  print $todept             (0,120)
  print $acteffdt           (0,109)
  add 1 to #Empl_cnt
  add 1 to #Grand_cnt
P.Supervisor_id
 move &p.Supervisor_id to $emplid
 do Get-Employee-Name
 Print $name          (,133)


>From PS_JOB JP,
     PS_Personnel_Hist P,
     PS_Position_Data  POS
Where JP.Emplid = &J.emplid
and JP.Empl_Rcd# = &J.Empl_Rcd#
and JP.Effdt = (select Max(J1P.Effdt)
     from PS_JOB J1P
     Where J1P.Emplid = JP.Emplid
     and J1P.Empl_Rcd# = &J.Empl_Rcd#
     and J1P.Effdt   < &J.effdt)
  and JP.Effseq = (Select Max(JP2.effseq)
         from PS_JOB JP2
         where JP2.Emplid    = JP.Emplid
         and   JP2.Empl_Rcd# = JP.Empl_Rcd#
         and JP2.Effdt = JP.Effdt)
and JP.Emplid    = P.Emplid
and P.Effdt = (select Max(P1.Effdt)
     from PS_Personnel_Hist P1
     Where P1.Emplid = P.Emplid
     and P1.Effdt   <=   JP.effdt)
and JP.Position_nbr = Pos.Position_nbr
and Pos.Effdt = (select Max(P3.Effdt)
     from PS_Position_data P3
     where P3.Position_nbr = Pos.Position_nbr
     and P3.effdt <= JP.effdt)
order by JP.deptid
End-Select                                                              CHAR
End-Procedure




begin-Procedure Dept-Change
display  'in procedure dept change'
  new-page
  move &JP.DeptId to $DeptId
  do Get-Department-Name
End-Procedure

begin-Procedure Get-Month
   let $Month = SUBSTR($acteffdt,6,2)
   evaluate $Month
   when = '01'
   let $Month = 'Jan'
   break
   when = '02'
   let $Month = 'Feb'
   break
   when = '03'
   let $Month = 'Mar'
   break
   when = '04'
   let $Month = 'Apr'
   break
   when = '05'
   let $Month = 'May'
   break
   when = '06'
   let $Month = 'Jun'
   break
   when = '07'
   let $Month = 'Jul'
   break
   when = '08'
   let $Month = 'Aug'
   break
   when = '09'
   let $Month = 'Sep'
   break
   when = '10'
   let $Month = 'Oct'
   break
   when = '11'
   let $Month = 'Nov'
   break
   when = '12'
   let $Month = 'Dec'
   break
   end-evaluate

End-Procedure

begin-Procedure Dept-Totals
display  'in procedure dept totals'
  Let $emplcnt = to_char(#empl_cnt)
  Let $total = 'Total for Department ' || $oldept || ' for '
      || ' the month of ' || $oldmonth
  print $total     (+2,1)
  print ': '      (0,+1)
  print $emplcnt  (0,57)
  print ' '        (+2,1)
  let #empl_cnt = 0
End-Procedure