Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Where less than in SQL

    A little help needed if anyone can please explain:
    an aspx page returns all my values when I use the following where as part of my select string:
    WHERE ((accledgeryear=" & intPrevYear & " ) and (accledgerperiod =" & intPrevPeriod & " ))

    accledger year is integer field
    accledgerperiod is integer field
    intPrevYear is integer Dim
    intPrevPeriod is integer Dim

    the following where statement doesn't:
    WHERE ((accledgeryear=" & intPrevYear & " ) and (accledgerperiod <" & intPrevPeriod & " ))

    and returns an error of:
    The provider could not determine the Double value. For example, the row was just created, the default for the Double column was not available, and the consumer had not yet set a new Double value.

    I have also tried:
    WHERE ((accledgeryear=" & intPrevYear & " ) and (accledgerperiod BETWEEN 1 and 4))
    which results in same error message

    also tried
    WHERE ((accledgeryear=" & intPrevYear & " ) and (accledgerperiod < '" & cstr(intPrevPeriod) & "))
    just to confirm and it does (as expected) return a mismatch

    Any suggestions would be helpful

    TIA
    Alan

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where less than in SQL

    You're using .NET, right? (You mentioned ASPX, so I assume this is true.)

    When generating a SQL Statement, you'll want to convert all of the values to strings. Therefore, I would recommend using the following:

    "..... WHERE accledgeryear=" & intPrevYear.ToString() & " AND accledgerperiod < " & intPrevPeriod.ToString()

    This will generate something like this: "WHERE accledgeryear=2003 AND accledgerperiod <2" (depending on the variable values)

    As for errors on the database side, you'll need to verify that the SQL statement is formed correctly. Are you using SQL Server? If so, try executing the exact SQL Statement being sent by your code against the database. If it fails there, then you need to adjust your SQL.

    Hope this helps

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Where less than in SQL

    Thanks Mark, (sorry, yes .Net) I ran the code against the db and no problem there.
    The really annoying bit is when an = is used then it returns the values, but when a < is used I get the double value error.
    Tried the < but no go:
    No value given for one or more required parameters.

    I am declaring the select string by
    strSelect = "SELECT...<<fields/criteria>>.....ORDER BY ReportToName"

    TIA
    Alan

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where less than in SQL

    Are you using SQL Server? What is the data type of the field in question?

    The following statement works perfectly in SQL Server (using the Northwind database):

    SELECT * FROM Employees WHERE ReportsTo = 5 AND EmployeeID < 10

    Both ReportsTo and EmployeeID are int fields.

    Also, you indicated that the variables are integers. The error message makes it sound like they are being convered to Double somehow...?

    Does this help?

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Where less than in SQL

    Yes it does help thanks Mark.

    And I feel kind of humbled because it also works on mine too!

    The less than is working perfectly and the error lies within the rest of the code, because it is a union query .

    The query uses the same underlying table to return first the values within the period and then unions the values for previous periods.
    When both the where statements are determined to be < it works.
    When one statement is < and one is = it doesn't work.
    When both are the same it works (but of course I have same values for currnt and previous)
    I need to examine further, but just in case a fresh pair of eyes wouldn't go amiss.

    "SELECT ALL " & _
    "ClientGroup, ReportToName, SortOrder, Tmargin as TargetMargin, sum(salesy) as SalesYTD, " & _
    "sum(costY) as costYTD, " & _
    "((sum(salesy)-sum(costY))/sum(salesY))*100 as MargYTD, " & _
    "sum(salesM) as SalesMTD, " & _
    "sum(costM) as CostMTD, " & _
    "((sum(salesM)-sum(costM))/sum(salesM))*100 as MargMTD, " & _
    "sum(curr_workers) as current_workers, " & _
    "sum(prev_workers) as Previous_workers " & _
    "FROM(SELECT ALL " & _
    "ClientGroup, " & _
    "ReportToName, " & _
    "SortOrder, " & _
    "sum(sumofTargetMarg*100) as TMargin, " & _
    "sum(Sumofsales)/1000 as salesY, " & _
    "sum(sumofcost)/1000 as costY, " & _
    "sum(0) as MargY, " & _
    "sum(0) as salesM, " & _
    "sum(0) as costM, " & _
    "sum(0) as MargM, " & _
    "sum(sumofcurrentworkers) as curr_workers, " & _
    "sum(0) as prev_workers " & _
    "FROM qry_tbl_GMRDataByPeriod " & _
    "WHERE ((accledgeryear=" & intYear & ") and (accledgerperiod <" & intPeriod & ")) GROUP BY ClientGroup, ReportToName, SortOrder " & _
    "UNION SELECT ALL " & _
    "ClientGroup, " & _
    "ReportToName, " & _
    "SortOrder, " & _
    "sum(sumofTargetMarg*100) as TMargin, " & _
    "sum(0) as salesY, " & _
    "sum(0) as costY, " & _
    "sum(0) as MargY, " & _
    "(sum(Sumofsales))/1000 as salesM, " & _
    "(sum(sumofcost))/1000 as costM, " & _
    "sum(0) as MargM, " & _
    "sum(0) as curr_workers, " & _
    "sum(sumofcurrentworkers) as prev_workers " & _
    "FROM qry_tbl_GMRDataByPeriod " & _
    "WHERE ((accledgeryear=" & intPrevYear & " ) and (accledgerperiod <" & intPeriod & ")) GROUP BY ClientGroup, ReportToName, SortOrder) " & _
    "GROUP BY ReportToName, SortOrder, TMargin, ClientGroup ORDER BY SortOrder, ReportToName"

    where the final WHERE statement should be:
    "WHERE ((accledgeryear=" & intPrevYear & " ) and (accledgerperiod =" & intPeriod & ")) GROUP BY ClientGroup, ReportToName, SortOrder) " & _
    Cheers
    Alan

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where less than in SQL

    I'm not sure if this will do the trick, but here's my first thought.

    Try building the Union as an inner SQL statement, including the accledgeryear and accledgerperiod fields including ALL records (no WHERE clause).

    Then wrap that statement with a Select * FROM (<previously mentioned SQL>) WHERE accledgeryear = ... AND accledgerperiod < ... GROUP BY... ORDER BY...

    Hopefully that will do the trick!

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Where less than in SQL

    Many thanks Mark, I have something to go at before the directors meeting.
    I think the error has been occuring because it went onto an erro page, and I've been trying it constantly and the indexing service is returning the same error page each time I try.
    I've turned indexing off and it appears to have had a desired effect - for now.
    Rebooted and all appears OK.

    My union was also presenting error with respect to the grouping of the TMargin values.

    Cheers
    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •