Databases
StackOverflow2013
Postsdbo
POSql Server 2008: Alternative for inefficient subquery in select clause
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSql Server 2008: Alternative for inefficient subquery in select clause
    primarykey
    data
    text
    <p>I have a table:</p> <p>SourceID, TargetID, Amount, Year.</p> <p>I also have a view. This view is looking at differences between a row and the row above it in the same table. I use CTE to do this:</p> <pre><code> WITH cte as (SELECT ROW_NUMBER() OVER (PARTITION BY SourceID ORDER BY ... ) as rowNum, SourceId, TargetID, Amount, Year FROM table) Select t1.SourceID as SourceID1, t2.SourceID as SourceID2, t1.TargetID as TargetID1, t2.TargetID as TargetID2, t1.Year, (t1.Amount - t2.Amount) as Difference FROM cte t1 LEFT JOIN cte t2 ON t1.SourceID = t2.SourceID and t1.rowNum = t2.rowNum+1 </code></pre> <p>I now have 2 columns in that view. TargetID1 and TargetID2. TargetID2 is the same as TargetID1 in the row above. Sometimes TargetID2 is null, because the conditions were not met in the left join displayed above. That is fine. Selecting * from this view is exactly what I need.</p> <p>The problem here is when I want to select conditionally. For example: I only want to select rows that have 2012 as year. Ofcourse technically this is not a problem, but when the first row in 2012 is comparing itself to the (now invisible) last row of 2011 the customer gets confused. They want Difference to be empty (NULL) when its the first row in the resultset meeting the join conditions.</p> <p>So I wrote this:</p> <pre><code>SELECT SourceID1, SourceID2, TargetID1, TargetID2, Year, case when TargetID2 is null then null else case when TargetID2 NOT IN ( select TargetID1 from view where Year = 2012) then null else Difference end end as Difference, FROM view where Year = 2012 </code></pre> <p>This actually worked... It took a little longer because its a fairly large table, but it was acceptable. Now we are adding a percentage (the difference in percentage). This means that this last query is expanded with another conditional select checking the exact same contion.</p> <pre><code>case when TargetID2 is null then null else case when TargetID2 NOT IN ( select TargetID1 from view where Year = 2012) then null else Percentage end end as Percentage </code></pre> <p>So this is getting very consuming.</p> <p>My question is: what should I do? Changes to the solution in general, to the view or to the query are all fine. There are no limitations whatsoever as long as the filtered result shows the first row with a null as difference and percentage and the query performs.</p> <p>Sorry for the large post. Didn't know how else to put this.</p> <p>Thanks</p> <p>EDIT:</p> <p>I was not clear enough on problem. If Year would be the only concern I could do some of the suggestions below, but its not. I simplified the table and view so it would be more readable, but there are other columns and other things one can filter on.</p> <p>There is also a month column. So if I want to see october 2011 - februari 2012 then the last row of 2011 should be compared to the first row of 2012. Here the first row of october should have the NULL Difference and Percentage columns because its TargetID2 does not exist in the TargetID1 column...</p> <p>I hope that clears it up. Maybe I should start from scratch with this question.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJon Koeter
    1. USJon Koeter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI read through this and missed what feedback you were looking for, other than it was consuming. Does it work? Is it fast enough? Use of ISNULL() and NULLIF() would help readability instead of using the case statements.
      singulars
      1. POSql Server 2008: Alternative for inefficient subquery in select clause
      1. USdecompiled
    2. COI do not understand why are you using subquery - you already select year in cte and you can use it to discover if `t2.Year` does not equal `t1.Year`. What am I missing here?
      singulars
      1. POSql Server 2008: Alternative for inefficient subquery in select clause
      1. USNikola Markovinović
    3. COIf you do filtering in `CTE` resulting t2 rows will be null for `rowNum = 1`. If you do it later you have to add the same filters to `left join` to nullify non-matching rows. I'd go with filters in `CTE`.
      singulars
      1. POSql Server 2008: Alternative for inefficient subquery in select clause
      1. USNikola Markovinović
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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