Glad you got it working.
I still maintain that you don't need the SUM() and GROUP BY in the view
because the report can take care of that if you put the sums in the month
break footer.
You can use IYR4 to get a four digit year.
Also I think there's an error in the 2nd and 3rd selects: should be pamt,
not iamt.
Here's how I would do it:
set v vpyrb date =(rdate(1,1,(IYR4(.#date)-1)))
CREATE temp VIEW VDuesAct +
(DMonth,CYBAmt,CYPAmt,CYCAmt,PYBAmt,PYPAmt,PYCAmt) AS +
SELECT (imon(idate)),(ifeq(iyr(idate),iyr(.#date),iamt,0)),0,0, +
(ifeq(iyr(idate),iyr(.#date),0,iamt)),0,0 +
from vcie where idate >=.vpyrb and acctid = '401' +
UNION +
SELECT (imon(pdate)),0,(ifeq(iyr(pdate),iyr(.#date),pamt,0)),0, +
0,(ifeq(iyr(pdate),iyr(.#date),0,pamt)),0 +
from vcie where pdate >=.vpyrb and acctid = '401' +
UNION +
SELECT (imon(pdate)),0,0,(ifeq(iyr(pdate),iyr(.#date),pamt,0)), +
0,0,(ifeq(iyr(pdate),iyr(.#date),0,pamt)) +
from vcie where pdate >=.vpyrb and acctid = '401' and payway = 'cancel'
Regards,
Stephen Markson
ForenSys The Forensic Systems Group
www.ForenSys.ca
416 512 6950
> -----Original Message-----
> From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Jan Barley
> Sent: October 11, 2008 3:11 PM
> To: RBASE-L Mailing List
> Subject: [RBASE-L] - RE: Group By Problems
>
> Stephen!
>
> Guess what, guess what!!!!! I finally got the UNION SELECT to work! Know
> what the problem was? It was looking for a GROUP BY!!!! Here's the
> command
> I ended up with:
>
> set v vpyrb date =
> (rdate(1,1,((int(sget(format(.#date,'mm/dd/yyyy'),4,7)))-1)))
> CREATE temp VIEW VDuesAct
> (DMonth,CYBAmt,CYPAmt,CYCAmt,PYBAmt,PYPAmt,PYCAmt)
> AS +
> SELECT
> (imon(idate)),(ifeq((iyr(idate)),(iyr(#date)),(sum(iamt)),0)),0,0,(ifeq((i
> yr(idate)),(iyr(.vpyrb)),(sum(iamt)),0)),0,0+
> from vcie where idate >= (.vpyrb) and acctid = '401' group by idate +
> UNION SEL
> (imon(pdate)),0,(ifeq((iyr(pdate)),(iyr(#date)),(sum(pamt)),0)),0,0,(ifeq(
> (iyr(pdate)),(iyr(.vpyrb)),(sum(iamt)),0)),0
> from vcie +
> where pdate >= (.vpyrb) and acctid = '401' group by pdate +
> UNION SEL
> (imon(pdate)),0,0,(ifeq((iyr(pdate)),(iyr(#date)),(sum(pamt)),0)),0,0,(ife
> q((iyr(pdate)),(iyr(.vpyrb)),(sum(iamt)),0))
> from vcie +
> where pdate >= (.vpyrb) and acctid = '401' and payway = 'cancel' group by
> pdate
>
>
> Now I'm pretty sure I can get the report to summarize and break as I need
> it
> to!! Thank you SOOO much for your help. This is going to be such an
> awesome report and I think alot of y'all may have some use for it too! I
> am
> going to use this to do a report listing monthly billing and payments and
> compare it to the same month last year, with the percentage change for
> each - what a great management tool!
>
> Stephen, I really appreciate your offer to create the syntax for me. Can
> you tell I'm a tad bit "focused"? (My husband prefers that term to
> hard-headed!!!)
>
> Have a great weekend!!!!!
>
> Jan Barley
>
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com
> Version: 8.0.173 / Virus Database: 270.7.5/1704 - Release Date: 10/10/2008
> 4:08 PM
