Re: Shorten Query

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
     AND q0_shares = 0

hth,
Urs Metzger Received on Tue Apr 29 2008 - 12:49:50 CDT