× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



 


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.