left outer join and report/select expert

[HoosHot]

hi, i have an outer join in my report. for example:
WHERE
TABLE1."COL1" = TABLE2."COL1"(+) AND
TABLE1."COL2" = TABLE2."COL2"(+)

and, i'm using the select expert to add these to my where clause:
{TABLE2.COL3} = abc

however, when doing an outer join, i need to my 3rd where-clause to also have the outer join syntax:
{TABLE2.COL3}(+) = abc

where in the select expert or record selection formula editor can i add in the (+) ? i tried:
{TABLE2.COL3}(+) = abc
and i get "the remaining text does not appear to be part of the formula".

i also tried:
{TABLE2.COL3(+)} = abc
and i get "the field name is not known".

any suggestions on how i can specify my 3rd where clause to also have the outer join?

thanks!

 

[FVTrainer]

The join type is not determined in the record selection formula but in the Visual Linking Expert. Which version of Crystal are you using?

 

[pandpp]

In the Visual Linking Expert (Database menu item), select the link, change the Link Options to Left Outer.

Nothing simpler.

Peter.

 

[HoosHot]

product 8.0.1.0
crw32 8.0.0.441

 

[HoosHot]

hi,

in Database/Visual Link Expert, i do have table1 and table2 as left outer join. that's how i'm already able to get:
WHERE
TABLE1."COL1" = TABLE2."COL1"(+) AND
TABLE1."COL2" = TABLE2."COL2"(+)

notice, now, i'm doing the record select expert so i can add another where clause (not a table join) of:
{TABLE2.COL3} = abc

but i need this to be:
{TABLE2.COL3} (+) = abc

thanks for any advice

 

[pandpp]

Make it easier on us then.

Use Show SQL Query, grab the SQL that you have, post it for us to see, then, modify it to what you want it to be, then, post that as well, labelling each one respectively.

That way we can see what you currently have & what you finally want to have.

 

[HoosHot]

current sql:
SELECT
TABLE1."COL1", TABLE1."COL2",
TABLE2."COL1", TABLE2."COL2", TABLE2."COL3"
FROM
"OWNER"."TABLE1" TABLE1,
"OWNER"."TABLE2" TABLE2
WHERE
TABLE1."COL1" = TABLE2."COL1"(+) AND
TABLE1."COL2" = TABLE2."COL2"(+) AND
TABLE2."COL3" = abc

desired sql (only difference is the outer join syntax in the last where-clause):
SELECT
TABLE1."COL1", TABLE1."COL2",
TABLE2."COL1", TABLE2."COL2", TABLE2."COL3"
FROM
"OWNER"."TABLE1" TABLE1,
"OWNER"."TABLE2" TABLE2
WHERE
TABLE1."COL1" = TABLE2."COL1"(+) AND
TABLE1."COL2" = TABLE2."COL2"(+) AND
TABLE2."COL3"(+) = abc

 

[pandpp]

Wouldn't it just be easier to have

SELECT
TABLE1."COL1", TABLE1."COL2",
TABLE2."COL1", TABLE2."COL2", TABLE2."COL3"
FROM
"OWNER"."TABLE1" TABLE1,
"OWNER"."TABLE2" TABLE2
WHERE
TABLE1."COL1" = TABLE2."COL1"(+) AND
TABLE1."COL2" = TABLE2."COL2"(+) AND
(TABLE2."COL3" = abc OR TABLE2."COL3" IS NULL)

which is, I believe, what you are trying to acheive.

 

[HoosHot]

actually, if i'm not mistaken (and i ran this in sql to test it out),
an outer join:
TABLE2."COL3"(+) = abc
is not equivalent to:
(TABLE2."COL3" = abc OR TABLE2."COL3" IS NULL)

an outer join says give me back results even if no rows exist in table2. with your suggestion, results only come back if rows exist in table2.

outer join result:
table1.col1 table1.col2 table2.col1 table2.col2 table2.col3
a b c d abc
e f <null> <null> <null>

your suggestion result:
table1.col1 table1.col2 table2.col1 table2.col2 table2.col3
a b c d abc

 

[HoosHot]

correction:

outer join result:
table1.col1 table1.col2 table2.col1 table2.col2 table2.col3
a b a b abc
e f <null> <null> <null>

your suggestion result:
table1.col1 table1.col2 table2.col1 table2.col2 table2.col3
a b a b abc

 

[synapsevampire]

In the record selection formula place:

isnull({TABLE2.COL3})
or
{TABLE2.COL3} = &quot;abc&quot;

Don't play with the SQL for this column, Crystal will pass it afterwards. In fact, you shouldn't be editing the SQL at all, just add the tables, right click the links and select left outer joins.

Once you edit the SQL the Record Selection formula becomes iffy.

Also, Crystal requires that the insull test be first in the criteria.

-k

 

[HoosHot]

i tried pandpp's
(TABLE2.&quot;COL3&quot; = abc OR TABLE2.&quot;COL3&quot; IS NULL)
and
synapsevampire's
isnull({TABLE2.COL3})
or
{TABLE2.COL3} = &quot;abc&quot;

and the generated sql (from database / show sql query)is:
(TABLE2.&quot;COL3&quot; IS NULL OR TABLE2.&quot;COL3&quot; = abc OR )
again, i need it to be:
TABLE2.&quot;COL3&quot;(+) = abc
an outer join is not equivalent to this &quot;null&quot; logic you are suggesting.

btw, i am not editing the the sql in crw. i am merely doing a record select expert (or edit seletion formula).

let me try to explain an outer join in a different way:
table employee (name) has 2 rows: joe and jane. table employee_skill (name, skill) has 1 row: jane,programmer (joe has no skills and therefore is not in this table).

select e.name, es.skill
from employee e, employee_skill es
where e.name = es.name(+)
and es.skill(+) = 'programmer'
gives you 2 rows:
jane programmer
joe <null>

however, what you guys are suggesting with the null would be:
select e.name, es.skill
from employee e, employee_skill es
where e.name = es.name(+)
and (es.skill is null or es.skill = 'programmer')
gives you only row:
jane programmer

in this example, i want 2 rows to come back. that's how an outer join works. i need crw to somehow allow me to add this where clause (TABLE2.&quot;COL3&quot;(+) = abc) to my report, without editing the sql.

thanks

 

[pandpp]

What DB are you using this on? Your syntax shouts Oracle.

Now, when I plug

SQL> SELECT
2 Employee.Name,
3 Employee_Skill.Skill
4 FROM
5 Employee,
6 Employee_Skill
7 WHERE
8 Employee.Name = Employee_Skill.Name (+) AND
9 (Employee_Skill.Skill = 'Programmer' OR
10 Employee_Skill.Skill IS NULL)
11 /

NAME SKILL
---------- ---------------
Jane Programmer
Joe

into Oracle, you can clearly see that the IS NULL does work, as expected.

The problem appears to be else where. I understand what you are trying to do with the LEFT JOIN.

 

[FVTrainer]

Not to pile on here, but I'm in complete agreement with sv and pandpp. The problem, HoosHot, is in something you're not telling us.

 

[HoosHot]

ok, how about this?

create table z_employee
(name varchar2(10))

create table z_employee_skill
(name varchar2(10)
, skill varchar2(10))

insert into z_employee
values ('jane')

insert into z_employee
values ('joe')

insert into z_employee_skill
values ('jane', 'programmer')

insert into z_employee_skill
values ('jane', 'dba')

insert into z_employee_skill
values ('joe', 'dba')

select e.name, es.skill
from z_employee e
, z_employee_skill es
where e.name = es.name(+)
and es.skill(+) = 'programmer'
brings back desired result:
jane programmer
joe <null>

select e.name, es.skill
from z_employee e
, z_employee_skill es
where e.name = es.name(+)
and (es.skill is null or es.skill = 'programmer')
brings back only 1 row:
jane programmer

sorry, i should have made my data example earlier more representative of my real data.

thanks for everyone's continued help.

 

[FVTrainer]

Ok, so does your db support SQL-92? Try the following, just to see what the result is (I'm using Transact-SQL, so the varchar2 is a varchar and I've made the tables temp tables, and I changed your data for 'joe' so that his skill was null, which was part of your original problem; joe can't come back in the 2nd query if his skill is &quot;dba&quot; as you had it assigned in the last post.):

create table #z_employee
(name varchar(10))

create table #z_employee_skill
(name varchar(10)
, skill varchar(10))

insert into #z_employee
values ('jane')

insert into #z_employee
values ('joe')

insert into #z_employee_skill
values ('jane', 'programmer')

insert into #z_employee_skill
values ('jane', 'dba')

insert into #z_employee_skill (name)
values ('joe')

select e.name, es.skill
from #z_employee e inner join #z_employee_skill es on e.name = es.name
where e.name = es.name
and es.skill = 'programmer'
brings back one row

select e.name, es.skill
from #z_employee e left outer join #z_employee_skill es on e.name = es.name
where
(es.skill is null or es.skill = 'programmer')
brings back two rows:
jane programmer
joe NULL

The second result is also what I experienced in Crystal by:

1) Setting the join type to left outer join

2) Setting the record selection to sv's formula, namely:
IsNull({es.skill}) or {es.skill} = &quot;programmer&quot;

 

[lbass]

I think the bottom line issue here is why you are using the left join in the first place. If you want to return all employees from the employee table, you can't use ANY select on the employee skills table. Even &quot;IsNull({es.skill}) or {es.skill} = &quot;programmer&quot; will exclude those employees in the employees table who have some skill other than programmer, e.g.:

jane programmer
joe null
jackson manager

Jackson will not show up using these criteria. So if you want Joe AND Jackson to appear, but without a skill showing up, then you have to remove the criteria from your selection statement and leave the left join. Then in your report, create a formula like:

if isnull({es.skill}) or
{es.skill} <> &quot;Programmer&quot; then &quot;&quot; else &quot;Programmer&quot;

This will give you:

Jane Programmer
Joe
Jackson

If each employee can have multiple skills including &quot;Programmer&quot; then change the formula to {@programmer}:

if isnull({es.skill}) or
{es.skill} <> &quot;Programmer&quot; then 1 else 0

Group on employeeID, insert a summary on the formula, and then create another formula:

if sum({@programmer},{employee.ID}) > 0 then &quot;Programmer&quot; else &quot;&quot;

Then suppress the details.

-LB

 

[synapsevampire]

What I stated works, set the left join in Crystal and use the apporpriate criteria, it will return those that are null (Joe) and those that are 'Porgrammer'

If you'd bothered to post the SQL (rather than some part of it), I would have continued to assist.

It's true that it might not work in some cases, but there are workarounds.

Write a View or SP, that's what I'd do.

Good luck.

-k

 

[HoosHot]

hi all,

i wanted to thank everyone for their help. unfortunately, no matter how many times i tried, your suggested null logic does not bring back the desired number of rows. i ended up creating a view (with the outer join logic) and including that view in the crw to make it work.

thanks, though, for everyone's input.