Shorten Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mtek - 29 Apr 2008 17:02 GMT

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

Reply to this Message

Urs Metzger - 29 Apr 2008 18:49 GMT

Mtek schrieb:

> Hi,
>
[quoted text clipped - 27 lines]
>
> 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

Reply to this Message

Urs Metzger - 29 Apr 2008 18:51 GMT

Mtek schrieb:

> Hi,
>
[quoted text clipped - 27 lines]
>
> 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

Reply to this Message

Mtek - 29 Apr 2008 19:32 GMT

> Mtek schrieb:
>
[quoted text clipped - 40 lines]
> 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

Reply to this Message

Urs Metzger - 29 Apr 2008 20:07 GMT

Mtek schrieb:

>> Mtek schrieb:
>>
[quoted text clipped - 45 lines]
> -- 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

Reply to this Message

Mtek - 29 Apr 2008 20:10 GMT

> Mtek schrieb:
>
[quoted text clipped - 51 lines]
>
> 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

Reply to this Message