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
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.
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**
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.
