SQLTeam.com | Weblogs | Forums

Losing records

Not sure anyone can help me out here but I am experiencing the following problem:

When running an SQL query using Crystal Reports via my program, I am losing the same records consistently across any report that would or should put this report record.

It runs fine up to record 2019017, then fine all the way to 2019076 - skips 2019077, 2019083, 2019091 and 2019102.

I am searching for any record number with a PET_LOAD_NUMB between '2019000' and '2019107'

The missing records have been verified to be present in the database. When I run the SQL in MSSMS all the records return fine, with no error.

Every report I'm running is missing the EXACT same 5 records. Any thoughts as to why?

Please be specific. Seriously would appreciate any helpful suggestions and hints of what to look for.

What data ype is PET_LOAD_NUMB?
Are you using the same stored procedure for all reports?
Please post at least the WHERE clause here.
Most probably the missing ones are due to some tables you are joining to and.or the WHERE clause that is excluding them

Hi

Please Google search..
One of the articles may be your issue

The same procedure is being used on all relevant reports.
There are no joining of tables.

PETS_LOAD_NUMB is nvachar(10), null

where clause is very basic:

DECLARE @reportYear int = (year(getdate()) - iif(month(getdate()) = 1, 1, 0));
DECLARE @startLoadNumber int = @reportYear * 1000 + 1
, @endLoadNumber int = @reportYear * 1000 + 999;

....
WHERE PETS_LOAD_NUMB BETWEEN @startLoadNumber AND @endLoadNumber

So, in theory, any load number between 2019001-2019999 should be pulled.

Do this

WHERE PETS_LOAD_NUMB in ('missingnumberone','missingnumnertwo')

Etc for all of the missing numbers and see what returns as a test. Since that supposedly numeric column is really varchar I suspect you might have spaces somewhere in there in some data

Problem has been resolved. It seems a date field within the missing records was NULL (random occurrences) and if that date field is NULL the record does not print.

The date column in question, is supposed to be auto set for current date at record creation time then overwritten when the file import is imported which would/should overwrite the default date. Most of the time it works as designed but the occasional anomaly prevents the date from saving to the DB. Manually populating those few missing dates allowed the missing records to print.

Even though the date field is, in no way, queried for the particular record request, it did stop that record from printing if it was NULL.

:thinking::thinking::thinking::thinking: odd. Not even in the where clause

Never ceases to Amaze Me

how many different WAYS ...things don't work !!!!

Finding out WHY is a pain
I guess thats why we have JOBS ..

:slight_smile:
:slight_smile:

Wrong! Don't change the datatype you compare against, from nvarchar to int. You're forcing SQL to convert every value in the table to int before the comparison and drastically reducing the chance of an index being useful for this query. If the value is numeric, the column should actually be numeric, but since it isn't, you should compare using the data type that the column actually is, not what it should be.

DECLARE @reportYear int = (year(getdate()) - iif(month(getdate()) = 1, 1, 0));
DECLARE @startLoadNumber nvarchar(10) = CAST(@reportYear * 1000 AS nvarchar(4) + N'001'
, @endLoadNumber nvarchar(10) = CAST(@reportYear * 1000 AS nvarchar(4)) + N'999';
...
WHERE PETS_LOAD_NUMB BETWEEN @startLoadNumber AND @endLoadNumber **AND 
    LEN(PETS_LOAD_NUMB) = 7**

and to top that why is that column varchar instead of int

I have changed the PETS_LOAD_NUMB field to an INT field - Not sure why that was set to a varchar anyways. Must have been during one of my really late night nights when I was first setting this up years back.

1 Like