Re: Can I use a calculated value to order by ?

[Pankaj Bedekar <BedekarP@ALCONEMARKETING.COM>]

instead of using field name in order by use numbers ...
like order by 2,5,7,1

-----Original Message-----
From: Tim Wilkinson [mailto:Tim.Wilkinson@HANCORP.CO.UK]
Sent: Monday, May 14, 2001 6:23 AM
To: SQR-USERS@list.iex.net
Subject: Re: Can I use a calculated value to order by ?


Only way I can think of to do this off the top of my head is by using an
array (thats the way I thought of doing a similar problem). Populate the
array with the column values and your calculated values and you can then
print the array out anyway you want. Only trouble is that an array is fix
sized. If this is a problem, then you could populate a temp table...
                                ...or do whatever the next person says.
Their answer will probably be well thought out, insightful, concise,
informative and exactly what you want(unlike this vague waffle :) ).

Good luck,

                Tim

SQR in PeopleSoft and Other Applications page 164 shows all about arrays and
176 how to sort them.


BTW, I've got the .CSV report almost completed and its working well. So
thanks all.

-----Original Message-----
From: Chuck.L.King@SEALEDAIR.COM [mailto:Chuck.L.King@SEALEDAIR.COM]
Sent: 14 May 2001 13:03
To: SQR-USERS@list.iex.net
Subject: Can I use a calculated value to order by ?


Hi fellow SQR users,

As seen in my code below I am ordering the report by the
MAXIMO.PM.SUBACCOUNT field.  I  would like to do a secondary sort using the
#GTOTALCOST calculated value.  Is this possible and how would I code it?

!
----------------------------------------------------------------------------

BEGIN-SETUP
   DECLARE-PRINTER default-hp
      POINT-SIZE = 12
      FONT-TYPE  = fixed
      SYMBOL-SET = 10U
      PITCH      = 10
      FONT       = 3
   END-DECLARE

   DECLARE-LAYOUT PMESCOST
      PAPER-SIZE  = (8.5,11)
      FORMFEED    = yes
      ORIENTATION = portrait
      LEFT-MARGIN = 0.5
      TOP-MARGIN  = 0.25
      MAX-COLUMNS = 80
      MAX-LINES   = 60
      LINE-HEIGHT = 12
   END-DECLARE

     DECLARE-REPORT PMESCOST
          LAYOUT=PMESCOST
          PRINTER-TYPE=HP
     END-DECLARE

END-SETUP

!
----------------------------------------------------------------------------

BEGIN-PROGRAM
   USE-PRINTER-TYPE hp
   DO main
END-PROGRAM

!
----------------------------------------------------------------------------

BEGIN-HEADING 5

 Print-Image (1,1)
     type=bmp-file
     source='D:\MAXIMO\CRLOGOBW.BMP'
     image-size=(11,2)


   PRINT 'C R Y O V A C'               (1,) CENTER BOLD
   PRINT 'E S T I M A T E D    P M  M A S T E R    C O S T S' (3,) CENTER
BOLD
   PRINT 'IPT'                       (1,70)
   DATE-TIME                           (2,65)  MON-DD-YYYY
   DATE-TIME                           (3,67) 'HH:MI PM'
   PRINT 'PM MASTER'                  (5,1) BOLD
   PRINT 'LABOR COST'                 (5,16) BOLD
   PRINT 'MATL COST'                  (5,31) BOLD
   PRINT 'TOTAL COST'                 (5,44) BOLD
   GRAPHIC                             (6,1,75) HORZ-LINE 10


END-HEADING

!
----------------------------------------------------------------------------

BEGIN-PROCEDURE MAIN
 MOVE 0 TO #GTOTALCOST
BEGIN-SELECT
PM.PMNUM            &PMNUM
PM.DESCRIPTION      &PMDESC
PM.SUBACCOUNT       &PMSUBACCT
PM.JPNUM            &PMJPNUM
JOBMATERIAL.ITEMQTY &JMITEMQTY
INVENTORY.STDCOST   &INVSTDCOST
JOBLABOR.CRAFTQTY   &JLCRAFTQTY
JOBLABOR.LABORHRS   &JLLABHRS
LABOR.PAYRATE       &LABPAYRATE

  LET #LABQTYHRS = (&JLCRAFTQTY * &JLLABHRS)
  LET #LABCOST   = (&LABPAYRATE * #LABQTYHRS)
  ADD #LABCOST TO #TOTLABCOST

  LET #MATLQTYCOST = (&JMITEMQTY * &INVSTDCOST)
  ADD #MATLQTYCOST TO #TOTMATLCOST

  LET #TOTPMLINECOST = (#TOTLABCOST + #TOTMATLCOST)

  ADD #TOTLABCOST  TO #GTOTALCOST
  ADD #TOTMATLCOST TO #GTOTALCOST

     IF #TOTPMLINECOST >= 500.00
      PRINT &PMNUM       (+1,1)
      PRINT #TOTLABCOST  (,17)
      PRINT #TOTMATLCOST (,32)
      PRINT #GTOTALCOST  (,45)
     END-IF

     MOVE 0 TO #TOTLABCOST
     MOVE 0 TO #TOTMATLCOST
     MOVE 0 TO #TOTPMLINECOST
     MOVE 0 TO #GTOTALCOST

FROM MAXIMO.PM, MAXIMO.JOBMATERIAL, MAXIMO.INVENTORY,
        MAXIMO.JOBLABOR, MAXIMO.LABOR

WHERE     MAXIMO.PM.JPNUM = MAXIMO.JOBMATERIAL.JPNUM
      AND MAXIMO.PM.JPNUM = MAXIMO.JOBLABOR.JPNUM
      AND MAXIMO.JOBMATERIAL.ITEMNUM = MAXIMO.INVENTORY.ITEMNUM
      AND MAXIMO.JOBLABOR.LABORCODE = MAXIMO.LABOR.LABORCODE
      AND MAXIMO.PM.JPNUM IS NOT NULL
       AND   MAXIMO.PM.SUBACCOUNT LIKE 'BARR%'
 ORDER BY   MAXIMO.PM.PMNUM
END-SELECT
END-PROCEDURE

!
----------------------------------------------------------------------------

BEGIN-FOOTING 2
   GRAPHIC                            (1,1,75) HORZ-LINE 10
   UPPERCASE $sqr-program
   PRINT $sqr-program                    (2,1)
     PAGE-NUMBER                              (,35) 'Page '
     LAST-PAGE                           ()        ' of '
 UPPERCASE $username
   PRINT $username                   (,65)
END-FOOTING

!
----------------------------------------------------------------------------

Chuck L. King
Information Systems Support
TEL (940) 592-2111 x511
FAX (940) 592-2665