Left join limitation 2

[dreman]

Using CR 8.5 with ODBC drive (Sql)
this should be very simple yet it is not working.
I do think it is a limitation of Crystal Report.
i have 2 sql table linked:
customer->sales_history linked by customer_id
Parameter are based upon:
sls.customer (ie ABC)
date.sales_history (ie 1/1/1910 to today's date, to cover all sales)

this is what I am trying to resolve.
List all customer for a particular sls whether we sold to them or not.
I've used left join but it only listing the customer if they have records in sales_history.
How can I list customers if they do not have sales or record in the other table.
Any advice, thank you.
dré

 

[Naith]

I think Dreman is using a late version of Oracle which permits the FROM section of the query to handle parameter conditions. Not all rdbms environments allow this - but it would depend what your database was.

Naith

 

[dreman]

Naith:
I am using SQL 2000. When I went to sql i just put the condition in the from.
Ken mentioned to do a stored procedure but I am trying not to use one.
As for infomania: I put the date range in the from selection using sql itself. I haven't found a solution to do it without using stored procedure.
Thank you.
dré

 

[heppme]

You can filter on the table side with null values when using an outer-join. It works (in Oracle) as follows:

select *
from table1 a, table2 b
where a.field1 = b.field1(+)
and b.field2(+) = 123

I use this frequently in Toad and Crystal by editing the SQL statement and don't have any problems with it. The example above would return all cases where the second field in table two had a value of 123 and all cases with no matching row in table two.
Hope this does not muddy the waters further.

 

[dreman]

Slizzo:
that is in Oracle but I am using SQL 2000.
thanks
dré

 

[kenhamady]

Crystal allows you to modify the FROM and WHERE in the "Show SQL" window (You can't change the SELECT until v9, which allows you to use any SQL you want.

Slizzo,
I have a question about your approach. You mention two cases but didn't mention the 3rd possible case. What if there were a record in A and a single record match in B that was "124"? Would the A record still show up? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[heppme]

Kenhamady,
Yes, it would but it would bring null values in on the side of table two. Here's a real-life example and why I use this SQL:
select * from proj a, genl_udef b where a.proj_id = b.genl_id(+) and a.proj_id = '010142.011'
and (b.udef_lbl_key = 6 or b.udef_lbl_key is null)

select * from proj a, genl_udef b where a.proj_id = b.genl_id(+) and a.proj_id = '010142.011'
and b.udef_lbl_key(+) = 6

Scenario one returns no rows even though there is a record in table a for this project. This is because table two has that project, but five other udef_lbl_key's rather than the one I am calling. It's seen as neither 6 nor null so it returns nothing.

The second SQL statement returns my project from table a and a null value on the side of table b, which is what is required in this case. Thanks.

 

[kenhamady]

Good to know. Which version of Oracle are you using? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[heppme]

We're on 8i.

 

[jimmperry]

value exceeding its mas length error.I dont understand why Im getting this error when all info in this field is under 16 characters in the sting. Any ideas?