Solved

Report shows different number of records than SQL query

Posted on 2006-07-07
18
494 Views
Last Modified: 2008-02-01
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
Comment
Question by:Doxelec
  • 8
  • 4
  • 2
  • +3
18 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 17060335
Hi Doxelec,
Please post the sql of the query as it appears in CR.

Pete
0
 
LVL 42

Expert Comment

by:frodoman
ID: 17060415
>>> 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
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 17060760
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
 

Author Comment

by:Doxelec
ID: 17061442
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
 
LVL 17

Expert Comment

by:MIKE
ID: 17070703
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
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 17071560
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
 

Author Comment

by:Doxelec
ID: 17072288
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
 
LVL 17

Expert Comment

by:MIKE
ID: 17076604
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
 

Author Comment

by:Doxelec
ID: 17076855
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Expert Comment

by:MIKE
ID: 17077398
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
 
LVL 17

Expert Comment

by:MIKE
ID: 17077426
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
 

Author Comment

by:Doxelec
ID: 17080794
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
 

Author Comment

by:Doxelec
ID: 17085892
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
 

Author Comment

by:Doxelec
ID: 17086048
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 17087364
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
 

Author Comment

by:Doxelec
ID: 17089640
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
 

Author Comment

by:Doxelec
ID: 17089667
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 17095432
Glad i could help

mlmcc
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now