outer join with string value

[beckybear]

I am running crystal 7 with oracle 8.1.7 and would like to put the following type of query in a crystal report:

select a.name, b.street
from a, b
where a.key = b.key (+)
and b.type (+) = 'HOME'

I understand how to do the outer join with the field linking, but I don't know how to do it in the select expert with the 'HOME' string. I can edit the SQL directly and accomplish this, but as I will not be maintaining this report, I'd like a straight UI solution.

I cannot use b.type is null or b.type = 'HOME' because there could be other types that I don't want to select.

I also do not have the option of creating a view at this time, which would be my preferred method.

Thanks.

 

[synapsevampire]

Isn't the final where clause you've created redundant redundant (always have to say that twice)?

because "and b.type (+) = 'HOME'" says to return everything that is 'HOME', and everything else...

Now assuming that you still want this sort of thing:

You've two options, do it externally to CR (View or SP), or paste your SQL in the Database->Show SQL Query.

But once you go manual on the SQL, Crystal can't help you out with visual tools, and may even cause problems later when adding tables, etc.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[beckybear]

Actually, it's not redundant and selects only the 'HOME' records or returns nulls.

I was hoping there would be a solution other than editing the sql directly, since someone less technical than I am will be maintaining the report. Guess I'll just have to go beat up on someone to get permission to create a view.

 

[synapsevampire]

Ahhh, forgive me, I wasn't thinking of the return value, just the row count.

You can do the equivalent in CR (and of course SQL) using a SQL Expression to create a conditional column (which is what your join does), and it will offload the processing to the server. In Oracle 8 or below, use decode, I think that you can use a case in 9 or on SQL Server.

So the SQL Expression in Oracle 8 would read:

decode ("YourTable"."YourField", 'Home' , 'Home' , null)

And you can replace the null with some other text if that's more useful.

SQL Expressions are finicky, but they do work.

For reusability, maintenance, etc, a View or SP is a better approach.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com