Report shows different number of records than SQL query

Hi Doxelec,
Please post the sql of the query as it appears in CR.

Pete

>>> The 2 records missing from the report seem to meet all of the criteria in the record selection formula.

Null values are a common issue with Crystal.  For one thing Crystal requires null checks before other conditions are checked and it doesn't behave consistently if you don't do it.  Also you can't be sure how null values that aren't explicitly checked will be handled - for example a condition {table.field} <> 0 may or may not return a record if {table.field} is null.

If that doesn't solve it then as Pete said, if you post the SQL it might help.

As the others have indicated, there are a number of issues that could cause this.  Please paste in your complete record selection criteria AND the SQL that Crystal Reports generates (Database|Show SQL Query).

Also, do you have 'convert NULL field value to default...' checked in your File|Report Options?

~Kurt

The "Convert NULL" options were not on.  Turning them on made no difference.

That reminded me to try changing the option in the record selection formula from "Exceptions For Nulls" to "Default Values For Nulls" and that also made no difference.

I suspected something to do with nulls, which is why I examined the entire row in the source table for one of the missing records, but didn't see any nulls or even any columns that seemed out of line from the data patterns in other rows in any way.  Specifically, none of the columns mentioned in the selection formula have a null, zero or zero-length value in the missing records.

The only thing I can see in common between the two missing records is that they should both be caught by the search for "*-CSP" in the mfg_part_num.  That is a condition that I recently added, and how I noticed that the additional records I expected to see in the report weren't showing up.

Here is the record selection formula:

{assocconts.assoc_type} = "KEYREP" and
{deal_1.date_created} >={?DateBegin} and
{deal_1.date_created}<={?DateEnd} and
{orderitem.po_number} > "0" and
{orderitem.item_status} <> "NPOCAN" and
{orderitem.item_status} <> "YPOCAN" and
{orderitem.item_status} <> "NTODD3" and
{orderitem.item_status} <> "NTODD2" and
{orderitem.item_status} <> "NTODD1" and
{orderitem.item_status} <> "NDISC" and
{orderitem.item_status} <> "YDISC" and
{orderitem.item_status} <> "NINV" and
{orderitem.item_status} <> "YINV" and
{orderitem.item_status} <> "RMALARRY" and
{orderitem.item_status} <> "NSOEXP" and
{orderitem.item_status} <> "YSOCAN" and
{orderitem.item_status} <> "NBACKORD" and
{orderitem.item_status} <> "NPEND" and
{orderitem.item_status} <> "NSOCAN" and
({orderitem.part_description} like ["*gold*", "*maint*", "*platinum*", "*subscrip*", "*support*", "*smartnet*", "*motif*", "*carepaq*", "*1 yr*", "*2 yr*", "*3 yr*", "*1yr*", "*2yr*", "*3yr*", "*Assurance*", "*annual*", "*renew*", "*1 year*", "*3 year*", "*5 year*"] or
 {orderitem.mfg_part_num} like ["CON-*", "SS-*", "GS-*", "*-CSP"] or
 {customer_1.customer_name} like ["*Symantec*", "*Veritas*"] or
({orderitem.part_description} like ["*Essential*", "*Access*"] and {customer_1.customer_name} like ["*Nokia*"]) or
 {orderitem.part_description} like ["*Tsupport*", "*Tx1*", "*Tx2*"]) and
{deal.order_type} = "ORDER" and
not ({customer.customer_name} startswith "ESL")

And here is the SQL query from the Database menu:


