Re: SQR3 / MAXIMO - Cost Summary

["Hallmark, Robert J" <robert.j.hallmark@LMCO.COM>]

I have tried all the suggestions:  brackets, group by, alias, and separate
procedure for sum function.  I still get the same error message with the
brackets, group by, and alias.  The separate sum function runs without an
error message, however it prints only the cost for the last line rather than
totalling the costs for all lines. Any ideas on what else I might try?

Thanks for the time you have spent helping me with this so far.


Bob


        ----------
        From:   Rogers, Mike[SMTP:mrogers@TI.ROBINS.AF.MIL]
        Reply To:       SQR-USERS@USA.NET
        Sent:   Friday, November 06, 1998 9:13 AM
        To:     Multiple recipients of list SQR-USERS
        Subject:        Re: SQR3 / MAXIMO - Cost Summary

        Ken / Bob / et al,

        Sorry for the misinfo.  The [ ]'s are used when an expression is
input
        as a variable and used in the where clause.  For ex. if the input
was
                sparepart.eqnum like '100%'
        instead of just
                1004
        then the [ ]'s are needed.

        However, this does not apply in Bob's case.  Getting back to the
problem
        at hand, I believe the problem is the sum function needs to be in a
        procedure by itself.  For ex.

        -x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-
        Begin-Procedure Parts
        begin-select
        sparepart.eqnum         &eqnum
        sparepart.itemnum       &itemnum
        item.description        &description
        inventory.lastcost      &cost
        invbalances.location    &location
        invbalances.binnum      &binnum
        invbalances.curbal      &curbal
         do sumcost
         move &cost to #cost
         move &curbal to #curbal
         multiply #cost times #curbal
         move #curbal to #extcost
         do print
        from sparepart, item, invbalances, inventory
         where sparepart.eqnum = $eqnum
         and sparepart.itemnum like '30%'
         and sparepart.itemnum = item.itemnum
         and sparepart.itemnum = invbalances.itemnum
         and sparepart.itemnum = inventory.itemnum
         order by sparepart.itemnum
        end-select
        end-procedure

        begin-procedure sumcost
        begin-select
        sum(inventory.lastcost * invbalances.curbal) &totcost
        from inventory, invbalances
         where inventory.itemnum = invbalances.itemnum
         and inventory.itemnum = &itemnum
         and invbalances.itemnum = &itemnum
        end-select
        end-procedure

        -x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-


        Mike