Re: Shorten Query
- From: Urs Metzger <urs@xxxxxxxxxxxxx>
- Date: Tue, 29 Apr 2008 21:07:37 +0200
Mtek schrieb:
On Apr 29, 12:51 pm, Urs Metzger <u...@xxxxxxxxxxxxx> wrote:You can use CASE rather than DECODE.Mtek schrieb:
Hi,SELECT SUM(DECODE(q1_shares, 0, 1, 0)) as q1,
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
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
Urs
.
- Follow-Ups:
- Re: Shorten Query
- From: Mtek
- Re: Shorten Query
- References:
- Shorten Query
- From: Mtek
- Re: Shorten Query
- From: Urs Metzger
- Re: Shorten Query
- From: Mtek
- Shorten Query
- Prev by Date: http://www.phenoelit.net/lablog/oracle.sl wrote
- Next by Date: Re: Shorten Query
- Previous by thread: Re: Shorten Query
- Next by thread: Re: Shorten Query
- Index(es):
Relevant Pages
|