SELECT "deal"."date_created", "orderitem"."order_qty", "orderitem"."group_qty", "orderitem"."mfg_part_num", "orderitem"."dist_cost", "orderitem"."part_description", "deal_1"."date_created", "assocconts"."assoc_id", "assocconts"."assoc_type", "customer_1"."customer_name", "customer"."customer_name", "deal"."order_type", "deal"."order_num", "serialnum"."serial_num", "orderitem"."po_number", "orderitem"."item_status"
 FROM   (((((("sfaservr"."dbo"."orderitem" "orderitem" INNER JOIN "sfaservr"."dbo"."deal" "deal" ON ("orderitem"."order_num"="deal"."ref_order_num") AND ("orderitem"."config_num"="deal"."ref_quote_num")) INNER JOIN "sfaservr"."dbo"."deal" "deal_1" ON "orderitem"."po_number"="deal_1"."ref_order_num") INNER JOIN "sfaservr"."dbo"."customer" "customer_1" ON "orderitem"."mfg_id"="customer_1"."customer_id") LEFT OUTER JOIN "sfaservr"."dbo"."serialnum" "serialnum" ON (("orderitem"."order_num"="serialnum"."order_num") AND ("orderitem"."config_num"="serialnum"."config_num")) AND ("orderitem"."item_seq_num"="serialnum"."item_seq_num")) INNER JOIN "sfaservr"."dbo"."ordercust" "ordercust" ON "deal"."ref_order_num"="ordercust"."order_num") INNER JOIN "sfaservr"."dbo"."customer" "customer" ON "ordercust"."customer_id"="customer"."customer_id") INNER JOIN "sfaservr"."dbo"."assocconts" "assocconts" ON "customer"."customer_id"="assocconts"."customer_id"
 WHERE  "assocconts"."assoc_type"='KEYREP' AND ("deal_1"."date_created">={ts '2005-07-01 00:00:00'} AND "deal_1"."date_created"<{ts '2005-08-01 00:00:00'}) AND "orderitem"."po_number">'0' AND  NOT ("orderitem"."item_status"='NBACKORD' OR "orderitem"."item_status"='NDISC' OR "orderitem"."item_status"='NINV' OR "orderitem"."item_status"='NPEND' OR "orderitem"."item_status"='NPOCAN' OR "orderitem"."item_status"='NSOCAN' OR "orderitem"."item_status"='NSOEXP' OR "orderitem"."item_status"='NTODD1' OR "orderitem"."item_status"='NTODD2' OR "orderitem"."item_status"='NTODD3' OR "orderitem"."item_status"='RMALARRY' OR "orderitem"."item_status"='YDISC' OR "orderitem"."item_status"='YINV' OR "orderitem"."item_status"='YPOCAN' OR "orderitem"."item_status"='YSOCAN') AND (("orderitem"."part_description" LIKE '%1 year%' OR "orderitem"."part_description" LIKE '%1 yr%' OR "orderitem"."part_description" LIKE '%1yr%' OR "orderitem"."part_description" LIKE '%2 yr%' OR "orderitem"."part_description" LIKE '%2yr%' OR "orderitem"."part_description" LIKE '%3 year%' OR "orderitem"."part_description" LIKE '%3 yr%' OR "orderitem"."part_description" LIKE '%3yr%' OR "orderitem"."part_description" LIKE '%5 year%' OR "orderitem"."part_description" LIKE '%annual%' OR "orderitem"."part_description" LIKE '%Assurance%' OR "orderitem"."part_description" LIKE '%carepaq%' OR "orderitem"."part_description" LIKE '%gold%' OR "orderitem"."part_description" LIKE '%maint%' OR "orderitem"."part_description" LIKE '%motif%' OR "orderitem"."part_description" LIKE '%platinum%' OR "orderitem"."part_description" LIKE '%renew%' OR "orderitem"."part_description" LIKE '%smartnet%' OR "orderitem"."part_description" LIKE '%subscrip%' OR "orderitem"."part_description" LIKE '%support%') OR ("orderitem"."mfg_part_num" LIKE '%-CSP' OR "orderitem"."mfg_part_num" LIKE 'CON-%' OR "orderitem"."mfg_part_num" LIKE 'GS-%' OR "orderitem"."mfg_part_num" LIKE 'SS-%') OR ("customer_1"."customer_name" LIKE '%Symantec%' OR "customer_1"."customer_name" LIKE '%Veritas%') OR ("orderitem"."part_description" LIKE '%Access%' OR "orderitem"."part_description" LIKE '%Essential%') AND "customer_1"."customer_name" LIKE '%Nokia%' OR ("orderitem"."part_description" LIKE '%Tsupport%' OR "orderitem"."part_description" LIKE '%Tx1%' OR "orderitem"."part_description" LIKE '%Tx2%')) AND "deal"."order_type"='ORDER' AND "customer"."customer_name" NOT  LIKE 'ESL%'
 ORDER BY "assocconts"."assoc_id", "deal"."date_created"

Doxelec:

I think this "might" be your discrepancy??

