Jan, Alastair, I'm happy I could supply a simple solution. Many years ago, (more than I would like to admit), someone shared this technique in my time of need.
Dennis ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Alastair Burr Sent: Monday, October 27, 2008 10:28 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Union Select 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<mailto:[EMAIL PROTECTED]> To: RBASE-L Mailing List<mailto:[email protected]> 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
