Query - Error: Expression caused an overflow

Hello!

Im running a application with a SQL CE database on a PDA (Qtek S200 and Qtek 2020i). Running the SQL CE version 3.1.

When querying the database with this question

SELECT Sum(Units) as sumUnits, tWorkReport.CreatedDate, tWorkReport.ModifiedDate FROM tWorkReport LEFT JOIN tWorkReportRow ON tWorkReport.WorkReportID = tWorkReportRow.WorkReportID WHERE 1=1 AND tWorkReport.PersonID = 160 GROUP BY tWorkReport.WorkReportID,GroupNr,ReportDate,PlantsPerUnit, tWorkReport.CreatedDate, tWorkReport.ModifiedDate ORDER BY ReportDate DESC

I get the error: Expression caused an overflow [,,, Name of function (if known),,] Native Error: 25901

A weird thing is that it has worked fine for a long time. After doing an update (adding records) to a table we got this error. The table that was updated has nothing to do with the SQL-query.

A temporary workaround for this was just write it "Sum(Convert(INT,Units) as Sumunits" but this means that Units with the format as decimal looses its decimals when converting to integer.

I came a across this error once before when doing development on the application - a workaround then was to write the sum like this " 1 - Sum(Units) - 1 as Sumunits" , this did not work in the latestest question.

I have tried breaking down the query to find where i get the error and it is the Sum function that gives this error.

Anyone else that has come across this error? Bug? Any workaround? The convert to int is just a temporary solution i want to use decimals!

Anyone? |||

Im still having this problem and havent got any solution yet. Reporting to Microsoft tomorrow.

Query - Error: Expression caused an overflow

Hello!

Im running a application with a SQL CE database on a PDA (Qtek S200 and Qtek 2020i). Running the SQL CE version 3.1.

When querying the database with this question

SELECT Sum(Units) as sumUnits, tWorkReport.CreatedDate, tWorkReport.ModifiedDate FROM tWorkReport LEFT JOIN tWorkReportRow ON tWorkReport.WorkReportID = tWorkReportRow.WorkReportID WHERE 1=1 AND tWorkReport.PersonID = 160 GROUP BY tWorkReport.WorkReportID,GroupNr,ReportDate,PlantsPerUnit, tWorkReport.CreatedDate, tWorkReport.ModifiedDate ORDER BY ReportDate DESC

I get the error: Expression caused an overflow [,,, Name of function (if known),,] Native Error: 25901

A weird thing is that it has worked fine for a long time. After doing an update (adding records) to a table we got this error. The table that was updated has nothing to do with the SQL-query.

A temporary workaround for this was just write it "Sum(Convert(INT,Units) as Sumunits" but this means that Units with the format as decimal looses its decimals when converting to integer.

I came a across this error once before when doing development on the application - a workaround then was to write the sum like this " 1 - Sum(Units) - 1 as Sumunits" , this did not work in the latestest question.

I have tried breaking down the query to find where i get the error and it is the Sum function that gives this error.

Anyone else that has come across this error? Bug? Any workaround? The convert to int is just a temporary solution i want to use decimals!

Anyone? |||

Im still having this problem and havent got any solution yet. Reporting to Microsoft tomorrow.

Query

I need a little help with this query.
I am querying the value of field, but I need to add to the logic that if
there is no record then the value = 1
This is the query as I have it:
SELECT *
FROM dbo.ParametersUsers
WHERE UserId = 1713 and ParamName = 'showpopups'
How do I tell the query that "ParamValue" = 1 if no result is returned ?
Thanks.
AleksTry,
select coalesce((select top 1 ParamValue FROM dbo.ParametersUsers where
UserId = 1713 and ParamName = 'showpopups'), 1) as ParamValue
AMB
"Aleks" wrote:

> I need a little help with this query.
> I am querying the value of field, but I need to add to the logic that if
> there is no record then the value = 1
> This is the query as I have it:
> --
> SELECT *
> FROM dbo.ParametersUsers
> WHERE UserId = 1713 and ParamName = 'showpopups'
> --
> How do I tell the query that "ParamValue" = 1 if no result is returned ?
> Thanks.
> Aleks
>
>|||Hi
Probably you are looking for this:
SELECT ISNULL(ParamValue,1), ...<other values>
FROM dbo.ParametersUsers
WHERE UserId = 1713 and ParamName = 'showpopups'
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Aleks" wrote:

> I need a little help with this query.
> I am querying the value of field, but I need to add to the logic that if
> there is no record then the value = 1
> This is the query as I have it:
> --
> SELECT *
> FROM dbo.ParametersUsers
> WHERE UserId = 1713 and ParamName = 'showpopups'
> --
> How do I tell the query that "ParamValue" = 1 if no result is returned ?
> Thanks.
> Aleks
>
>|||"Aleks" schrieb:

> I need a little help with this query.
> I am querying the value of field, but I need to add to the logic that if
> there is no record then the value = 1
> This is the query as I have it:
> --
> SELECT *
> FROM dbo.ParametersUsers
> WHERE UserId = 1713 and ParamName = 'showpopups'
> --
> How do I tell the query that "ParamValue" = 1 if no result is returned ?
> Thanks.
> Aleks
>
IF EXISTS (SELECT *
FROM dbo.ParametersUsers
WHERE UserId = 1713 and ParamName = 'showpopups')
SET @.ParamValue = 1
ELSE
SELECT @.ParamValue = ParamValue
FROM dbo.ParametersUsers
WHERE UserId = 1713 and ParamName = 'showpopups'|||Sorry - of course I meant:
IF NOT EXISTS (...sql