Help with SQL in GRM
Posted: Fri Apr 01, 2016 1:43 am
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;
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;