This is from the RECORD SELECTION FORMULA:

{deal_1.date_created} >={?DateBegin} and
{deal_1.date_created}<={?DateEnd}

This is from the SQL Query:

AND ("deal_1"."date_created">={ts '2005-07-01 00:00:00'} AND "deal_1"."date_created"<{ts '2005-08-01 00:00:00'})

Record Select is ">= and <="   ....    but the SQL Query is ">= and <"

Hope it helps....
Mike V

Mike

That's worth checking out.  Crystal handles the date range differently depending on whether or not you convert datetime values to dates.  In above scenario, I'm assuming you entered a date range of 07/01/2005  - 07/31/2005?

~Kurt

Yes, that was the date range I entered.  But this wouldn't be an issue even if the missing records had date field values that fell exactly on midnight at an end of the date range, because Crystal does the date to datetime conversion correctly.

The date range of 7/1/05 to 7/31/05 is exactly the same as the time range of 00:00:00 on 7/1/05 to anything less than 00:00:00 on 8/1/05.

Doxelec:

Also, you might check for 'spaces' that are appearing as nulls. In other words the data element will appear NULL, when it is in fact a erroneous SPACEbar...that was hit accidentally.


Also, on or near line #11 of the SQL.... I'm not sure why you are using a GREATERTHAN '>' for this TEXT '0'   ?????
 
 (ie...."orderitem"."po_number">'0')

This is a TEXT item..and not a zero????

Mike  

I visually checked all the columns, so they're definitely not any sort of blank in any column mentioned by the selection criteria.  And wherever I do see blanks, there are also blanks in other rows for the same column, so it's not unique to the missing records.

For whatever reason (we didn't write the application), our po_number field is a text data type even though the values are numerical, but we needed to make sure the value started with a char higher than '0'.  It's comparing as text, but it works the way we need it to.  For the missing records, that column contains a 10-digit number starting with '3' just like all the other records.

I'm curious about your INNER JOINING on so many different elements. Seems like over-kill? Are these fields good for use in JOINS?

Would it be too much to have you post the 125 items of data here? I will copy/paste the items out..and analyze them.

Thanks
M

Also, be sure your MS SQL Query Analyzer data convert options MIRROR.... Crystral Reports options.

This may be the issue. They being two totally separate programs...their options would need to be in Sync with other.

All the joins are a sign of a good normalized database.  ;)  They are quite necessary to pull the data we need.

Unfortunately the data is business-sensitive sales-related information that I can't post here.

I don't see any "data convert" options in Query Analyzer (version SQL 8.00.760).  But anyway, if this is the issue, it is Crystal that would need to change because we need it to show all 125 records.

I think I found the problem but I'm still not sure how to fix it...

One of the fields I was searching on, mfg_part_num, is a char(40), but for some reason Crystal says it's a string of length 20.  Therefore, I was searching for '%-CSP' but the P was at position 21 in one of the missing records, and Crystal was only seeing the data up to '-CS' so it wouldn't show that record.

So the question becomes...  Why does Crystal think this field is only a 20-character string?  I found this out by right-clicking on the field in the Field Explorer, selecting Browse Data, then seeing the info at the top of the window.  I tried clicking Refresh in that same context menu but it didn't fix it.

I guess recreating the report from scratch would get Crystal to refresh the data source field types, but if anyone can suggest an easier way I'll give you the points for it.

I got it.

Apparently this report was written by another user originally, and their Datasource has been on it from day one.  Somewhere along the line, that field must have been redefined in the source database from a length of 20 to a length of 40.

I had the same database defined as a favorite Datasource for new reports I had been creating.  So I was able to fix this issue like so:

From the Database menu, select Set Datasource Location...
In the top window, under the report object, select sfaservr
In the bottom window, under the Favorites folder, select sfaservr
Click Update

But the more generic answer for anyone who runs into a similar problem is to reconnect to the datasource to refresh it.

I tried that...  It apparently didn't work for this particular problem.  Maybe it only detects the addition or removal of columns and tables, but not whether their properties changed.

I take that back.  I must have been thinking of another report.  Just now I opened an old copy of this report with the problem and used the Verify Database option on it, and it updated the field length.  So I'll consider that the best solution.  Thanks!

Glad i could help

mlmcc