Re: Confused with this SQL query

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Wed, 15 Dec 1999 17:14:15 +0200
Message-ID: <3857B047.531556EC@0800-einwahl.de>

Hi Parvinder,

your query

SQL> select a.pd_val, b.pg_val, c.mr_val, d.month_val, msr1_tab.msr_val,msr2_tab.msr_val
  2 from msr1_tab, msr2_tab,product a ,program b ,market c, month_f d   3 where
  4 (a.pd_val = msr1_tab.product
  5 and b.pg_val = msr1_tab.program
  6 and c.mr_val = msr1_tab.market)
  7 or
  8 (a.pd_val = msr2_tab.product
  9 and b.pg_val = msr2_tab.program
10 and c.mr_val = msr1_tab.market);

has the following meaning:

and no restriction on msr2_tab ) 7 or 8 (a.pd_val = msr2_tab.product 9 and b.pg_val = msr2_tab.program
I guess the next line should look like my line 10: ------------------v 10 and c.mr_val = msr2_tab.market
and no restriction on msr1_tab );

What you most likely want is
select pd_val, pg_val, mr_val, month_val, sum (msr_val1), sum (msr_val2) from (
select a.pd_val, b.pg_val, c.mr_val, d.month_val, msr1_tab.msr_val as msr_val1,0 as msr_val2
from msr1_tab,product a ,program b ,market c, month_f d where
a.pd_val = msr1_tab.product
and b.pg_val = msr1_tab.program
and c.mr_val = msr1_tab.market
--
union
--
select a.pd_val, b.pg_val, c.mr_val, d.month_val, 0 as msr_val1,msr2_tab.msr_val as msr_val2
from msr2_tab,product a ,program b ,market c, month_f d where
a.pd_val = msr2_tab.product
and b.pg_val = msr2_tab.program
and c.mr_val = msr2_tab.market
)
group by pd_val, pg_val, mr_val, month_val;

I assume that a condition on month_f is missing in your original query. So you will get the output of the union for all entries in month_f

Martin

Parvinder Singh wrote:
>
> Hi All
>
> I am really confused with this problem .... Its something like this
>
> These are my two tables
>
> SQL> select * from msr1_Tab;
>
> PRODUCT PROGRAM MARKET MSR_VAL
> --------------- --------------- --------------- ---------
> Product - A Program - A11 Comp -2 100
>
> SQL> select * from msr2_tab;
>
> PRODUCT MONTH PROGRAM MSR_VAL
> --------------- --------------- --------------- ---------
> Pd - A1 01-MAR-99 Program - A11 11
>
> and i have a querry something like this
>
>
> SQL> select a.pd_val, b.pg_val, c.mr_val, d.month_val,
> msr1_tab.msr_val,msr2_tab.msr_val
> 2 from msr1_tab, msr2_tab,product a ,program b ,market c, month_f d
>
> 3 where
> 4 (a.pd_val = msr1_tab.product
> 5 and b.pg_val = msr1_tab.program
> 6 and c.mr_val = msr1_tab.market)
> 7 or
> 8 (a.pd_val = msr2_tab.product
> 9 and b.pg_val = msr2_tab.program
> 10 and c.mr_val = msr1_tab.market);
>
> which gives me this output
>
>
>
> msr1_tab. msr2_tab.
> PD_VAL PG_VAL MR_VAL MONTH_VAL MSR_VAL
> MSR_VAL
> ------------- ------------- ------------- ------------- ---------
> ---------
> Pd - A1 Program - A11 Comp -2 01-JAN-99
> 100 11
> Pd - A1 Program - A11 Comp -2 01-OCT-99
> 100 11
> Pd - A1 Program - A11 Comp -2 01-SEP-99
> 100 11
> Pd - A1 Program - A11 Comp -2 01-AUG-99
> 100 11
> Pd - A1 Program - A11 Comp -2 01-JUL-99
> 100 11
> Pd - A1 Program - A11 Comp -2 01-JUN-99
> 100 11
> Pd - A1 Program - A11 Comp -2 01-MAY-99
> 100 11
> Pd - A1 Program - A11 Comp -2 01-MAR-99
> 100 11
> Pd - A1 Program - A11 Comp -2 01-APR-99
> 100 11
> Pd - A1 Program - A11 Comp -2 01-FEB-99
> 100 11
> Product - A Program - A11 Comp -2 01-JAN-99
> 100 11
> Product - A Program - A11 Comp -2 01-FEB-99
> 100 11
> Product - A Program - A11 Comp -2 01-APR-99
> 100 11
> Product - A Program - A11 Comp -2 01-MAR-99
> 100 11
> Product - A Program - A11 Comp -2 01-MAY-99
> 100 11
> Product - A Program - A11 Comp -2 01-JUN-99
> 100 11
> Product - A Program - A11 Comp -2 01-JUL-99
> 100 11
> Product - A Program - A11 Comp -2 01-AUG-99
> 100 11
> Product - A Program - A11 Comp -2 01-SEP-99
> 100 11
> Product - A Program - A11 Comp -2 01-OCT-99
> 100 11
>
>
>
> But i want the output as this
>
> msr1_tab. msr2_tab.
> PD_VAL PG_VAL MR_VAL MONTH_VAL MSR_VAL
> MSR_VAL
> ------------- ------------- ------------- ------------- ---------
> ---------
> Pd - A1 Program - A11 Comp -2 01-JAN-99
> Pd - A1 Program - A11 Comp -2 01-OCT-99
> Pd - A1 Program - A11 Comp -2 01-SEP-99
> Pd - A1 Program - A11 Comp -2 01-AUG-99
> Pd - A1 Program - A11 Comp -2 01-JUL-99
> Pd - A1 Program - A11 Comp -2 01-JUN-99
> Pd - A1 Program - A11 Comp -2 01-MAY-99
> Pd - A1 Program - A11 Comp -2
> 01-MAR-99 11
> Pd - A1 Program - A11 Comp -2 01-APR-99
> Pd - A1 Program - A11 Comp -2 01-FEB-99
> Product - A Program - A11 Comp -2 01-JAN-99 100
> Product - A Program - A11 Comp -2 01-FEB-99 100
> Product - A Program - A11 Comp -2 01-APR-99 100
> Product - A Program - A11 Comp -2 01-MAR-99 100
> Product - A Program - A11 Comp -2 01-MAY-99 100
> Product - A Program - A11 Comp -2 01-JUN-99 100
> Product - A Program - A11 Comp -2 01-JUL-99 100
> Product - A Program - A11 Comp -2 01-AUG-99 100
> Product - A Program - A11 Comp -2 01-SEP-99 100
> Product - A Program - A11 Comp -2 01-OCT-99 100
>
> since the value of 11 in msr2_tab is associated with Pd - A1
> 01-MAR-99 Program - A11
> and the record where these three values are occuring only there value
> 11 should occur
>
> and since 100 in msr1_tab is associated with Product - A Program -
> A11 Comp -2
> therefore the record where these three values are occuring only there
> the value 100 should occur
>
>
> can i have this ...by any kind of join or something ? i am a bit
> confused with this
> i hope i m clear with my problem ..i want both the values
> (msr1_tab.msr_val and msr2_tab.msr_val) in one querry only ...
>
>
> Regards & Thanks in advance
> ~Parvinder
>
> also reply to parora_at_questone.com
Received on Wed Dec 15 1999 - 09:14:15 CST