Dennis, I knew about having to get the first declared columns with the right data type for a Union to work properly but I didn't have any idea that you could do it the way you've just demonstrated to Jan.
That is so cunning and so simple at the same time - thanks for sharing it. Regards, Alastair. ----- Original Message ----- From: Dennis McGrath To: RBASE-L Mailing List Sent: Monday, October 27, 2008 2:33 PM Subject: [RBASE-L] - RE: Union Select OK, Jan, You have to make sure the first union gets the data types right. SET VAR vTmpCurr CURRENCY = 0.00 Use .vTmpCurr instead of the zeros in the first section cre temp vie vduesact (DMonth,DuesPer,TIamt,TPAmt,TCAmt) as sel idate,(((iyr4(idate))*100)+(imon(idate))),+ (.vTmpCurr ),(sum(iamt)),( .vTmpCurr ) from vcie where idate >= (.vdrpb) and acctid = '401' group by idate + This will cause the view to use currency datatypes. The zeros in the other 2 sections are ok as the datatypes are already defined by the first section. Dennis McGrath ------------------------------------------------------------------------------ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Jan Barley Sent: Monday, October 27, 2008 9:13 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Union Select Dennis, Here it is: cre temp vie vduesact (DMonth,DuesPer,TIamt,TPAmt,TCAmt) as sel idate,(((iyr4(idate))*100)+(imon(idate))),+ 0,(sum(iamt)),0 from vcie where idate >= (.vdrpb) and acctid = '401' group by idate + union sel pdate,(((iyr4(pdate))*100)+(imon(pdate))),+ (sum(pamt)),0,0 from vcie where pdate >= (.vdrpb) and acctid = '401' group by pdate + union sel pdate,(((iyr4(pdate))*100)+(imon(pdate))),+ 0,0,(sum(iamt)) from vcie where pdate >= (.vdrpb) and acctid = '401' and payway = 'cancel' group by pdate Thanks! Jan ------------------------------------------------------------------------------ No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus Database: 270.8.4/1749 - Release Date: 27/10/2008 07:57
