Link to home
Start Free TrialLog in
Avatar of johntmcii
johntmcii

asked on

Joins on tables

Continuing to work on the Reports, ran into another issue not usre if it is a coding problem or data exception. I am using four SAP tables in this CR;  ODLN is the main table used and joins OCRD, DLN1, and OSHP. I oiginally had code all to use left outer joins, enforced outer join, =.
The changed to Inner joins as shown in the file below (see Crystal Report joins file)

In testing, I ran a query in SAP to select records from the tables so I could compare the output. SAP created the following join statements in the query it created.

From ODLN T0
      Inner Join OCRD T1 on T0.cardcode = T1.cardcode
      Inner Join OSHP T2 on T0.Trnspcode = T2.trnscode
      inner Join  DLN1 T3 on T0.Docenry = T3.Docentry

From the test, the Crystal report produced correct reports until dates 10/01/08 encountered (This is probably going to confuse the issue but the information on the query appeared to be correct regardless of the date) No matter how I had the joins coded any report containing a shipment number prior to 10/01 appeared to be correct however any shipment number after 10/01 did not show any data on the report. It appeared that there was no data in the DLN1, OSHP or OCRD. No detail lines appeared, no address lines appeared, only static data on the report, but on the query I could see the information.

My question is simply there were 4500 lines on the query that I could verify the same data on the crystal report so I know the logic worked in the CR. By that I mean the detail lines were appearing fine, the addresses formated properly, the totals were correct, etc. Is there a debug or some other tool I can turn on in CR to see why the report stopped working after 10/01?
SKMBT-C45108102210040.pdf
Avatar of Mike McCracken
Mike McCracken

Not that I know of.

Is there data in the database for dates after 10/1

Are you filtering the data through the select exprt?

Since it is 10/1 is that the end of an FY and the new data is m a new table for the FY?

mlmcc
Avatar of johntmcii

ASKER

On the databse option I listed the SQL (below) This report  is one that I had pointed to a test database initially then changed to production, this query appears to be pointing to Test, (look at the dsn. The dsn appears to be pointing to production. when could I be picking up the test_sap_test_2008-10-01) Could this be pointing to the test databse even though I have the live database selected in the "set datasource location" This would explain the problem?
Something I am not understanding about how to create set the dso/dsn.

 SELECT "ODLN"."DocNum", "DLN1"."ItemCode", "DLN1"."Dscription", "ODLN"."DocDate", "DLN1"."ShipDate", "DLN1"."Quantity", "DLN1"."LineTotal", "DLN1"."Price", "ODLN"."CardCode", "OSHP"."TrnspName", "OCRD"."CardName", "OCRD"."Address", "OCRD"."City", "OCRD"."State1", "OCRD"."ZipCode"
 FROM   (("TEST_SAP_TEST_2008-10-01"."dbo"."ODLN" "ODLN" LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01"."dbo"."DLN1" "DLN1" ON "ODLN"."DocEntry"="DLN1"."DocEntry") LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01"."dbo"."OCRD" "OCRD" ON "ODLN"."CardCode"="OCRD"."CardCode") LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01"."dbo"."OSHP" "OSHP" ON "ODLN"."TrnspCode"="OSHP"."TrnspCode"
 WHERE  "ODLN"."DocNum"=275763
 ORDER BY "ODLN"."DocNum", "DLN1"."ItemCode"
 
Code in the file below is the current dsn

2008-10-22-130058.jpg
As a guess the database  TEST_SAP_TEST_2008-10-01 is data up to 2008-10-01

mlmcc
Yep, I just wrote anotehr CR (no copy), included same fields, all worked fine. so my question is (how, where) do I change the qualified name on the DSO? Maybe I am missing something but I don't recall seeing where this is required. I changed the location under the database, I thought that would make the necessary changes?
I need a spell check , can't get my fingers to work!
It should be in the SET DATASOURCE LOCATION that you show.

Just navigate to the new connection
Select the one to change in the top box
Click UPDATE

mlmcc
Notl not sure where I go from here other than starting over with a new CR. I performed those exact steps and  as you can see the dso is still pointing to Test. The SQL was after I had changed the Set Datasource location. I will try one more time then let you know the results.
I ran through the entire process again, created new DSN, set datasource location, same results
SQL STILL SHOWING TEST_SAP_TEST_2008-10-01"."dbo

 SELECT "ODLN"."DocNum", "DLN1"."ItemCode", "DLN1"."Dscription", "ODLN"."DocDate", "DLN1"."ShipDate", "DLN1"."Quantity", "DLN1"."LineTotal", "DLN1"."Price", "ODLN"."CardCode", "OSHP"."TrnspName", "OCRD"."CardName", "OCRD"."Address", "OCRD"."City", "OCRD"."State1", "OCRD"."ZipCode"
 FROM   (("TEST_SAP_TEST_2008-10-01"."dbo"."ODLN" "ODLN" LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01"."dbo"."DLN1" "DLN1" ON "ODLN"."DocEntry"="DLN1"."DocEntry") LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01"."dbo"."OCRD" "OCRD" ON "ODLN"."CardCode"="OCRD"."CardCode") LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01"."dbo"."OSHP" "OSHP" ON "ODLN"."TrnspCode"="OSHP"."TrnspCode"
 WHERE  "ODLN"."DocNum"=272626
 ORDER BY "ODLN"."DocNum", "DLN1"."ItemCode"

2008-10-23-090324.jpg
It looks like it changed it to me.

Do you get the expected data?

mlmcc
No sir, thats what I am saying the DSN changed, however the ressults are the same. This is exactly what I saw previously. It says it changes but is still pointing to "Test" when I Show SQL Query (after change) as shown above.
 
Is the report saved with data?

mlmcc
No the option to save data with the report is not selected.
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Strange,I have created a new dsn in the same report but had the same results; the fully qualified name is still incorrect and the information still did not appear on the report. I wrote a quick report with same dsn used above but did not copy and change report as I did in the instance above, in this case the information was correct and fully qualified name was correct.
 I am at a point I am going scrub the report and start over with a new one, I believe it will work.
Thanks for you help.
 
I recreated report and that part worked fine. Nothing different that I would tell from the previous report.