Re: sql problem

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

Division is handled as integer division - so cast either the dividend 
or divisor to a decimal with non-zero decimal places

something like that

At 06:40 AM 3/1/2007, you wrote:

> I am hoping that you can help me with this problem.
>
> >
>
> > I have a file that I am trying to update a percent to total using sql.
> I created the file using sql's global temporary table.
>
> >
>
> > I have a type '1' record in the file that has the total that I want to
> divide by. a type '2' record that will have the mtd percent updated.
> Same file in order by record type.
>
> >
>
> > This is the sql that I am using.
>
> >
>
> > UPDATE QTEMP/TEMP1 A SET A.MTDNUM = A.CM/(SELECT B.CM FROM
>
> > QTEMP/TEMP1 B WHERE A.BSTYP1 = B.BSTYP1 AND A.TYPER = '2' AND B.TYPER
>
> > = '1')*100 WHERE EXISTS(SELECT B.CM FROM  QTEMP/TEMP1 B WHERE A.BSTYP1
>
>
> > = B.BSTYP1 AND A.TYPER = '2' AND B.TYPER = '1')
>
> >
>
> > The field mtdnum should have the percents, but ends up being 0.
>
> >
>
> > TYPER  KEY         BSTYP1           MTDNUM              CM
>
> >   1    Promotiona    A               .0000      143,056.00
>
> >   2                  A               .0000        1,364.00
>
> >   2    H1005         A               .0000           50.00
>
> >   2    IBDG          A               .0000           50.00
>
> >   2    IBDGC         A               .0000            4.00
>
> >   2    IBDG7         A               .0000           10.00
>
> >   2    IBEXG         A               .0000        2,953.00
>
> >   2    IBHC7         A               .0000            4.00
>
> >   2    IBH52         A               .0000             .00
>
> >
>
> > Any help that you can give me would be greatly appreciated
>
> >
>
>
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.