Derived Tables - 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 2000 Forums

Transact-SQL (2000)

Derived Tables

Author

Topic

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2005-07-21 : 12:25:58

Can anyone direct me to the rules for derived tables.

I have inherited a complex stored procedure that uses multiple derived tables. Is there a way to select from a derived table as you would normally select from a table. When I attempt this, I get a message similar to this one:

The column prefix 'Combined' does not match with a table name or alias name used in the query.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-07-21 : 12:26:54

Can you post the SQL statement?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2005-07-21 : 12:56:08

I've pasted this rather complex code below. What I have been trying (unsuccessfully for 2 days) to do is change the logic in this line of code

"cast(min(DataSetTract.Total_Count) as decimal(18,5))/(select TotalCount from #TotalSetCount Where #TotalSetCount.chrRANDName=FillerFormat.OSA) as TotalPlats_Tract_Percent,"

which is in the FillerFormat Derived Table. The code currently gets a total of all records the temp table #TotalSetCount and uses this as the basis for the division. I need to modify this to only get the records from the DataSetTract query where FillerFormat.chrSortOrder=6 and use this as the basis for the division (starting with Select TotalCount from..........)

I am probably missing something obvious, but I cannot figure out how to accomplish this.

/*Temporary Domain Table*/
Select Domain.IntRevenueCode,
case when Domain.chrRevType = '01' and Domain.bolDomainCDV=0 and Domain.monLoanAmount <= 1000000 then Domain.chrRevType
when Domain.chrRevType = '02' and Domain.bolDomainCDV=0 and Domain.monLoanAmount <= 500000 then Domain.chrRevType
when Domain.chrRevType between '01' and '09' and Domain.bolDomainCOM=0 and Domain.bolDomainCDV=1 then '11'
when Domain.chrRevType between '01' and '09' and Domain.bolDomainCOM=1 and Domain.bolDomainCDV=1 then '10'
when Domain.chrRevType between '03' and '09' and Domain.bolDomainCDV=0 then Domain.chrRevType
when Domain.chrRevType = '98' and Domain.bolDomainCDV=1 then '10'
When Domain.chrRevType = '99' and Domain.bolDomainCOM=0 then '11'
When Domain.chrRevType = '99' and Domain.bolDomainCOM=1 then '10'
end as chrRevType,
Domain.MonLoanAmount,
Domain.BolDomaincdv,
Domain.BolDomainCOM,
Domain.chrDomainal,
Domain.chrappno,
Domain.fkRANDcode,
Domain.pkDomainguid,
Domain.fkactiontype,
Domain.fkFillerEntirety

into #Domain from Tbl_Domain as Domain
left join tbl_Filler_key as rkey
on RKey.fkGuidKey=Domain.pkDomainGuid
AND RKey.fkFiller=@fkFiller and RKey.fkUser=@fkUser

where (Domain.chrDomainAL='1' or Domain.chrDomainAL = '2') and (Domain.fkActionType='1' or Domain.fkActionType='6')
and Domain.fkFillerEntirety=@fkFillerEntirety and intRecycleBin=0
and Domain.FKFillerEntirety = @FKFillerEntirety and Domain.intRecycleBin=0
and ((RKey.fkUser=@fkUser and RKey.fkFiller=@fkFiller) or
@xintRouteTrigger=0)

CREATE INDEX IXDomain ON #Domain(chrRevType)

/*Lender Associated FillerEntirety TempTable*/
select distinct re.pkFillerEntirety
into #LenderREList
from tbl_Filler_Entirety as re
inner join tbl_RAND_Area as RANDarea
on re.chrRespondentID=RANDarea.chrRespondentID
and re.fkFillerYear=RANDArea.fkFillerYear
where RANDArea.chrRespondentID in (Select chrRespondentID from tbl_Filler_Entirety where pkFillerEntirety=@fkFillerEntirety)
and re.pkFillerEntirety<>@fkFillerEntirety

/*Temporary Lender Table*/

Select Lender.intRevenuecode,
Lender.bolDomainCOM,
Lender.bolDomaincdv,
Lender.ChrAppNo,
Lender.MonLoanAmount,
Lender.fkRANDCode,
Lender.pkLenderGuid,
Lender.fkFillerEntirety

into #Lender

from Tbl_Lender as Lender (index = IX_tbl_Lender)
where (Lender.chrDomainAL='1'or Lender.chrDomainAL = '2') and (bolDomainCOM=1 or bolDomainCDV=1) and Lender.fkActionType='1'
and Lender.fkFillerEntirety in (Select pkFillerEntirety from #LenderREList)

Select distinct 'Total' as chrRANDName,
fkRANDCode
into #TotalSet
from #Domain as Domain

/*Sums for the Percentages*/
select TotalSumCount.chrRANDName,
cast(nullif(Sum(TotalSumCount.TotalCount),0) as decimal(18,5)) as TotalCount,
cast(nullif(sum(TotalSumCount.TotalDollars),0) as decimal(18,5)) as TotalDollars
into #TotalSetCount
From(
select #TotalSet.chrRANDName,
cast(nullif(Count(Domain.chrAppNo),0) as decimal(18,5)) as TotalCount,
cast(nullif(sum(Domain.monLoanAmount),0) as decimal(18,5)) as TotalDollars
from #Domain as Domain
inner join #TotalSet on Domain.fkRANDCode=#TotalSet.fkRANDCode
group by #TotalSet.chrRANDName
union
select #TotalSet.chrRANDName,
cast(nullif(Count(Lender.chrAppNo),0) as decimal(18,5)),
cast(nullif(sum(Lender.monLoanAmount),0) as decimal(18,5))
from #Lender as Lender
inner join #TotalSet on Lender.fkRANDCode=#TotalSet.fkRANDCode

group by #TotalSet.chrRANDName
) as TotalSumCount
group by TotalSumCount.chrRANDName
/*Reference table to join tbl_RANDCode and tbl_RAND_Val_Dem*/
select distinct gc.pkRANDCode as fkRANDCode,
ValDem.chrHILOModCode as chrHILOModCode
into #RANDRef
from tbl_RANDCode as gc
inner join tbl_RAND_Val_Dem as ValDem
on ValDem.pkRAND=gc.fkRAND
inner join #Domain as Domain
on Domain.fkRANDCode=gc.pkRANDCode

/*Filler Output*/
/*Head SQL Statment*/
Select distinct FillerFormat.intlinking as linking,
FillerFormat.chrFillerType,
FillerFormat.OSA,
FillerFormat.chrCategory,
FillerFormat.chrItem,
FillerFormat.chrSortOrder,
sum(DataSetTract.Total_Count) as TotalPlats_Tract_Count,
cast(min(DataSetTract.Total_Count) as decimal(18,5))/(select TotalCount from #TotalSetCount Where #TotalSetCount.chrRANDName=FillerFormat.OSA) as TotalPlats_Tract_Percent,
sum(DataSetTract.Total_Dollars) as TotalPlats_Tract_Dollars,
cast(min(DataSetTract.Total_Dollars) as decimal(18,5))/(select TotalDollars from #TotalSetCount Where #TotalSetCount.chrRANDName=FillerFormat.OSA) as TotalPlats_Tract_Dollars_Percent,
Sum(DataSetBorrower.Total_Count) as Borrower_Count,
cast(min(DataSetBorrower.Total_Count) as decimal(18,5))/(select TotalCount from #TotalSetCount Where #TotalSetCount.chrRANDName=FillerFormat.OSA) as Borrower_Percent,
Sum(DataSetBorrower.Total_Dollars) as Borrower_Dollars,
cast(min(DataSetBorrower.Total_Dollars) as decimal(18,5))/(select TotalDollars from #TotalSetCount Where #TotalSetCount.chrRANDName=FillerFormat.OSA) as Borrower_Dollars_Percent
from
(/*Row Format and Organization*/
select distinct rformat.intLinking,
#TotalSet.chrRANDName as OSA,
rformat.chrFillerType,
rformat.chrCategory,
rformat.chrItem,
rformat.chrSortOrder
from #TotalSet,
tbl_Filler_Format as rformat
left join tbl_Loan_Type on rformat.intlinking=cast((case when tbl_Loan_Type.chrRevType='98' then '10'
when tbl_Loan_Type.chrRevType='99' then '11' else tbl_Loan_Type.chrRevType end) as int)
where rformat.chrRelatedView='rsp_Domain_DILCT'
group by rformat.intLinking,
#TotalSet.chrRANDName ,
rformat.chrFillerType,
rformat.chrCategory,
rformat.chrItem,
rformat.chrSortOrder
union
select distinct rformat.intLinking,
'Total' as OSA,
rformat.chrFillerType,
rformat.chrCategory,
rformat.chrItem,
rformat.chrSortOrder
from tbl_Filler_Format as rformat
left join tbl_Loan_Type on rformat.intlinking=cast((case when tbl_Loan_Type.chrRevType='98' then '10'
when tbl_Loan_Type.chrRevType='99' then '11' else tbl_Loan_Type.chrRevType end) as int)
where rformat.chrRelatedView='rsp_Domain_DILCT'
group by rformat.intLinking,
rformat.chrFillerType,
rformat.chrCategory,
rformat.chrItem,
rformat.chrSortOrder
) as FillerFormat
left join
(/*Revenue Talley Header*/
Select intRevenueCode,
chrRevType,
OSA,
count(Total_Count) as Total_Count,
sum(Total_Dollars) as Total_Dollars
from (/*Domain Revenue Borrow record data assignment*/
/*Domain Rows 1-5*/
select case when Domain.intRevenueCode<>0 then cast(Domain.intRevenueCode as char(1))
when Domain.intRevenueCode=0 then '5' end as intRevenueCode,
chrRevType,
#TotalSet.chrRANDName as OSA,
Domain.chrAppNo as Total_Count,
Domain.monLoanAmount as Total_Dollars
from #Domain as Domain
inner join #TotalSet on Domain.fkRANDCode=#TotalSet.fkRANDCode

union
/*Domain Row 6*/
select '6' as intRevenueCode,
chrRevType,
#TotalSet.chrRANDName as OSA,
Domain.chrAppNo as Total_Count,
Domain.monLoanAmount as Total_Dollars
from #Domain as Domain
inner join #TotalSet on Domain.fkRANDCode=#TotalSet.fkRANDCode

union
/*Domain Row 1-5 Section 12*/
select case when Domain.intRevenueCode<>0 then cast(Domain.intRevenueCode as char(1))
when Domain.intRevenueCode=0 then '5' end as intRevenueCode,
'12' as chrRevType ,
#TotalSet.chrRANDName as OSA,
Domain.chrAppNo as Total_Count,
Domain.monLoanAmount as Total_Dollars
from #Domain as Domain
inner join #TotalSet on Domain.fkRANDCode=#TotalSet.fkRANDCode

union
/*Domain Row 6 Section 12*/
select '6' as intRevenueCode,
'12' as chrRevType ,
#TotalSet.chrRANDName as OSA,
Domain.chrAppNo as Total_Count,
Domain.monLoanAmount as Total_Dollars
from #Domain as Domain
inner join #TotalSet on Domain.fkRANDCode=#TotalSet.fkRANDCode

union
/*Lender Row 1-5 */
Select case when Lender.intRevenueCode<>0 then cast(Lender.intRevenueCode as char(1))
else '5' end as intRevenueCode,
case when bolDomainCOM=1 then '10'
when bolDomainCDV=1 and bolDomainCOM=0 then '11' end as chrRevType,
#TotalSet.chrRANDName as OSA,
Lender.chrAppNo as Total_Count,
Lender.monLoanAmount as Total_Dollars
from #Lender as Lender
inner join #TotalSet on Lender.fkRANDCode=#TotalSet.fkRANDCode

union
/*Lender Row 6*/
Select '6' as intRevenueCode,
case when bolDomainCOM=1 then '10'
when bolDomainCDV=1 and bolDomainCOM=0 then '11' end as chrRevType,
#TotalSet.chrRANDName as OSA,
Lender.chrAppNo as Total_Count,
Lender.monLoanAmount as Total_Dollars
from #Lender as Lender
inner join #TotalSet on Lender.fkRANDCode=#TotalSet.fkRANDCode

union
/*Lender Row 1-5 Section 12*/
Select case when Lender.intRevenueCode<>0 then cast(Lender.intRevenueCode as char(1))
else '5' end as intRevenueCode,
'12' as chrRevType,
#TotalSet.chrRANDName as OSA,
Lender.chrAppNo as Total_Count,
Lender.monLoanAmount as Total_Dollars
from #Lender as Lender
inner join #TotalSet on Lender.fkRANDCode=#TotalSet.fkRANDCode

union
/*Lender Row 6 Section 12*/
Select '6' as intRevenueCode,
'12' as chrRevType,
#TotalSet.chrRANDName as OSA,
Lender.chrAppNo as Total_Count,
Lender.monLoanAmount as Total_Dollars
from #Lender as Lender
inner join #TotalSet on Lender.fkRANDCode=#TotalSet.fkRANDCode

) as Combined
group by intRevenueCode,chrRevType,OSA
)as DataSetBorrower
on FillerFormat.chrSortOrder=DataSetBorrower.intRevenueCode
and FillerFormat.intLinking=DataSetBorrower.chrRevType
and FillerFormat.OSA=DataSetBorrower.OSA
left join
(/*HILOModCode Talley Header*/
Select chrHILOModCode,
chrRevType,
OSA,
count(Total_Count) as Total_Count,
sum(Total_Dollars) as Total_Dollars
from(/*Domain HILOModCode Borrow record data assignment*/
/*Domain Rows 1-5*/
select case when #RANDRef.chrHILOModCode<>0 then cast(#RANDRef.chrHILOModCode as char(1))
when #RANDRef.chrHILOModCode=0 or #RANDRef.chrHILOModCode is null then '5' end as chrHILOModCode,
chrRevType,
#TotalSet.chrRANDName as OSA,
Domain.chrAppNo as Total_Count,
Domain.monLoanAmount as Total_Dollars
from #Domain as Domain
left join #RANDRef on Domain.fkRANDCode=#RANDRef.fkRANDCode
inner join #TotalSet on Domain.fkRANDCode=#TotalSet.fkRANDCode

union
/*Domain Rows 6*/
select '6' as chrHILOModCode,
chrRevType,
#TotalSet.chrRANDName as OSA,
Domain.chrAppNo as Total_Count,
Domain.monLoanAmount as Total_Dollars
from #Domain as Domain
inner join #TotalSet on Domain.fkRANDCode=#TotalSet.fkRANDCode

union
/*Domain Rows 1-5 section 12*/
select case when #RANDRef.chrHILOModCode<>0 then cast(#RANDRef.chrHILOModCode as char(1))
when #RANDRef.chrHILOModCode=0 or #RANDRef.chrHILOModCode is null then '5' end as chrHILOModCode,
'12' as chrRevType ,
#TotalSet.chrRANDName as OSA,
Domain.chrAppNo as Total_Count,
Domain.monLoanAmount as Total_Dollars
from #Domain as Domain
left join #RANDRef on Domain.fkRANDCode=#RANDRef.fkRANDCode
inner join #TotalSet on Domain.fkRANDCode=#TotalSet.fkRANDCode

union
/*Domain Rows 6 section 12*/
select '6' as chrHILOModCode,
'12' as chrRevType ,
#TotalSet.chrRANDName as OSA,
Domain.chrAppNo as Total_Count,
Domain.monLoanAmount as Total_Dollars
from #Domain as Domain
inner join #TotalSet on Domain.fkRANDCode=#TotalSet.fkRANDCode

union
/*Lender Rows 1-5*/
Select case when #RANDRef.chrHILOModCode<>0 then cast(#RANDRef.chrHILOModCode as char(1))
else '5' end as chrHILOModCode,
case when bolDomainCOM=1 then '10'
when bolDomainCDV=1 and bolDomainCOM=0 then '11' end as chrRevType,
#TotalSet.chrRANDName as OSA,
Lender.chrAppNo as Total_Count,
Lender.monLoanAmount as Total_Dollars
from #Lender as Lender
inner join #TotalSet on Lender.fkRANDCode=#TotalSet.fkRANDCode
left join #RANDRef on Lender.fkRANDCode=#RANDRef.fkRANDCode

union
/*Lender Rows 6*/
Select '6'as chrHILOModCode,
case when bolDomainCOM=1 then '10'
when bolDomainCDV=1 and bolDomainCOM=0 then '11' end as chrRevType,
#TotalSet.chrRANDName as OSA,
Lender.chrAppNo as Total_Count,
Lender.monLoanAmount as Total_Dollars
from #Lender as Lender
inner join #TotalSet on Lender.fkRANDCode=#TotalSet.fkRANDCode
union
/*Lender Rows 1-5 section 12*/
Select case when #RANDRef.chrHILOModCode<>0 then cast(#RANDRef.chrHILOModCode as char(1))
else '5' end as chrHILOModCode,
'12' as chrRevType,
#TotalSet.chrRANDName as OSA,
Lender.chrAppNo as Total_Count,
Lender.monLoanAmount as Total_Dollars
from #Lender as Lender
inner join #TotalSet on Lender.fkRANDCode=#TotalSet.fkRANDCode
left join #RANDRef on Lender.fkRANDCode=#RANDRef.fkRANDCode
union
/*Lender Rows 6 section 12*/
Select '6' as chrHILOModCode,
'12' as chrRevType,
#TotalSet.chrRANDName as OSA,
Lender.chrAppNo as Total_Count,
Lender.monLoanAmount as Total_Dollars
from #Lender as Lender
inner join #TotalSet on Lender.fkRANDCode=#TotalSet.fkRANDCode
) as Combined
group by chrHILOModCode,
chrRevType,
OSA
) as DataSetTract
on FillerFormat.chrSortOrder=chrHILOModCode
and FillerFormat.intLinking=DataSetTract.chrRevType
and FillerFormat.OSA=DataSetTract.OSA
group by FillerFormat.intlinking,
FillerFormat.chrFillerType,
FillerFormat.OSA,
FillerFormat.chrCategory,
FillerFormat.chrItem,
FillerFormat.chrSortOrder

Order by intlinking
/*Drop TempTables*/
drop table #TotalSetCount
drop table #TotalSet
drop table #RANDRef
drop table #LenderREList
drop table #Lender
drop table #Domain

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-21 : 13:58:47

The "Rule" for derived tables is simple:

1) The dervied table must be a valid SELECT statement
2) The derived table itself must have an alias
3) Outside of the derived table, you can only access columns that the derived table explicitly names and returns.

i.e.,

if tableA contains col1 and col2, then this is not legal:

select col2
from
(select col1 from tableA) tmp

Because Col2 was not returned by the derived table. The derived table only exposes col1.

Also, note that this is not legal:

select tableA.col1
from
(select col1 from TableA) tmp

Because TableA.Col1 makes no sense in the context -- TableA is not available to the outside SELECT; the only thing made available is the derived table aliased as "tmp" and the one column it returns, which is "col1". The outer (main) SELECT statement has no idea where Col1 comes from, it only knows that it comes from the derived SELECT statement with an alias of "tmp".

Does this help?

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources