Help with SQL in GRM

Page 1 of 1
2 posts
Help with SQL in GRM

Vara
New MMEX User

Posts: 6
Joined: Wed Mar 23, 2016 11:19 pm
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;
Re: Help with SQL in GRM

Vara
New MMEX User

Posts: 6
Joined: Wed Mar 23, 2016 11:19 pm
I have now resolved this problem by changing the SQl as under

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]


Post can now be closed:D
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 4 guests