Derived Table? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Derived Table?

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-06-03 : 12:55:19

Isn't this a derived table? A subquery which I use to produce columns, however many, grouped, with whichever criteria, produced from any number of joined tables... it comes up with lists of associated values, like a table, which I need to be picked up by a bigger query around it. It runs fine as it is. Why can't I do a select * from it?

(select cp.CNPDateCreated, count(cp.CNPDateCreated)
from asdandat.dbo.tbl_centre c
INNER JOIN asdandat.dbo.tbl_net_centre nc ON c.centre_id = nc.centre_ID
inner join asdandat.dbo.tbl_candidates can on can.canCentreID = nc.Centre_ID
inner join asdandat.dbo.tbl_candidatesprogrammes cp on can.CANCandidateID = cp.CNPCandidateID
inner join asdandat.dbo.tbl_programmes p on p.PRGProgrammeCode = cp.CNPProgrammeCode
where c.Reg_Status = 'R'
group by cp.CNPDateCreated
having count(cp.CNPDateCreated)>5)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 13:00:59

its a derived table and you can select from it. just use


SELECT * FROM (select cp.CNPDateCreated, count(cp.CNPDateCreated)
from asdandat.dbo.tbl_centre c
INNER JOIN asdandat.dbo.tbl_net_centre nc ON c.centre_id = nc.centre_ID 
inner join asdandat.dbo.tbl_candidates can on can.canCentreID = nc.Centre_ID
inner join asdandat.dbo.tbl_candidatesprogrammes cp on can.CANCandidateID = cp.CNPCandidateID
inner join asdandat.dbo.tbl_programmes p on p.PRGProgrammeCode = cp.CNPProgrammeCode
where c.Reg_Status = 'R'
group by cp.CNPDateCreated
having count(cp.CNPDateCreated)>5) t

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-03 : 13:02:25

>>Why can't I do a select * from it?
good question - what happens when you try? You probably don't know that you need to give you derived table an alias:

select * from
(
<your sql statement>
) as myDerivedTb

Be One with the Optimizer
TG

edit:

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-06-04 : 04:52:41

This is great. I had this answer before, but it tends to be the syntax which I struggle with. I had to name the second column as well. That's fine. Now it's going well as a practical lone entity:

select * from
(select cp.CNPDateCreated, count(cp.CNPDateCreated) count
from asdandat.dbo.tbl_centre c
INNER JOIN asdandat.dbo.tbl_net_centre nc ON c.centre_id = nc.centre_ID
inner join asdandat.dbo.tbl_candidates can on can.canCentreID = nc.Centre_ID
inner join asdandat.dbo.tbl_candidatesprogrammes cp on can.CANCandidateID = cp.CNPCandidateID
inner join asdandat.dbo.tbl_programmes p on p.PRGProgrammeCode = cp.CNPProgrammeCode
where c.Reg_Status = 'R'
group by cp.CNPDateCreated
having count(cp.CNPDateCreated)>5) candidates_per_date

Now, needing to integrate this, given this error, why am I restricted to one expression?

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Derived table put at the end of the column list...

SELECT --distinct
Centre_Name, ntwrk_cd, det.det_quantity, det_year, prc.pr_sort_key, s.stk_sort_key, cp.CNPDateCreated, can.CANCandidateRef,
(select cp.CNPDateCreated, count(cp.CNPDateCreated) count
from asdandat.dbo.tbl_centre c
INNER JOIN asdandat.dbo.tbl_net_centre nc ON c.centre_id = nc.centre_ID
inner join asdandat.dbo.tbl_candidates can on can.canCentreID = nc.Centre_ID
inner join asdandat.dbo.tbl_candidatesprogrammes cp on can.CANCandidateID = cp.CNPCandidateID
inner join asdandat.dbo.tbl_programmes p on p.PRGProgrammeCode = cp.CNPProgrammeCode
where c.Reg_Status = 'R'
group by cp.CNPDateCreated
having count(cp.CNPDateCreated)>5) candidates_per_date
FROM asdandat.dbo.tbl_centre c
INNER JOIN asdandat.dbo.tbl_net_centre nc ON c.centre_id = nc.centre_ID
inner join asdandat.dbo.tbl_candidates can on can.canCentreID = nc.Centre_ID -- error joining on centreNo
inner join asdandat.dbo.tbl_candidatesprogrammes cp on can.CANCandidateID = cp.CNPCandidateID
inner join asdandat.dbo.tbl_programmes p on p.PRGProgrammeCode = cp.CNPProgrammeCode
inner join financetemplate.dbo.sl_accounts sla on c.centre_no = sla.cucode
inner join financetemplate.dbo.sl_pl_nl_detail det on det.det_account = sla.cucode
left join financetemplate.dbo.prc_price_recs prc on prc.prcode = det.det_price_code
left join financetemplate.dbo.stk_stock s on s.stkcode = det_stock_code
WHERE c.Reg_Status = 'R'
and (prc.pr_sort_key = 'CANDIDATE FEES'
or s. stk_sort_key in ('STUDENT BOOKS','RESOURCE BOOKLETS'))
and det.det_quantity > 5 -- I think this is better, rather than aggregating on it
and det_year in ('C','L')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 05:01:39

Your derived table cannot be used as a column.
Use it as if candidates_per_date is a table.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-04 : 13:03:36

seems like what you're looking for is this


SELECT --distinct
Centre_Name, ntwrk_cd, det.det_quantity, det_year, prc.pr_sort_key, s.stk_sort_key, cp.CNPDateCreated, can.CANCandidateRef,
candidates_per_date.count
FROM asdandat.dbo.tbl_centre c
INNER JOIN asdandat.dbo.tbl_net_centre nc ON c.centre_id = nc.centre_ID 
inner join asdandat.dbo.tbl_candidates can on can.canCentreID = nc.Centre_ID -- error joining on centreNo
inner join asdandat.dbo.tbl_candidatesprogrammes cp on can.CANCandidateID = cp.CNPCandidateID
inner join asdandat.dbo.tbl_programmes p on p.PRGProgrammeCode = cp.CNPProgrammeCode
inner join financetemplate.dbo.sl_accounts sla on c.centre_no = sla.cucode
inner join financetemplate.dbo.sl_pl_nl_detail det on det.det_account = sla.cucode
inner join(select cp.CNPDateCreated, count(cp.CNPDateCreated) count
from asdandat.dbo.tbl_centre c
INNER JOIN asdandat.dbo.tbl_net_centre nc ON c.centre_id = nc.centre_ID 
inner join asdandat.dbo.tbl_candidates can on can.canCentreID = nc.Centre_ID
inner join asdandat.dbo.tbl_candidatesprogrammes cp on can.CANCandidateID = cp.CNPCandidateID
inner join asdandat.dbo.tbl_programmes p on p.PRGProgrammeCode = cp.CNPProgrammeCode
where c.Reg_Status = 'R'
group by cp.CNPDateCreated
having count(cp.CNPDateCreated)>5) candidates_per_date
on candidates_per_date.CNPDateCreated=cp.CNPDateCreated
left join financetemplate.dbo.prc_price_recs prc on prc.prcode = det.det_price_code
left join financetemplate.dbo.stk_stock s on s.stkcode = det_stock_code
WHERE c.Reg_Status = 'R'
and (prc.pr_sort_key = 'CANDIDATE FEES'
or s. stk_sort_key in ('STUDENT BOOKS','RESOURCE BOOKLETS'))
and det.det_quantity > 5 -- I think this is better, rather than aggregating on it
and det_year in ('C','L')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources