outer joins

[mouse291]

Hello,

I have been trying to figure out how to do this, but to no avail.

I have three tables, a lease table, an accounts receivable table and an accounts receivable assessments table. I need to join the lease table to the accounts receivable table using the left outer join. My problem arises when I add the accounts receivable assessment table - I can only join this table to the accounts receivable table on invoice number. I need to use the left outer join from the accounts receivable table to the accounts receivable assessment table. When I have these three tables joined by left outer joins, Crystal returns an error message of "Query contains an illegal outer-join request".

Can this be done? I haven't been able to figure it out.

If anyone can help me out, I would really appreciate it!

 

[jazerr]

Can you paste the query in here so I can see what you are trying to do? If not:
Try something like this if you can Query using SQL...Let me know what you come up with..

SELECT DISTINCT
lease.LeaseField,
AccountsRecievableAssessments.Somefield,
AccountsRecievable.Anotherobscurefield

FROM Lease
LEFT JOIN AccountsRecievable
LEFT OUTER JOIN AccountsRecievableAssessments ON AccountsRecievable.Invoicenumber
ON Lease.JoinField = AccountsRecievable.Joinfield

 

[kenhamady]

What is your database? Where is the error coming from?

It is possible that your ODBC driver doesn't support this combination of joins. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[mouse291]

Thanks for the response!

Here is a copy of the SQL statement. My database is housed on a sybase server and I am accessing this database through an ODBC connection - (Merant 3.50 IVSYB14.DLL 3/12/99, according to the tech). I am getting this error when I run the report.

I am using CR v7.

SELECT
rls.lse_s, rls.act_s, rls.rec_d, rar.invo_s, rar.pamt_b_d, rar.pamt_p_d, rarb.aamt_b_d, rarb.aamt_p_d
FROM
lpr_prod.dbo.rls rls,
lpr_prod.dbo.rar rar,
lpr_prod.dbo.rarb rarb
WHERE
rls.lse_s *= rar.lse_s AND
rar.invo_s *= rarb.invo_s AND
rls.act_s LIKE 'A*%'

Thanks for any help.

mouse291

 

[Caren]

Try changing from Microsoft SQl to ODBC drivers. Seagate told us that Microsoft doesn't support left outer joins very well yet. We changed all our reports from menu "database/convert database driver", then "set location". We already had ODBC drivers pointing to our various SQL databases previously set up for FRx so we just select from those rather than creating a whole new set.

 

[Guest_imported]

We having been having a problem running a query using left outer joins as well, and we are using ODBC drivers. We have a query based on 2 tables (table A and table B) with an outer join to include all records from table A, and when you apply a WHERE clause to table B, only those records from table B have a matching value in table A are returned. The resolution I found was to create a subquery with the WHERE clause to table B, and the second query should combine table A with the subquery to perform the outer join. This type of query processing by design complies with ANSI SQL 92 specs. However, I can't this to work either. The logic seems so simple, but we have tried everything and can't find a resolution. Any help on this would be greatly appreciated. Also, Does Crystal support UNIONs???

 

[dsaba]

I have used UNIONs and Outer Joins with crystal but I have done so by creating views in the database and then pulling the data into Crystal. I have recently discovered in Crystal 8 that you can also use the Crystal SQL Designer to create the data using Joins and Unions and then pull the information into a report.

dsaba(Technical User)

 

[Guest_imported]

Practically all reports that I have created have used outer joins; however, I have done so by creating views. Performance is also enhanced because almost all processing is done server side.

 

[IdoMillet]

The problem with your WHERE clause is due to
the WHERE clause being applied AFTER the
OUTER JOIN. Hence the WHERE clause on the
B Table columns finds NULL values there that
cause the whole composite record to be discarded.

You can solve this by using an ON condition
instead of a WHERE condition (if your DBMS
supports this syntax).

Alternatively, you can simple add
"OR IsNull(column_name)" to your
WHERE condition. For example:

Change
...
WHERE Table_B.Year(Order_Date) = 1999

to something like
...
WHERE IsNull(Table_B.Order_Date)OR
Table_B.Year(Order_Date) = 1999

Cheers,
- Ido

 

[JJB81]

Hello
I am having the same problem that ido is talking about here. Can you take a look at my sql. i get the error when i have two left outer joins b/c of the null values? how can i correct this?

SELECT
TK_CUSTOMER1."MAIL_ADDR1", TK_CUSTOMER1."MAIL_ADDR2", TK_CUSTOMER1."MAIL_CSZ", TK_CUSTOMER1."MAIL_MNAME", TK_CUSTOMER1."TAG",
TK_ODET_2003P1."CUSTOMER", TK_ODET_2003P1."I_BAL", TK_ODET_2003P1."I_EXT", TK_ODET_2003P1."I_OQTY", TK_ODET_2003P1."I_PAY", TK_ODET_2003P1."I_PRICE", TK_ODET_2003P1."ITEM", TK_ODET_2003P1."ITEM_NAME",
TK_ODET_2003P_SB_ASSOC1."E_SBLS",
TK_ORDER_2003P_CHG_ASSOC1."CHG_AMT"
FROM
"TK_CUSTOMER" TK_CUSTOMER1,
{ oj "TK_ORDER_2003P_CHG_ASSOC" TK_ORDER_2003P_CHG_ASSOC1 LEFT OUTER JOIN "TK_ODET_2003P" TK_ODET_2003P1 LEFT OUTER JOIN "TK_ODET_2003P_SB_ASSOC" TK_ODET_2003P_SB_ASSOC1 ON
TK_ODET_2003P1."Z_ID" = TK_ODET_2003P_SB_ASSOC1."Z_ID" ON
TK_ORDER_2003P1."Z_ID" = TK_ORDER_2003P_CHG_ASSOC1."Z_ID"}
WHERE
TK_CUSTOMER1."NUMBER" = TK_ODET_2003P1."CUSTOMER" AND
TK_ODET_2003P1."CUSTOMER" = TK_ORDER_2003P1."CUSTOMER" AND
TK_CUSTOMER1."TAG" = '2003PS'
ORDER BY
TK_ODET_2003P1."CUSTOMER" ASC,
TK_ODET_2003P1."ITEM_NAME" ASC