Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 560
  • Last Modified:

Report shows different number of records than SQL query

I saw a similar question title already in here but this one is different...

At the simplest level, I have a report in Crystal XI Developer that returns 123 records.  However, when I copy the SQL query from Database->Show SQL Query... and paste that into SQL Query Analyzer and run it directly against the data source, it returns 125 records.

What can cause the number of records to be different?

The 2 records missing from the report seem to meet all of the criteria in the record selection formula.  I don't have a group selection formula.  What else would cause Crystal to omit records that were returned by the SQL query?
0
Doxelec
Asked:
Doxelec
  • 8
  • 4
  • 2
  • +3
1 Solution
 
peter57rCommented:
Hi Doxelec,
Please post the sql of the query as it appears in CR.

Pete
0
 
frodomanCommented:
>>> 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.
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DoxelecAuthor Commented:
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"

0
 
MIKESoftware Solutions ConsultantCommented:
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
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
0
 
DoxelecAuthor Commented:
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.
0
 
MIKESoftware Solutions ConsultantCommented:
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  
0
 
DoxelecAuthor Commented:
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.
0
 
MIKESoftware Solutions ConsultantCommented:
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
0
 
MIKESoftware Solutions ConsultantCommented:
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.
0
 
DoxelecAuthor Commented:
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.
0
 
DoxelecAuthor Commented:
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.
0
 
DoxelecAuthor Commented:
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.
0
 
mlmccCommented:
Or you can use the VERIFY DATABASE optionunder the DATABASE menu.  Its functionis to renew the connection and ensure any changes to the data source are reflected in the report.

mlmcc
0
 
DoxelecAuthor Commented:
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.
0
 
DoxelecAuthor Commented:
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!
0
 
mlmccCommented:
Glad i could help

mlmcc
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now