Hi, I am trying to write an SQL which will read the Budget (budgettable_v1) and access an UNION of checkingaccount_v1 and splittransactions_v1 with categid, subcategid and amount fields being summarised .
When I just do the SQL using the UNION ALL feature for the 2 tables, it works fine. See below SQL:
select subquery1.categid, subquery1.subcategid, sum(subquery1.actuals) as transtot from
(select a.transid, a.categid, a.subcategid, a.splittransamount as actuals from splittransactions_v1 a where a.subcategid not in ( '98', '99') union all
select b.transid, b.categid, b.subcategid, b.transamount from checkingaccount_v1 b where b.subcategid not in ('-1','98','99') ) subquery1 group by subquery1.categid, subquery1.subcategid ;
However when I bring in the Budgettable_v1, Category_V1 and subcategory_v1 using the below SQL , I keep getting a syntax error. I am fairly a novice in SQL commands.
Would appreciate any help here.
This is the SQL I wrote:
select c.categid,ct.categname, c.subcategid, sc.subcategname,abs(c.amount) as budget,
subquery2.actuals as transtot from
budgettable_v1 c, category_v1 ct,
subcategory_v1 sc,
(select subquery1.categid, subquery1.subcategid,sum(subquery1.actuals) as transtot from
(select a.transid, a.categid, a.subcategid,a.splittransamount as actuals from splittransactions_v1 a where a.subcategid
not in ( '98', '99') union all
select b.transid, b.categid, b.subcategid, b.transamountfrom checkingaccount_v1 b where b.subcategid not in ('-1','98','99') ) subquery1
group by subquery1.categid,
subquery1.subcategid) subquery2 where
c.categid = subquery2.categid and c.subcategid =subquery2.subcategid and
c.categid = ct.categid and c.subcategid = sc.subcategid;
Help with SQL in GRM
Moderator: Renato
-
- New User
- Posts: 7
- Joined: Thu Mar 24, 2016 5:19 am
- Are you a spam bot?: No
-
- New User
- Posts: 7
- Joined: Thu Mar 24, 2016 5:19 am
- Are you a spam bot?: No
Re: Help with SQL in GRM
I have now resolved this problem by changing the SQl as under
Post can now be closed:D
Code: Select all
[b]select subquery1.categid, d.categname,subquery1.subcategid, e.subcategname, sum(subquery1.actuals) as transtot,
sum(subquery1.budget) as budtot from category_v1 d, subcategory_v1 e,[/b]
[b](select a.categid, a.subcategid, a.splittransamount asactuals , 0 as budget from splittransactions_v1 a where a.subcategid not in (
'98', '99') union all[/b]
[b]select b.categid,b.subcategid, b.transamount ,0 from checkingaccount_v1 b where b.subcategid not
in ('-1','98','99') union all select
c.categid,c.subcategid, 0, abs(amount) from budgettable_v1 c) subquery1 where subquery1.categid = d.categid and
subquery1.subcategid = e.subcategid group by subquery1.categid,
subquery1.subcategid;[/b]