SQL Server Outer Joins problem in Crystal.

[chrishorn]

I have kind of an odd problem, I'll try to describe it and maybe if someones seen something similar they might be able to help.

My report has two tables (both are needed in main report as both are involved in creating of parameters so sub reports are out of the question).

I want to join the two tables on the column "DOTCLASS" with an outer join so that the rest of the record will display if that field is null.

If it was Oracle the syntax would simply be

SELECT *
FROM TABLE1, TABLE2
WHERE TABLE1.DOTCLASS =TABLE2.DOTCLASS
AND TABLE2.CATEGORY='CAT1'

however with SQL SERVER Crystal Defines it as
SELECT * FROM
{OJ TABLE1
LEFT OUTER JOIN TABLE2 ON
TABLE1.DOTCLASS = TABLE2.DOTCLASS
}
WHERE TABLE2.CATEGORY='CAT1'

Now that doesn't work as an outer join, it works the same as an inner join would. But if I put my where clause into the from statement it works as I'd expect. My problem is I have no idea how to duplicate that fix within crystal. How do I get that where statement into my join statement?

Thanks in advance!

 

[synapsevampire]

The following wouldn't return everything in Table1, if that's what the cryptic "the rest of the record will display if that field is null" means as it's not an outer join in Oracle.

SELECT *
FROM TABLE1, TABLE2
WHERE TABLE1.DOTCLASS =TABLE2.DOTCLASS
AND TABLE2.CATEGORY='CAT1'

You're explicitly stating that you want only TABLE2.CATEGORY='CAT1' on an inner join, you shouldn't expect anything else, and I think that you misunderstand how a Left Outer works.

Try adding something like the following to the record selection formula:
(
isnull({TABLE2.CATEGORY})
or
TABLE2.CATEGORY='CAT1'
)

There are other possible solutions, but since I don't even know what version of Crystal you're using, it's hard to help.

-k

 

[chrishorn]

Thanks for your help. Sorry, I screwed up my oracle query it's kind of irrelevant anyway.

Anyway I tried OR'ing it with an isnull already and it didn't seem to make a difference.

I'm using Crystal 8.0.

 

[lbass]

If you want to return all Table1 records, then use the left join from Table1 to Table2, but do not make a select on any columns from Table2. You can then control the display of the Table2 category field by creating a formula like:

if isnull({table2.category}) or
{table2.category} <> &quot;Cat1&quot; then &quot;&quot; else
{table2.category}

-LB

 

[synapsevampire]

An isnull() check has to be first, if it's after the OR it won't work.

This works for straight SQL, though it may not function for Crystal unless you create a custom data source (SQL).

LB points out one means that might handle the column, or you can use a decode in a SQL Expression to return the data already formatted by the server.

-k