SV: SQR3 / MAXIMO - Cost Summary

[Karin Öberg <karin.oberg@METEORIT.SE>]

Hi!!
I don´t know if you mean like this when you´ve tried "group by" , but in
case you haven´t -
try using all the columns you select even in the group by:
 
group by sparepart.eqnum,item.description, inventory.lastcost,
invbalances.location, invbalances.binnum invbalances.curbal    
sparepart.itemnum

Good luck anyway!!
 
//Karin

> -----Ursprungligt meddelande-----
> Från:       Hallmark, Robert J [SMTP:robert.j.hallmark@LMCO.COM]
> Skickat:       den 9 november 1998 14:16
> Till: Multiple recipients of list SQR-USERS
> Ämne:       Re: SQR3 / MAXIMO - Cost Summary
> 
> 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