Post reply

Union Join

  • July 30, 2015 at 12:19 pm

    #300905

    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

    #1816276

    I'm not sure, but just guessing here. Telling us what error you got might help a little.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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

    #1816278

    Thanks Alvin.

    The erros msg I got is :ORA-00923: FROM keyword not found where expected

  • July 30, 2015 at 12:37 pm

    #1816280

    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.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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

    #1816283

    Tried!

    No luck.

    Thank you for your help.

  • July 30, 2015 at 12:45 pm

    #1816284

    here is an error msg :ORA-00933: SQL command not properly ended

    thanks

  • July 30, 2015 at 12:48 pm

    #1816285

    kennyhuang0108 (7/30/2015)

    here is an error msg :ORA-00933: SQL command not properly ended

    thanks

    WAIT!!!!!

    Error message: ORA-00933???

    Is this an Oracle query? FYI, this is not an Oracle forum.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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

    #1816288

    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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 30, 2015 at 2:30 pm

    #1816306

    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

    #1816322

    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) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply