Data not displaying as per links assigned. 2

[NBVC]

I am not sure what I am missing here.

I am inserting and linking 3 tables from my ERP database... yet the SQL statement that Crystal creates is this:

SELECT "DEMAND_SUPPLY_LINK"."DEMAND_BASE_ID", "DEMAND_SUPPLY_LINK"."DEMAND_SEQ_NO", "DEMAND_SUPPLY_LINK"."SUPPLY_BASE_ID", "DEMAND_SUPPLY_LINK"."SUPPLY_LOT_ID"
 FROM   "SYSADM"."DEMAND_SUPPLY_LINK" "DEMAND_SUPPLY_LINK"

which only shows FROM one table... why would the other 2 tables not be shown... and where is the WHERE statement.. I did create links.

 

[IanWaterman]

If you have not used any data from the other tables crystal optimises the query.

If you do not have a select statement then the where clause will not exist.

Add a field from each of the other tables and a select filter.

If you are using a Crystal formula in select then that might not parse to the database either.

Ian

 

[lbass]

Try placing at least one field from each table on the report and then recheck the SQL. If the tables are not referenced in the report, then they won't show up in the SQL query unless you explicitly enforce the links.

To enforce links, select each link in the database expert->link options->and check "enforce both".

-LB

 

[NBVC]

thanks for the replies.

Though I have selected to items from each table for the report and still same SQL statement results.

Also, when I try to do the "enforce both"... I don't see a choice for that.

I am running CR version 9.2.2.634

 

[NBVC]

Also,

If I try the same setup in MSQuery with EXCEL... the resulting SQL is:

SELECT DEMAND_SUPPLY_LINK.DEMAND_BASE_ID, DEMAND_SUPPLY_LINK.DEMAND_LOT_ID, DEMAND_SUPPLY_LINK.DEMAND_PART_ID, DEMAND_SUPPLY_LINK.DEMAND_SEQ_NO, DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID, DEMAND_SUPPLY_LINK.SUPPLY_LOT_ID
FROM SYSADM.CUST_ORDER_LINE CUST_ORDER_LINE, SYSADM.DEMAND_SUPPLY_LINK DEMAND_SUPPLY_LINK
WHERE DEMAND_SUPPLY_LINK.DEMAND_SEQ_NO = CUST_ORDER_LINE.LINE_NO AND CUST_ORDER_LINE.CUST_ORDER_ID = DEMAND_SUPPLY_LINK.DEMAND_BASE_ID

which gives me expected results.

I want to duplicate this in Crystal...

 

[lbass]

I guess the enforce options might have been new in XI.

Please show the query after you have placed fields from each table on the report.

Maybe you should also go to database->verify database.

-LB

 

[lbass]

Didn't see your last post.

Are you creating the links in the database expert by joining the tables, or are you adding the links in the record selection formula? I think if you add them in the record selection formula, they will occur locally, and therefore would not appear in the SQL query (although then you should see two separate queries in the Show SQL Query).

-LB

 

[IanWaterman]

As we said unless you select fields from the other tables Crystal will optimise the query

SELECT 
DEMAND_SUPPLY_LINK.DEMAND_BASE_ID, DEMAND_SUPPLY_LINK.DEMAND_LOT_ID, DEMAND_SUPPLY_LINK.DEMAND_PART_ID, DEMAND_SUPPLY_LINK.DEMAND_SEQ_NO, DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID, DEMAND_SUPPLY_LINK.SUPPLY_LOT_ID

you have only selected fields from DEMAND_SUPPLY_LINK, so crystal will not query the other tables.

Ian

 

[NBVC]

Yes Ian, you are correct... I understand now.

I am used to using MSQuery in Excel, that I didn't realize what you were referring to previously...

Now I added a field from the other table and it looks fine. This is my final query after adding some joins...

 SELECT "DEMAND_SUPPLY_LINK"."DEMAND_BASE_ID", "DEMAND_SUPPLY_LINK"."DEMAND_SEQ_NO", "DEMAND_SUPPLY_LINK"."SUPPLY_BASE_ID", "DEMAND_SUPPLY_LINK"."SUPPLY_LOT_ID", "CUST_ORDER_LINE"."CUST_ORDER_ID"
 FROM   "SYSADM"."CUST_ORDER_LINE" "CUST_ORDER_LINE", "SYSADM"."DEMAND_SUPPLY_LINK" "DEMAND_SUPPLY_LINK"
 WHERE  (("CUST_ORDER_LINE"."CUST_ORDER_ID"="DEMAND_SUPPLY_LINK"."DEMAND_BASE_ID" (+)) AND ("CUST_ORDER_LINE"."LINE_NO"="DEMAND_SUPPLY_LINK"."DEMAND_SEQ_NO" (+)))

thank you too for helping LB.