Re: Shorten Query

From: Mtek <mtek_at_mtekusa.com>
Date: Tue, 29 Apr 2008 12:10:04 -0700 (PDT)
Message-ID: <5ef65c90-55e6-4521-9716-3c909783c152@27g2000hsf.googlegroups.com>

On Apr 29, 2:07 pm, Urs Metzger <u..._at_ursmetzger.de> wrote:
> Mtek schrieb:
>
> > On Apr 29, 12:51 pm, Urs Metzger <u..._at_ursmetzger.de> wrote:
> >> Mtek schrieb:
>
> >>> Hi,
> >>> Please look at this query. notice that most of the criteria is the
> >>> same, except for 1 line. I need to get the count for 10 items. Is
> >>> there a better way to do this so I do not have to repeat the criteria
> >>> for each item I want to return?
> >>> I realize that one line of the criteria is different, but I am hoping
> >>> to not have to repeat the other criteria.....
> >>> SELECT a.q1, b.q2
> >>> FROM (SELECT count(*) q1
> >>> FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices
> >>> p
> >>> WHERE vi.inst_num = vh.inst_num
> >>> AND vh.ticker = mt.ticker
> >>> AND mt.m_ticker = sd.m_ticker
> >>> AND mt.m_ticker = p.m_ticker
> >>> AND q1_shares = 0) a,
> >>> (SELECT count(*) q2
> >>> FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices
> >>> p
> >>> WHERE vi.inst_num = vh.inst_num
> >>> AND vh.ticker = mt.ticker
> >>> AND mt.m_ticker = sd.m_ticker
> >>> AND mt.m_ticker = p.m_ticker
> >>> AND q0_shares = 0) b;
> >>> Thank you much,
> >>> John
> >> SELECT SUM(DECODE(q1_shares, 0, 1, 0)) as q1,
> >> SUM(DECODE(q0_shares, 0, 1, 0)) as q2
> >> FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices p
> >> WHERE vi.inst_num = vh.inst_num
> >> AND vh.ticker = mt.ticker
> >> AND mt.m_ticker = sd.m_ticker
> >> AND mt.m_ticker = p.m_ticker;
>
> >> hth,
> >> Urs Metzger
>
> > Your solution is interesting. In fact, I like it. Is this possible
> > also if I need to use a > or <?
>
> > The actual results they are looking for is:
>
> > -- A count of the number of tickers that have Q1_SHARES = 0
> > -- A count of the number of tickers that have Q0_SHARES = 0
> > -- A count of the number of tickers that have Q1_SHARES > Q0_SHARES
> > -- A count of the number of tickers that have Q1_SHARES < Q0_SHARES
> > -- A count of the number of tickers that have Q1_SHARES = Q0_SHARES
>
> You can use CASE rather than DECODE.
>
> Urs

Actually, I also used DECODE like this:

SELECT SUM(DECODE(q1_shares, 0, 1, 0)) AS q1_shares_0,
       SUM(DECODE(q0_shares, 0, 1, 0)) AS q0_shares_0,
       SUM(DECODE(q0_shares, q1_shares, 1, 0)) AS q0_q1_shares_equal
       SUM(DECODE(SIGN(q0_shares - q1_shares), -1, 1, 0)) AS q0_lt_q1
       SUM(DECODE(SIGN(q1_shares - q0_shares), -1, 1, 0)) AS q1_lt_q0

   FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices p

   WHERE vi.inst_num = vh.inst_num
     AND vh.ticker = mt.ticker
     AND mt.m_ticker = sd.m_ticker
     AND mt.m_ticker = p.m_ticker;

So, you pointed me in the right direction.

Thanks!!

John Received on Tue Apr 29 2008 - 14:10:04 CDT