RE: sql problem

My first thought is that you should have a set of brackets around the SET
formula as you are currently multiplying the divisor by 100 and not the
result:

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

All the best

Jonathan

-----Original Message-----
From: rpg400-l-bounces+jonathan.mason=astradyne-uk.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+jonathan.mason=astradyne-uk.com@xxxxxxxxxxxx] On
Behalf Of Landeen, Douglas
Sent: 01 March 2007 12:41
To: rpg400-l@xxxxxxxxxxxx
Subject: sql problem

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