Skip to Content
0
Oct 10, 2014 at 04:34 AM

Very odd behaviour when adding an extra table

24 Views

I hope someone can help me with this.

I have a Crystal Report v9 that is a form which is used to print a quotation request. I have modified the form to add in the customer address (it was only printing the shipping address).

Now, I have 9 quote formats to add in exactly the same fields to. I have completed 5 without any errors but have now come up with the following issue.

When I add in the Customer Master table using the database expert and then set up the link and join type (left outer join) to the quote header record, the SQL generated by the report suddenly deletes the joins to other tables.

Here is the code before the adding the table. Notice the last 3 joins:

SELECT Mailing . Mail_Name , Quote_Hd . ShipName , Quote_Hd . ShipPCode , Quote_Hd . AgreementNo , Quote_Ln . Qty , Quote_Ln . UnitListPrice , Quote_Hd . DateEntered , Quote_Ln . LineType , Quote_Ln . LineText , Quote_Ln . Model , Models . InvoiceDescr , Quote_Hd . Division , Quote_Ln . Spare_Dollars_1 , Quote_Hd . Spare_Bool_1 , Quote_Ln . RentalPeriod , Quote_Ln . LineSubType , Quote_Ln . DamageWaiver , Quote_Ln . SequenceNo , Models . ProductCode , Mailing . Mail_Phone , Mailing . Mail_Fax , Quote_Hd . ShipStreet1 , Quote_Hd . ShipStreet2 , Quote_Hd . ShipCity , Quote_Hd . ShipState , Mail_Hdr . Mail_FirstName , USERS . UserPrintLocn , Quote_Hd . Spare_Dollars_2 , Cont_Sma . NAME , Cont_Sma . POSSTRING , Cont_Sma . EMail , Cont_Sma . PHONE2 , Models . MonthlyRate , Quote_Ln . TaxCode1 , Quote_Hd . ShipStreet3 , ModelDes . CatalogDescription , Quote_No . Quote_Notes , Quote_No . Addendum

FROM {oj ((((((( TRACE . dbo . Quote_Hd Quote_Hd

INNER JOIN TRACE . dbo . Quote_Ln Quote_Ln ON Quote_Hd . AgreementNo = Quote_Ln . AgreementNo )

INNER JOIN TRACE . dbo . Mail_Hdr Mail_Hdr ON Quote_Hd . OrderContact = Mail_Hdr . Mail_Code )

INNER JOIN TRACE . dbo . Mailing Mailing ON Quote_Hd . OrderContact = Mailing . Mail_Code )

INNER JOIN TRACE . dbo . USERS USERS ON Quote_Hd . Salesman1 = USERS . UserID )

INNER JOIN TRACE . dbo . Quote_No Quote_No ON Quote_Hd . AgreementNo = Quote_No . AgreementNo )

LEFT OUTER JOIN TRACE . dbo . Models Models ON Quote_Ln . Model = Models . Model )

LEFT OUTER JOIN TRACE . dbo . ModelDes ModelDes ON Quote_Ln . Model = ModelDes . Model )

INNER JOIN TRACE . dbo . Cont_Sma Cont_Sma ON USERS . UserID = Cont_Sma . UserID }

WHERE Quote_Hd . AgreementNo =422141

ORDER BY Quote_Ln . SequenceNo

Now, here is the code after adding in the new table:

SELECT Mailing . Mail_Name , Quote_Hd . ShipName , Quote_Hd . ShipPCode , Quote_Hd . AgreementNo , Quote_Ln . Qty , Quote_Ln . UnitListPrice , Quote_Hd . DateEntered , Quote_Ln . LineType , Quote_Ln . LineText , Quote_Ln . Model , Quote_Hd . Division , Quote_Ln . Spare_Dollars_1 , Quote_Hd . Spare_Bool_1 , Quote_Ln . RentalPeriod , Quote_Ln . LineSubType , Quote_Ln . DamageWaiver , Quote_Ln . SequenceNo , Mailing . Mail_Phone , Mailing . Mail_Fax , Quote_Hd . ShipStreet1 , Quote_Hd . ShipStreet2 , Quote_Hd . ShipCity , Quote_Hd . ShipState , Mail_Hdr . Mail_FirstName , USERS . UserPrintLocn , Quote_Hd . Spare_Dollars_2 , Quote_Ln . TaxCode1 , Quote_Hd . ShipStreet3 , Quote_Hd . CustomerNo , Quote_No . Quote_Notes , Quote_No . Addendum

FROM (((( TRACE . dbo . Quote_Hd Quote_Hd

INNER JOIN TRACE . dbo . Quote_Ln Quote_Ln ON Quote_Hd . AgreementNo = Quote_Ln . AgreementNo )

INNER JOIN TRACE . dbo . Mail_Hdr Mail_Hdr ON Quote_Hd . OrderContact = Mail_Hdr . Mail_Code )

INNER JOIN TRACE . dbo . Mailing Mailing ON Quote_Hd . OrderContact = Mailing . Mail_Code )

INNER JOIN TRACE . dbo . USERS USERS ON Quote_Hd . Salesman1 = USERS . UserID )

INNER JOIN TRACE . dbo . Quote_No Quote_No ON Quote_Hd . AgreementNo = Quote_No . AgreementNo

WHERE Quote_Hd . AgreementNo =422141

ORDER BY Quote_Ln . SequenceNo

When I look at the database expert and the links, all of the tables are present. It is only when I view the SQL code that the tables are missing.

Now, the really odd part is that for that particular transaction number (422141), there should only be 1 row returned.

On the report that is displaying the incorrect behaviour, the SQL still only returns a single row, but the report displays 839 rows, all of them identical.

Additionally, the report cannot be saved once this occurs. It returns the "Failed to save document" error.

I have done various tests, deleting fields, verifying the database, etc and saving the report at each stage. It is only when I add the new table that it all goes pear shaped.

I have attached the sample output that the reports are producing. the first file shows the correct information. The second file shows the records duplicating.

Any ideas?

Regards,

mavrick5

Attachments

Capture2.JPG (96.4 kB)
Capture1.JPG (106.6 kB)