Left Outer Join

[NiceArms]

I have 4 tables:
1. Account
2. Customer
3. Sundry
4. Transactions

Account to Customer = Inner join
Account to Sundry = Inner Join
Account to Transactions = Left Outer

I am trying to return data from the Account table whether the {Transactions.tran-code} = 'SC3000' or not.

I think I have found the source of the issue as per below but I don't know how to fix it.

Selection Criteria:

{Account.client-code} = {?Code}
and {Account.dt-datinstr} in {?Date_range} 
and {Customer.dr-rectype} in 'D'
and split({sundry.si-logical},";")[12] = "0"
and ({Transactions.tran-code} = 'SC3000')

Now I know that:

and ({Transactions.tran-code} = 'SC3000')

Causes the Left Outer Join to act like an Inner Join. Which is what is causing nothing to return

I know the solution to this is SQL is to place:

and ({Transactions.tran-code} = 'SC3000')

Into the FROM statement not the WHERE statement but Crystal 9 does not allow me to edit the SQL statement.

How do i get round this issue?

/Nice

 

[IanWaterman]

Change

and ({Transactions.tran-code} = 'SC3000')

to

and (isnull({Transactions.tran-code}) or {Transactions.tran-code} = 'SC3000')

Ian

 

[lbass]

You can CREATE the SQL in v.9 by using a command as your datasource (database expert->your datasource->add command (above the table list)), and then you will be able to add the criterion in the FROM clause and still maintain the left outer join.

Otherwise, you cannot add criteria--even with an isnull clause--since you would lose some records where the transaction exists (not null and not the desired code). You would then test for the presence of the code in a formula within the report.

-LB

 

[NiceArms]

Thank you both for your input

I have gone with lbass's suggestion.