Re: sql problem

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.