Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Crystal Select Expert not selecting expected recor Post Reply Post New Topic
Author Message
NBVC
Newbie
Newbie
Avatar

Joined: 28 Apr 2008
Online Status: Offline
Posts: 2
Quote NBVC Replybullet Topic: Crystal Select Expert not selecting expected recor
     Posted: 28 Apr 2008 at 5:55am
Hi,

I've crossposted this question here: http://www.access-programmers.co.uk/forums/showthread.php?p=698016#post698016

but got no replies...so was hoping to get help here...

I am trying to develop a Crystal Report and have use the Select Expert to create formulas that ultimately produced this SQL statement:

Code:
 SELECT "REQUIREMENT"."WORKORDER_BASE_ID", "REQUIREMENT"."WORKORDER_LOT_ID", "REQUIREMENT"."WORKORDER_SUB_ID", "REQUIREMENT"."PIECE_NO", "REQUIREMENT"."CALC_QTY", "REQUIREMENT"."ISSUED_QTY", "REQUIREMENT"."PART_ID", "CUST_ORDER_LINE"."LAST_SHIPPED_DATE", "CUST_ORDER_LINE"."TOTAL_SHIPPED_QTY"
 FROM   "SYSADM"."DEMAND_SUPPLY_LINK" "DEMAND_SUPPLY_LINK", "SYSADM"."REQUIREMENT" "REQUIREMENT", "SYSADM"."CUST_ORDER_LINE" "CUST_ORDER_LINE"
 WHERE  (("DEMAND_SUPPLY_LINK"."SUPPLY_BASE_ID"="REQUIREMENT"."WORKORDER_BASE_ID") AND ("DEMAND_SUPPLY_LINK"."SUPPLY_LOT_ID"="REQUIREMENT"."WORKORDER_LOT_ID")) AND (("DEMAND_SUPPLY_LINK"."DEMAND_BASE_ID"="CUST_ORDER_LINE"."CUST_ORDER_ID") AND ("DEMAND_SUPPLY_LINK"."DEMAND_SEQ_NO"="CUST_ORDER_LINE"."LINE_NO")) AND ("REQUIREMENT"."WORKORDER_BASE_ID" LIKE 'F%' AND "REQUIREMENT"."PART_ID"='91841' AND "CUST_ORDER_LINE"."LAST_SHIPPED_DATE">={ts '2007-10-31 00:00:01'} OR "REQUIREMENT"."WORKORDER_BASE_ID" LIKE 'F%' AND "REQUIREMENT"."PART_ID"='91841' AND "CUST_ORDER_LINE"."TOTAL_SHIPPED_QTY"=0)
It doesn't pull all the data I expect it to and can't figure out why.

When I use the same/similar SQL statement in MS Query (Excel).. I get the expected results.

This is the SQL from Excel's MSQuery:

Code:
SELECT REQUIREMENT.WORKORDER_BASE_ID, REQUIREMENT.WORKORDER_LOT_ID, REQUIREMENT.WORKORDER_SUB_ID, REQUIREMENT.PIECE_NO, REQUIREMENT.CALC_QTY, REQUIREMENT.ISSUED_QTY, CUST_ORDER_LINE.CUSTOMER_PART_ID
FROM SYSADM.CUST_ORDER_LINE CUST_ORDER_LINE, SYSADM.DEMAND_SUPPLY_LINK DEMAND_SUPPLY_LINK, SYSADM.REQUIREMENT REQUIREMENT
WHERE REQUIREMENT.WORKORDER_BASE_ID = DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID AND REQUIREMENT.WORKORDER_LOT_ID = DEMAND_SUPPLY_LINK.SUPPLY_LOT_ID AND DEMAND_SUPPLY_LINK.DEMAND_BASE_ID = CUST_ORDER_LINE.CUST_ORDER_ID AND DEMAND_SUPPLY_LINK.DEMAND_SEQ_NO = CUST_ORDER_LINE.LINE_NO AND ((REQUIREMENT.WORKORDER_BASE_ID Like 'F%') AND (REQUIREMENT.PART_ID='91841') AND (CUST_ORDER_LINE.LAST_SHIPPED_DATE>{ts '2007-10-31 00:00:00'}) OR (REQUIREMENT.WORKORDER_BASE_ID Like 'F%') AND (REQUIREMENT.PART_ID='91841') AND (CUST_ORDER_LINE.TOTAL_SHIPPED_QTY=0))
I don't really see the difference... Does anyone know what is happening?

Thanks.
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 28 Apr 2008 at 3:51pm
First off, this SQL is huge and since it stretches to the right, it's almost impossible to read and figure out what is going on. That is probably one reason why you didn't get any responses. Nonetheless, the look very similar and are probably correct. One thing that happens a lot with Crystal is that if you have Nulls in your data then CR won't retrieve all the data. You have to test for nulls. Does your data have any null data? Secondly, what would be easier is to save this SQL as a stand-alone query and call the query directly from Crystal Reports. That would produce the most reliable results.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
NBVC
Newbie
Newbie
Avatar

Joined: 28 Apr 2008
Online Status: Offline
Posts: 2
Quote NBVC Replybullet Posted: 29 Apr 2008 at 9:18am
Hi Brian and thanks for the reply...

Sorry, I know the SQL is long, but I didn't want to break it up in case someone wanted to diagnose it piece by piece.

Well, these records may have have nulls: CUST_ORDER_LINE"."LAST_SHIPPED_DATE

but I asked for anything greater than a specific date... do I need to say And IsNotNull...or something similar?

How would I go about doing this, it sounds interesting:

Secondly, what would be easier is to save this SQL as a stand-alone query and call the query directly from Crystal Reports. That would produce the most reliable results.
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.