| Union Join [message #640570] |
Thu, 30 July 2015 15:51  |
 |
ajen7118
Messages: 3
Registered: July 2015
|
Junior Member |
|
|
Hi all,
I have a query as below and I want to join these two queries in order to get info.
each query works fine but when I try to join them together.
I got the error msg :ORA-00933: SQL command not properly ended thank you for your kind help.
select distinct b.lev5 AS "LEVEL 1",b.lev5NAME, C.lev7 "FUND", C.lev7NAME,round (sum(a.data),2) AS AMOUNT
(Select distinct b.lev5
from bf_data a
inner join bf_orgn_cnsl_tbl b
on a.bf_orgn_cd = b.bf_orgn_cd
inner join bf_fund_cnsl_tbl c
on a.bf_fund_cd =c.bf_fund_cd
inner join bf_Orgn d ---> inner join with bf_orgn_cd colunm
on b.bf_orgn_cd =d.cd
WHERE a.bf_tm_perd_cd in (select bf_tm_perd_chld_cd from bf_tm_perd_cnsl_tbl where bf_tm_perd_select_cd='ADOPTTOT')
and a.bf_bdob_cd in (select bf_bdob_chld_cd from bf_bdob_rlup_tbl where rlup1 in ('TOTEXP','TOTBSAEXP'))
and c.bf_fund_cnsl_slcn_cd = '01-FUND'
and b.bf_orgn_cnsl_slcn_cd = '01-ORG'
and d.bf_acty_cd in (select distinct bf_acty_cd from bf_acty_cnsl_tbl where lev2 in ('400_FUNC'))
and c.LEV4='GF'
AND c.bf_fund_cd= 'A01' )
union
(select distinct b.lev5
from bf_data a
inner join bf_orgn_cnsl_tbl b
on a.bf_orgn_cd = b.bf_orgn_cd
inner join bf_fund_cnsl_tbl c
on a.bf_fund_cd =c.bf_fund_cd
inner join bf_Orgn d
on b.lev5 =d.cd ---> inner join with lev5 colunm
WHERE a.bf_tm_perd_cd in (select bf_tm_perd_chld_cd from bf_tm_perd_cnsl_tbl where bf_tm_perd_select_cd='ADOPTTOT')
and a.bf_bdob_cd in (select bf_bdob_chld_cd from bf_bdob_rlup_tbl where rlup1 in ('TOTEXP','TOTBSAEXP'))
and c.bf_fund_cnsl_slcn_cd = '01-FUND'
and b.bf_orgn_cnsl_slcn_cd = '01-ORG'
and b.lev5NAME like 'CP%' --> the only difference from first query
and d.bf_acty_cd in (select distinct bf_acty_cd from bf_acty_cnsl_tbl where lev2 in ('400_FUNC'))
and c.LEV4='GF'
AND c.bf_fund_cd= 'A01')
group by b.lev5 ,b.lev5NAME , C.lev7, C.lev7NAME
order by b.lev5
|
|
|
|
| Re: Union Join [message #640571 is a reply to message #640570] |
Thu, 30 July 2015 16:06  |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Your code doesn't make sense, is that query on the second line meant to be a subquery projected as a column of the first query? If so, you need to insert a comma before it. You seem to have column aliases for some columns, but not others. Is that right?
|
|
|
|
|
|
| Re: Union Join [message #640582 is a reply to message #640576] |
Fri, 31 July 2015 02:55 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
In addition to what John already pointed out your outer query is missing a FROM clause.
What you've posted is a mess and since we don't know anything about your DB or what the query is supposed to do, we can't tell you what it should be.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
