Pass Value To Derived Table

Question

Is there a way to pass a value to a derived table query?

In the derived table I want reference a value ([docSVsys].[sID]) from the outer query.

I get an error:

Msg 4104, Level 16, State 1, Line 7 The multi-part identifier "docSVsys.sID" could not be bound.

Yes I know this query can be simplified to no loop.
Have a cursor that has to loop and trying to convert it so set.

select top 10 [docSVsys].[sID], [sI].[count]
from docSVsys 
join 
(
    select count(*) as [count]   
    from docSVenum1 as [sIt]
    where  [sIt].[sID] = [docSVsys].[sID] 
) as [sI] 
on  '1' = '1'
order by [docSVsys].[sID]

Cross apply seemed to do the trick. And it is exactly 1/3 faster than the cursor version. Real query using cross apply below.

SELECT [sO].[sID], [sI].[max], [sI].[avg], [sI].[stdev]
FROM docSVsys as [sO] with (nolock)
cross apply 
( 
    select [sO].[sID], max(list.match) as 'max', avg(list.match) as 'avg', stdev(list.match) as 'stdev'
    from
    (
        select #SampleSet.[sID], [match] = 200 * count(*) / CAST ( #SampleSetSummary.[count] + [sO].[textUniqueWordCount]  as numeric(8,0) ) 
        from #SampleSet with (nolock) 
        join FTSindexWordOnce as [match] with (nolock) -- this is current @sID
            on match.wordID  = #SampleSet.wordID
            and [match].[sID] = [sO].[sID]
        join #SampleSetSummary with (nolock)  -- to get the word count from the sample set
            on #SampleSetSummary.[sID] = #SampleSet.[sID] 
        group by #SampleSet.[sID], #SampleSetSummary.[count] 
    ) as list
    having max(list.match) > 60 
) as [sI]  
where [textUniqueWordCount] is not null and [textUniqueWordCount] > 4 and [sO].[sID] <= 10686
order by [sO].[sID]

score 3 · Accepted Answer · answered Jul 26 '12 at 20:46

3

You can do what you want with a CROSS APPLY rather than a JOIN:

select top 10 [docSVsys].[sID], [sI].[count] 
from docSVsys  
cross apply 
( 
    select count(*) as [count]    
    from docSVenum1 as [sIt] 
    where  [sIt].[sID] = [docSVsys].[sID]  
) as [sI]  
order by [docSVsys].[sID]

answered Jul 26 '12 at 20:46

GilM

3,711
17
18

Yes it's implemented in a looping fashion, but that was what was asked for. Whether or not it performs better than the join with the group by query depends on the sizes of the tables, indexes, etc. Another option might be to execute the group by query into a temp table so that you could have an index on sID. I'd say test the various options and use the one that performs best. – GilM Jul 26 '12 at 21:30
Awesome now I get cross apply – paparazzo Jul 26 '12 at 22:16
That's great. Some people think it's only for table-valued functions, but it can be used with arbitrary queries that reference columns from prior tables, as well. – GilM Jul 26 '12 at 22:57

score 2 · Answer 2 · answered Jul 26 '12 at 20:32

2

Add the ID to the derived table, and join on that:

select top 10 [docSVsys].[sID], [sI].[count]
from docSVsys 
join 
(
     select [sIt].[sID], count(*) as [count]   
     from docSVenum1 as [sIt]
     group by [sIt].[sID]
) as [sI] 
on [sI].[sID] = [docSVsys].[sID] 
order by [docSVsys].[sID]

answered Jul 26 '12 at 20:32

LittleBobbyTables - Au Revoir

32,008
25
109
114

Yes that is alternate syntax. But I absolutely have to loop ONE [docSVsys].[sID] at a time. In real life if that inner loop generated all the rows once it would be several TB. That inner loop evaluates about 1 M rows at a time to generate one output row. And the inner loop is called about 1 M times. This may be just stay with a cursor. As a cursor it runs 8 hours (with a small memory footprint). – paparazzo Jul 26 '12 at 21:10

Pass Value To Derived Table

2 Answers2