Classic Left Outer Join Problem with filter

[fredp1]

Hi All,

CR 8.5, SQLServer 2K

Within Crystal I want to write a subselect query that accepts a paramater. I know you can achieve similar results with Sub reports, but they can slow reports down.
How can you do it with pure Crystal?

The scenario is, you want to see every part number in the item master table and the total usage over a given period of time from the stock transaction table.

Item---->Transcation (Left outer join)

As I understand it, you can add a filter to the transaction table to limit it to a paramter date range as this effectively turns the quey into a inner join. (this then limits all items that exist in the transaction table that meet the date range)Correct?

You can remove the filter on the transaction table and have a variable that 'sums' the qty given the date range.
e.g. if transdate in {?min date} to {?max date} then qty else 0
again, this can be slow because you need to go through every transaction record and if you have a 1 to many link, the number of items shown will be wrong.

In SQL it would be something like
Select t1.Item, t3.qty from item t1 left outer join (Select t2.item, t2.qty from transaction t2 where t2.date >=01/01/2000 and t2.date <=01/01/2002) t3

this is the most efficient code.
How do you do this in Crystal with the Dates been parameters? Can ver 9 or 10 handle this?

Or do you best handle this as a stored procedure?

Can anyone confirm/clarify show simple example of a SP of the above.

Thanks
Fred

 

[IdoMillet]

Change the record selection criterion to:

IsNull({transaction_Date}) OR ...

where the 2nd half of the condition is your regular
date range condition.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

Ido,

Except that records from the left table would be eliminated if the right table contained a date (was not null) that was not in the date range.

-LB

 

[IdoMillet]

Good point... )

To handle such cases, you would need to use a UNION statement that adds the records from the Left table and Null columns for the right table. By using UNION rather than UNION ALL you make sure duplicates are removed.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lyanch]

Actually the database usually handles the isnull(X) or xxx correctly. It is crystal that doesn't handle the outer join correctly when you use filters, adding the null doesn't really change the query on the SQL side, and crystal will handle it correctly. Test it out, you will probably find you get the data that you want.

Lisa

 

[fredp1]

Thanks all for responding,

Lisa,
If you are saying that you can add isnull(xx) OR ... in the selection part of the query, that isn't right. It will not return all the records.

Lbass has also agreed that you cannot do it in the selection formulae, you can do it in a variable.

I've been having that discussion of this thread

http://www.tek-tips.com/viewthread.cfm?spid=766&newpid=766&sqid=851334

What i'm trying to find out is how do we do it in CR8.5 to make it run fast. I'm trying to avoid Subreports, but I cannot edit the SQL and add a Paramter in the SQL that Crystal generates. Hence the possible suggestion of using a SP.
The query I'm trying to create is this
Select t1.Item, t3.qty from item t1 left outer join (Select t2.item, t2.qty from transaction t2 where t2.date >=01/01/2000 and t2.date <=01/01/2002) t3
There the date fields are parameters in the report.
I only have 8.5 and I run CE as well.
V9 and 10 is a long way off, but i am curious if you can pass a parameter to the SQL subquery?

 

[lyanch]

Fred,

Yes.

*If* the original SQL before adding the isnull OR addition, returned the right dataset outside crystal..
*AND* after adding the isnull OR all the selection formula is still getting sent to the database,
then it will work correctly. The "isnull" is for crystal which doesn't handle the outer join correctly internally.

I use this method extensively when I have outer joins with parameters.

Lisa

 

[fredp1]

Hi Lisa,

I'm a little confused.

I think what you are saying in yor 1st sentence is... as long as CR passes the SQL down to the server it will behave the same way as if you wrote the query on the db. (Agree)

I not clear on what you mean on 'The "isnull" is for crystal which doesn't handle the outer join correctly internally'

I haven't noticed CR handle the outer join incorrectly.
Could you provide a example please?
(I've done some testing with the Northwind db and a SQL2k tables and CR works as expected.)

So if you had 100 records in table1 and 200 records in table2 (50 have a date of 01/01/2004), Select t1.*, T2.* from table1 t1 left outer join table2 t2 on t1.id=t2.id where (t2.id is null or t2.date='01/01/2004' then you will only get 50 records returned.
Instead what I wanted to achieve was
Select t1.*, T2.* from table1 t1 left outer join (select t3.id, t3.date from table2 t3 where t2.date='01/01/2004') t2 on t1.id=t2.id would still return 200 records

I'm talking to tech support again this morning to see if there is a way to do it in Crystal 8.5 (which runs fast) or whether a SP is the way to do it. (i'm using SQL 2k and ODBC)

Any thoughts?
Thanks for hanging in there.

Fred

 

[lyanch]

Fred, check out my example in the other thread you were talking about. At least for Sybase it does work correctly.

The reason that it doesn't work without the isnull is because *crystal* removes the records locally *after* the database returns the records. That is why you need to get the whole selection sent in SQL to the database. The SQL will process correctly, and with the "isnull OR" statment, crystal won't remove them locally either.

Lisa

 

[fredp1]

Hi Lisa,

I just saw your other posting in

http://www.tek-tips.com/viewthread.cfm?SQID=851334&SPID=766&page=1

I've think I found something in a book that I have that may explain what way be happening.. i'll test it out and let you know for sure, but its basiclly the different syntax of the joins, Legacy syntax (Sybase) and ANSI/IOS-92. The syntax that you using in the example is the legacy syntax. What I read is that with the Legacy syntax,
'the ordering of the terms in the WHERE clause is significant when constructing mutlilevel joins using the legacy syntax.
Its precisely because of this ambiguity- whether the ordering of the WHERE clause predicates is significant- that the SQL-92 standard moved join contruction to the FROM clause. Here the amiguities are gone....'

Look like with the different syntax we are both right. This may explain some of the conflicting threads out there which has confused me and others.

(I think there is a way in CR to change the syntax by changing a registry entry)

Again I'll test it out and let you know.

Hang in there.
Fred

 

[fredp1]

Hi Lisa,

I cannot test this with SQL Server, it doesn't like that syntax.

Is there any chance if you get a second that you test it with the SQL-92 syntax?
There is also a paper on the the Support site that deals with setting the left outer join syntax that CR generates.

The document is cr85_left_outer_join.pdf or cr9_left_outer_join.pdf.

My gut feeling is that depending on the db that you are using, the isnull may work, but for SQL Server is will not work because of ANSI SQL.

I'm hoping that once this is cleared up I will put a FAQ on this.

Cheers

 

[lyanch]

Fred,

I take it back.. well sort of.. This only works for Sybase.. I tried Oracle, SQL Server and postgresSQL. But the logic is the same, the part that fails is:

*If* the original SQL before adding the isnull OR addition, returned the right dataset outside crystal..


They all return the same thing that crystal does. Sybase is the only one that always returns everything on the LHS no matter what the criteria on the RHS is..

When the *if* above is true, it is crystal that is removing the records, not the database..

Lisa