StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCorrelated join with outer fields as parameters
text
Body
copied!<p>I need a way to join a table with a function's results. I'm not sure if it's possible and somehow I don't think it's a good idea. Let me try and explain the situation.</p> <p>There is a table [Entities]:</p> <pre><code>[ID] [Description] [kWh] [kVArh] [kVAh] [OfferID] [CustomOfferID] </code></pre> <p>Another table [Data]:</p> <pre><code>[ID] [Timestamp] [Value] </code></pre> <p>And a function:</p> <pre><code>[Calc] (@offer, @customOffer, @kWh, @kVArh, @kVAh, @dtStart, @dtEnd) </code></pre> <p>So you can see there are entities, the entities' data (usage) and a function to calculate the cost.</p> <p>I need to display the usage and cost of multiple entites:</p> <pre><code>[Description] [MWh] [MVA] [Cost] </code></pre> <p><code>[MWh]</code> will be a SUM of all the entity's data over the period; <code>[MVA]</code> will be the MAX of all the data over the period; and <code>[Cost]</code> will SUM the <code>[Cost]</code> field from the sub query (function).</p> <p>The query I figured would do the jobs looks like this:</p> <pre><code>SELECT [tc].[ID], [tc].[Desc] , SUM([kWh].[Value]) / 1000 AS [MWh] , MAX([kVAh].[Value]) / 1000 AS [MVA] , SUM([cost].[Cost]) FROM [Tree_Cost] AS [tc] INNER JOIN [Data] AS [kWh] ON [tc].[kWh] = [kWh].[ID] INNER JOIN [Data] AS [kVAh] ON [tc].[kVAh] = [kVAh].[ID] INNER JOIN ( SELECT [tc].[ID], [Cost] FROM [Calc] ([tc].[Offer_ID], [tc].[OfferCustom_ID], [tc].[kWh], [tc].[KVArh], [tc].[kVAh], @dtStart, @dtEnd) ) AS [cost] ON [tc].[ID] = [cost].[ID] WHERE [tc].[Type] = 1 AND [tc].[TypeDesc] = 'GF_K_M' AND [kWh].[Timestamp] BETWEEN @dtStart AND @dtEnd AND [kVAh].[Timestamp] BETWEEN @dtStart AND @dtEnd GROUP BY [tc].[ID], [tc].[Desc] </code></pre> <p>The real problem here is that I need to include the <code>[ID]</code> from the outer query in the result set of the inner query (function) in order to be able to join the two. Then I also need to be able to use the fields from the outer query as arguments for the inner query (function).</p> <p>This is obviously not the way seeing as the <code>[tc]</code> identifier is not recognized in the inner query. So how am I supposed to accomplish something like this?</p> <hr> <pre><code>CREATE FUNCTION [dbo].[Calc] ( \@intOffer [int] , \@intCustom [int] , \@intP [int] , \@intQ [int] , \@intS [int] , \@dtStart [datetime] , \@dtEnd [datetime] ) RETURNS TABLE ( [Entry] [nvarchar](200) NULL , [Rate] [float] NULL , [Unit] [nvarchar](50) NULL , [Reading] [float] NULL , [Cost] [float] NULL , [DDate] [nvarchar](50) NULL ) WITH EXECUTE AS CALLER AS EXTERNAL NAME [OfferCalcLite].[UserDefinedFunctions].[SqlArray] </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload