July 30, 2015 at 12:19 pm
Hi all,
I have a query which wants to union join the data. no matter how many times I tried, I got an error. Could someone can let me know how to change my union query?
thank you.
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 [/b]
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
July 30, 2015 at 12:30 pm
I'm not sure, but just guessing here. Telling us what error you got might help a little.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 30, 2015 at 12:35 pm
Thanks Alvin.
The erros msg I got is :ORA-00923: FROM keyword not found where expected
July 30, 2015 at 12:37 pm
kennyhuang0108 (7/30/2015)
Thanks Alvin.The erros msg I got is :ORA-00923: FROM keyword not found where expected
Maybe adding the word FROM after
select distinct b.lev5 AS "LEVEL 1",b.lev5NAME, C.lev7 "FUND", C.lev7NAME,round (sum(a.data),2) AS AMOUNT
might help.
Just guessing.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 30, 2015 at 12:43 pm
Tried!
No luck.
Thank you for your help.
July 30, 2015 at 12:45 pm
here is an error msg :ORA-00933: SQL command not properly ended
thanks
July 30, 2015 at 12:48 pm
kennyhuang0108 (7/30/2015)
here is an error msg :ORA-00933: SQL command not properly endedthanks
WAIT!!!!!
Error message: ORA-00933???
Is this an Oracle query? FYI, this is not an Oracle forum.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 30, 2015 at 12:54 pm
You need at least three tables in your join which you refer as a, b & c. You're doing a UNION between 2 SELECTs and but you don't define why is it in an additional query, how are those related?
In Oracle, all statements need to end with a semicolon.
July 30, 2015 at 2:30 pm
Thanks Alvin and luiz.
Did you refer that I need to union with another table?
The single query worked fine when I executed. Each one has the data I want.
I just want to combine those two.
Thank you,.
July 30, 2015 at 4:14 pm
Get rid of the parentheses around the main queries -- those "tell" SQL that a subquery is coming, but these not subqueries.
/*(*/
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 lev5 --Edit: should be lev5 not b.lev5
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply