Problem with Outer Join 1

[woodyinoz]

Hi all,

I am using a SQLBase 7.6.1 and Crystal 9.

I have a simple link between 2 tables and am looking to show all records in the 1st table regardless of whether they have a link in the 2nd table.

I assumed that using an outer join rather than an inner join would solve this problem but I get the same results whether I use an inner or outer join......

I have written some SQL code which performs an outer join between these two tables and that works but in Crystal I still only get the records that match in the 2 tables.....

Am I missing something here? Any advice would be much appreciated... Are there any alternative methods of showing these records?

Thanks in advance,

Woody.

 

[mrmtek]

in the database expert use a left outer join to join your two tables

 

[lbass]

Go to database->visual linking expert and make sure the join is a left join FROM table 1 to table 2, and then make sure you don't have any selection criteria on the second table.

-LB

 

[woodyinoz]

Ok......

The reason I couldn't see any results was because Ihad some selection criteria on the 2nd table.... However, I need to have this selection criteria!! Is there any way I can still have selection criteria on the 2nd table but still have a left outer join?

Thanks,

Woody

 

[lbass]

You should explain what you're trying to do and show the selection formula.

-LB

 

[kskid]

If you need a selection criteria on an outer joined table, then try the following

...
(
isnull({table.field}) or
{table.field} = "X"
)


Cheers,
-LW

 

[woodyinoz]

Ok,

I have 2 tables:
Master Table - "Part" table
Child Table - "Trace" table
These are linked together using the Part_ID field.
I have used a left outer join to link these tables because I want to display all part id's from Part table, even if they are not in the Trace table.

Just running the report on the above setting works fine.
However, I also need to have some parameters set up on both tables.
The problem I get is when I set some parameters on the Trace table.... all unmatched records disappear.
The selection formula is below:

(if isnumeric(left({?Length},1)) then
tonumber({PART.USER_5}) = val({?Length}) else
if left({?Length},1) = "<" then
tonumber ({PART.USER_5}) < val(mid({?Length},2)) else
if left({?Length},1) = ">" then
tonumber({PART.USER_5}) > val(mid({?Length},2)) else
tonumber({PART.USER_5}) > 0) and
(if isnumeric(left({?Width},1)) then
tonumber({PART.USER_6}) = val({?Width}) else
if left({?Width},1) = "<" then
tonumber ({PART.USER_6}) < val(mid({?Width},2)) else
if left({?Width},1) = ">" then
tonumber({PART.USER_6}) > val(mid({?Width},2)) else
tonumber({PART.USER_6}) > 0) and
(if isnumeric(left({?Thickness},1)) then
tonumber({PART.USER_7}) = val({?Thickness}) else
if left({?Thickness},1) = "<" then
tonumber ({PART.USER_7}) < val(mid({?Thickness},2)) else
if left({?Thickness},1) = ">" then
tonumber({PART.USER_7}) > val(mid({?Thickness},2)) else
tonumber({PART.USER_7}) > 0) and
{TRACE.APROPERTY_1} like "*"+{?Plate No}+"*" and
{TRACE.APROPERTY_2} like "*"+{?Cast No}+"*" and
{TRACE.APROPERTY_3} like "*"+{?Cert No}+"*" and
{PART.USER_1} like {?Enter Contract & Structure}

I know that the problem is the
{TRACE.APROPERTY_1} like "*"+{?Plate No}+"*" and
{TRACE.APROPERTY_2} like "*"+{?Cast No}+"*" and
{TRACE.APROPERTY_3} like "*"+{?Cert No}+"*" and
part of the code because as soon as I take it out I get to see all records again.

Any suggestions?

 

[woodyinoz]

Just tried that code but no success.... anything else I can try?

 

[lbass]

First remove the references to these fields in the selection formula. Then create formulas like the following:

if isnull({TRACE.APROPERTY_1}) or
not ({TRACE.APROPERTY_1}like "*"+{?Plate No}+"*") then 0 else {table.amt} //or "then "" else {table.field}

I don't know what you want to look at for these fields--an amount, a count, or what, but the above format will display records that match your criteria--without eliminating records from the left table. The formulas allow not only for nulls, but for values other than the parameter values, that if eliminated, would eliminate records from the left table.

Repeat for each of the other two parameters on the Trace table.

-LB

 

[woodyinoz]

Hi, sorry, me again!

I have used the above code but I now get the following error:

A boolean is required here.

I get this on the {table.amt} or the {table.field} part, which ever I use.
The APROPERTY fields hold a mixture of characters and numbers and I want the user to be able to enter a part of the string (they may not know it all) and the result will show all values containing the entered values.

Woody.

 

[woodyinoz]

I've cracked it!

It was simple but I wouldn't have been able to get to the bottom of it withou this forum...

The formula I used was.

(
if {?Plate No} = "*" then
isnull({TRACE.APROPERTY_1})
or
{TRACE.APROPERTY_1} like "*"+{?Plate No}+"*"
else
{TRACE.APROPERTY_1} like "*"+{?Plate No}+"*"
)

Simple eh?!

Thanks again,
Woody

 

[mtrang81]

Hi,

I'm having a similar problem and tried to follow the selection formulas outlined but unsuccessfully.

My main table is "People Count History" which is FULL-OUTER joined to "People Count Current YTD". I have two parameters, one is based on the field {People_Count_History.Ops Center} and the other is {People_Count_Current_YTD.Date}.

I think the problem is the 2nd parameter.
Here's my selection formula:

{People_Count_History.Ops Center} = {?Operations Center} and
{People_Count_Current_YTD.Date} = {?Report Month}

What I ultimately want is for the report to display all records in either the History and/or Current YTD table even if there is no matching records in the other table. It can show 0 or "blank" for these rows.

For example, some months, I might not have any Current YTD data for a particular department that exists in my History table. And other months, I might have data for a new Department in my Current YTD table that does not exist in the History table. I want my report to show both when I preview it.

Any help is greatly appreciated.
Thanks!

~Trang