none
null from Sum in a subquery RRS feed

  • Question


  • If I do an in-memory query on a numeric array:

     

    Dim mynums() As Integer = {1, 2, 3, 4, 5}
    Dim result = From i In mynums _
               Select New With {i, .startpos = (From x In mynums _
                                               Where x < i _
                                              Select x).Sum() + 1}

     

    which will return the expected result of:

    1 1
    2 2
    3 4
    4 7
    5 11


    But, if I create a sql server table to emulate this and do the following..

     

    Dim result = from i in DC.nynums _
               select new with {.seq = i.seq, _
                                .startpos = (from x in DC.mynums _
                                            where x.seq < i.seq _
                                           select x.seq).Sum() + 1}

     

    I unfortunately get:

     

    1 nothing
    2 2
    3 4
    4 7
    5 11

     

    How can I get the subselect to actually return a value when it doesn't find any rows to build the sum ?

    Friday, October 31, 2008 3:45 PM

Answers

  • Ok, I think I have it now..

     

    I found GetValueOrDefault, which as the name suggests, get the value or returns a default value..

     

    Dim Result = from i in DC.Mynums _
     select new with
    {.seq = i.seq, _
                      .startpos = 
    (from x in
    DC.Mynums _
                                  where
    x.seq < i.seq _
                                 select x.seq).Sum().GetValueOrDefault(0) + 1}

     

    however, this gets upset with: 'GetValueOrDefault' is not a member of 'Integer'

     

    So you then have to cast the seq column to nullable..

     

        select new integer?(x.seq)).Sum().GetValueOrDefault(0) + 1}

     

    Then I get the expected results.

    Monday, November 3, 2008 11:16 AM

All replies

  • HI

     

    Code Snippet

    var result = (from i in db.Numbers

    select new MyNumber

    {

    Code = i.Code,

    Pluss = (from x in db.Numbers

    where x.Code < i.Code

    select x.Code).Count() == 0 ? i.Code : (from x in db.Numbers

    where x.Code < i.Code

    select x.Code).Sum() + 1

    }).ToList();

     

     

    Here is generated query in sql

     

    Code Snippet

    SELECT [t0].[Code],

    (CASE

    WHEN ((

    SELECT COUNT(*)

    FROM [dbo].[Numbers] AS [t1]

    WHERE [t1].[Code] < [t0].[Code]

    )) = 0 THEN [t0].[Code]

    ELSE ((

    SELECT SUM([t2].[Code])

    FROM [dbo].[Numbers] AS [t2]

    WHERE [t2].[Code] < [t0].[Code]

    )) + 1

    END) AS [Pluss]

    FROM [dbo].[Numbers] AS [t0]

     

     

    Sunday, November 2, 2008 12:22 PM
    Answerer
  • Ok, I think I have it now..

     

    I found GetValueOrDefault, which as the name suggests, get the value or returns a default value..

     

    Dim Result = from i in DC.Mynums _
     select new with
    {.seq = i.seq, _
                      .startpos = 
    (from x in
    DC.Mynums _
                                  where
    x.seq < i.seq _
                                 select x.seq).Sum().GetValueOrDefault(0) + 1}

     

    however, this gets upset with: 'GetValueOrDefault' is not a member of 'Integer'

     

    So you then have to cast the seq column to nullable..

     

        select new integer?(x.seq)).Sum().GetValueOrDefault(0) + 1}

     

    Then I get the expected results.

    Monday, November 3, 2008 11:16 AM