Where less than in SQL

[ase001]

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

[MarkJ]

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

[ase001]

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

[MarkJ]

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?

[ase001]

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

[MarkJ]

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!

[ase001]

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