Messed Up Query

#1

Hi,

I have this query:

SELECT q10.q10_count,
vi.mgr_flag,vi.inst_type,vi.inst_num,vi.inst_cst,v i.inst_zip,vi.inst_phone,vi.url,vi.turnover,vi.hol d_cnt,
vi.date_,vi.inst_size,vi.inst_dchg,

vh.ticker,vh.q0_shares,vh.q1_shares,vh.q0q1_grwth, vh.q0q1_dchg,vh.shares_out,
mt.comp_name, sd.sector_name, p.closing
FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices p,
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10
WHERE inst_name = 'Yacktman Fund'
AND 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;

Take a look at the inner query in the FROM clause:
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10

I basically need the same criteria applied as the outer query. Do I
have to re-list the criteria? I'll have to do that count for 5
fields. It will make this query WAY long.......

Thanks!

John

#2

Mtek wrote:

Quote:

Hi,

I have this query:

SELECT q10.q10_count,
vi.mgr_flag,vi.inst_type,vi.inst_num,vi.inst_cst,v i.inst_zip,vi.inst_phone,vi.url,vi.turnover,vi.hol d_cnt,
vi.date_,vi.inst_size,vi.inst_dchg,

vh.ticker,vh.q0_shares,vh.q1_shares,vh.q0q1_grwth, vh.q0q1_dchg,vh.shares_out,
mt.comp_name, sd.sector_name, p.closing
FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices p,
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10
WHERE inst_name = 'Yacktman Fund'
AND 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;

Take a look at the inner query in the FROM clause:
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10

I basically need the same criteria applied as the outer query. Do I
have to re-list the criteria? I'll have to do that count for 5
fields. It will make this query WAY long.......

I can't tell for sure, but it sounds like you just need an analytical
function in the outer query...

count(*) over (partition by vh.ticker)

....or a selective count...

sum(decode(vh.q1_shares, 0, 1, 0)) over (partition by vh.ticker)

--
Peter

#3

Mtek wrote:

Quote:

Hi,

I have this query:

SELECT q10.q10_count,
vi.mgr_flag,vi.inst_type,vi.inst_num,vi.inst_cst,v i.inst_zip,vi.inst_phone,vi.url,vi.turnover,vi.hol d_cnt,
vi.date_,vi.inst_size,vi.inst_dchg,

vh.ticker,vh.q0_shares,vh.q1_shares,vh.q0q1_grwth, vh.q0q1_dchg,vh.shares_out,
mt.comp_name, sd.sector_name, p.closing
FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices p,
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10
WHERE inst_name = 'Yacktman Fund'
AND 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;

Take a look at the inner query in the FROM clause:
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10

I basically need the same criteria applied as the outer query. Do I
have to re-list the criteria? I'll have to do that count for 5
fields. It will make this query WAY long.......

I can't tell for sure, but it sounds like you just need an analytical
function in the outer query...

count(*) over (partition by vh.ticker)

....or a selective count...

sum(decode(vh.q1_shares, 0, 1, 0)) over (partition by vh.ticker)

--
Peter

#4

Mtek wrote:

Quote:

Hi,

I have this query:

SELECT q10.q10_count,
vi.mgr_flag,vi.inst_type,vi.inst_num,vi.inst_cst,v i.inst_zip,vi.inst_phone,vi.url,vi.turnover,vi.hol d_cnt,
vi.date_,vi.inst_size,vi.inst_dchg,

vh.ticker,vh.q0_shares,vh.q1_shares,vh.q0q1_grwth, vh.q0q1_dchg,vh.shares_out,
mt.comp_name, sd.sector_name, p.closing
FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices p,
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10
WHERE inst_name = 'Yacktman Fund'
AND 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;

Take a look at the inner query in the FROM clause:
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10

I basically need the same criteria applied as the outer query. Do I
have to re-list the criteria? I'll have to do that count for 5
fields. It will make this query WAY long.......

I can't tell for sure, but it sounds like you just need an analytical
function in the outer query...

count(*) over (partition by vh.ticker)

....or a selective count...

sum(decode(vh.q1_shares, 0, 1, 0)) over (partition by vh.ticker)

--
Peter

#5

Mtek wrote:

Quote:

Hi,

I have this query:

SELECT q10.q10_count,
vi.mgr_flag,vi.inst_type,vi.inst_num,vi.inst_cst,v i.inst_zip,vi.inst_phone,vi.url,vi.turnover,vi.hol d_cnt,
vi.date_,vi.inst_size,vi.inst_dchg,

vh.ticker,vh.q0_shares,vh.q1_shares,vh.q0q1_grwth, vh.q0q1_dchg,vh.shares_out,
mt.comp_name, sd.sector_name, p.closing
FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices p,
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10
WHERE inst_name = 'Yacktman Fund'
AND 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;

Take a look at the inner query in the FROM clause:
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10

I basically need the same criteria applied as the outer query. Do I
have to re-list the criteria? I'll have to do that count for 5
fields. It will make this query WAY long.......

I can't tell for sure, but it sounds like you just need an analytical
function in the outer query...

count(*) over (partition by vh.ticker)

....or a selective count...

sum(decode(vh.q1_shares, 0, 1, 0)) over (partition by vh.ticker)

--
Peter

#6

On Apr 28, 7:51 pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:

Quote:

Hi,

I have this query:

SELECT q10.q10_count,
vi.mgr_flag,vi.inst_type,vi.inst_num,vi.inst_cst,v i.inst_zip,vi.inst_phone,vi.url,vi.turnover,vi.hol d_cnt,
vi.date_,vi.inst_size,vi.inst_dchg,

vh.ticker,vh.q0_shares,vh.q1_shares,vh.q0q1_grwth, vh.q0q1_dchg,vh.shares_out,
mt.comp_name, sd.sector_name, p.closing
FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices p,
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10
WHERE inst_name = 'Yacktman Fund'
AND 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;

Take a look at the inner query in the FROM clause:
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10

I basically need the same criteria applied as the outer query. Do I
have to re-list the criteria? I'll have to do that count for 5
fields. It will make this query WAY long.......

I don't see a join with q10. Is a cross join really what you intend?

robert

#7

On Apr 28, 7:51 pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:

Quote:

Hi,

I have this query:

SELECT q10.q10_count,
vi.mgr_flag,vi.inst_type,vi.inst_num,vi.inst_cst,v i.inst_zip,vi.inst_phone,vi.url,vi.turnover,vi.hol d_cnt,
vi.date_,vi.inst_size,vi.inst_dchg,

vh.ticker,vh.q0_shares,vh.q1_shares,vh.q0q1_grwth, vh.q0q1_dchg,vh.shares_out,
mt.comp_name, sd.sector_name, p.closing
FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices p,
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10
WHERE inst_name = 'Yacktman Fund'
AND 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;

Take a look at the inner query in the FROM clause:
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10

I basically need the same criteria applied as the outer query. Do I
have to re-list the criteria? I'll have to do that count for 5
fields. It will make this query WAY long.......

I don't see a join with q10. Is a cross join really what you intend?

robert

#8

On Apr 28, 7:51 pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:

Quote:

Hi,

I have this query:

SELECT q10.q10_count,
vi.mgr_flag,vi.inst_type,vi.inst_num,vi.inst_cst,v i.inst_zip,vi.inst_phone,vi.url,vi.turnover,vi.hol d_cnt,
vi.date_,vi.inst_size,vi.inst_dchg,

vh.ticker,vh.q0_shares,vh.q1_shares,vh.q0q1_grwth, vh.q0q1_dchg,vh.shares_out,
mt.comp_name, sd.sector_name, p.closing
FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices p,
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10
WHERE inst_name = 'Yacktman Fund'
AND 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;

Take a look at the inner query in the FROM clause:
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10

I basically need the same criteria applied as the outer query. Do I
have to re-list the criteria? I'll have to do that count for 5
fields. It will make this query WAY long.......

I don't see a join with q10. Is a cross join really what you intend?

robert

#9

On Apr 28, 7:51 pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:

Quote:

Hi,

I have this query:

SELECT q10.q10_count,
vi.mgr_flag,vi.inst_type,vi.inst_num,vi.inst_cst,v i.inst_zip,vi.inst_phone,vi.url,vi.turnover,vi.hol d_cnt,
vi.date_,vi.inst_size,vi.inst_dchg,

vh.ticker,vh.q0_shares,vh.q1_shares,vh.q0q1_grwth, vh.q0q1_dchg,vh.shares_out,
mt.comp_name, sd.sector_name, p.closing
FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices p,
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10
WHERE inst_name = 'Yacktman Fund'
AND 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;

Take a look at the inner query in the FROM clause:
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10

I basically need the same criteria applied as the outer query. Do I
have to re-list the criteria? I'll have to do that count for 5
fields. It will make this query WAY long.......

I don't see a join with q10. Is a cross join really what you intend?

robert

#10

On Apr 29, 4:18 am, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:

Quote:

On Apr 28, 7:51 pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:

Hi,

I have this query:

SELECT q10.q10_count,
vi.mgr_flag,vi.inst_type,vi.inst_num,vi.inst_cst,v i.inst_zip,vi.inst_phone,vi.url,vi.turnover,vi.hol d_cnt,
vi.date_,vi.inst_size,vi.inst_dchg,

vh.ticker,vh.q0_shares,vh.q1_shares,vh.q0q1_grwth, vh.q0q1_dchg,vh.shares_out,
mt.comp_name, sd.sector_name, p.closing
FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices p,
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10
WHERE inst_name = 'Yacktman Fund'
AND 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;

Take a look at the inner query in the FROM clause:
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10

I basically need the same criteria applied as the outer query. Do I
have to re-list the criteria? I'll have to do that count for 5
fields. It will make this query WAY long.......

I don't see a join with q10. Is a cross join really what you intend?

robert

Well, I want to select a certain criteria from X number of tables. I
also want a COUNT of certain columns with their own critera. And, I'm
trying to put this all into one query.......