Confusion about Left outer Join in CR XI

[aamkumar]

I am little confused with Left outer join in Data Expert for CR XI.
In first scenario I m dealing with around 3 tables out of which table A and Table B are INNER JOINED. So I am getting all the records for Table A plus where the values match with Table B

Next scenarion I do a Left outer join between table A and B and I get All values for A plus Null values for B where records dont match.

Next scenario I do a Left outer join between A and B and drop in a Constraint like TableB.Colum1<>30 (I am trying to get all values that are not equal to 30 along with the NULL values). But the result set I am getting is showing me values that are <>30 but excluding the NULL values. It seems as if my left outer join is behaving like inner join.

Please give your suggestions

 

[synapsevampire]

What's the advantage to showing pseudo code for things like:

Next scenario I do a Left outer join between A and B and drop in a Constraint like TableB.Colum1<>30 (I am trying to get all values that are not equal to 30 along with the NULL values). But the result set I am getting is showing me values that are <> 30 but excluding the NULL values.

Show what you actually tried, I never understand why people state that they can code yet won't show their code.

I suspect you want:

(
TableB.Colum1 is null
or
TableB.Colum1 <> 30
)

-k

 

[aamkumar]

I think I tried what you are saying.....and it didnt work

Clear picture for you.....

Scenario 1.

select TabA.*, TabB.Col2 from TabA inner join TabB on TabA.Col1=TabB.Col1

Scenario 2.


select TabA.*, TabB.Col2 from TabA left outer join TabB on TabA.Col1=TabB.Col1

Gives Null values for TabB.Col2 where it dosen't Match

Scenario 3.

select TabA.*, TabB.Col2 from TabA left outer join TabB on TabA.Col1=TabB.Col1

and the constraint condition for record selection is given in record selection expert as TabB.Col2<>30

Output expected is all ''ALL null values along with <>30 values...

Output Received is <>30 values only.

I already TableB.Colum1 <> 30 or TableB.Colum1 is null

it still only gives <>30 results....

I think this is a more clear description of the problem.

ALL these steps are done in CRXI 's Dataexpert and Record expert sections....without add command

 

[groggle]

My experience is that a left outer join works fine from table A to B until you include a record selection criteria for table B.

Once you include a record selection for table B, even if it is joined by a left outer join, is that the join indeed behaves like an inner join.

A brilliant solution to this issue that I have only learned recently is to use an SQL command (Add Command, select fiels from table name where criteria = required criteria) to obtain the records you need from table B and join to the SQL Command using a left outer join. Whilst this slows down the performance of the report, it works perfectly.

 

[aamkumar]

Thanks groggle yes you are exactly right SQL ADD command is the only solution I was able to get hold of to resolve this issue but I was thinking of getting something from within the crystal only with simple table joining in Dataexpert.

I have another confusion about how the left outer join behaves with NULL conditions ...I will post my code next to this one by tomorrow to discuss...

THanks

 

[lbass]

You can do this in the record selection formula by using:

(
isnull(TableB.Colum1)
or
TableB.Colum1 <> 30
)

Make sure the null check is first.

-LB

 

[aamkumar]

the other question I had was about using NULL option like the one shown below in SQL

Select * from Table A TA,

Inner Join TabB TB on TB.Col1=TA.Col1.

Left outer join TabC TC.Col1=TA.Col1 and TC.Col2 isnull.

Where

TB.Col3=33

othercase is ...

Select * from Table A TA,

Inner Join TabB TB on TB.Col1=TA.Col1.

Left outer join TabC TC.Col1=TA.Col1

Where

TB.Col3=33

and TC.Col2 isnull


Why is the behavior different in both the cases....How is the Query one working or flowing by taking into consideration isnull value.


Please Advice..


thanks

 

[robinCQ]

I'm not a Crystal expert, but I've looked at this in pure SQL terms.

You should try experimenting with simpler test cases, so that you can see more easily what's going on. For example I broke your snippets down as follows (these ones are actual code though)

select *
from ta
left join tb on (ta.a = tb.a and tb.b is null)


select *
from ta
left join tb on ta.a = tb.a
where tb.b is null


Your confusion seems to stem from the order in which your filtering is being applied.

The first case will include records from 'ta' against records from 'tb' which DO NOT match the NULL clause, because of the left join. The second case ONLY includes records which match the NULL clause because they're being filtered out after the tables have been joined.

The result sets would be identical if you changed the join on the first one to be INNER. However, with a guess at what you're trying to do, I'd suggest that the second case is better suited to your needs -- complex join conditions are more likely to lead to problems later.

Robin

 

[robinCQ]

Sorry, I should have said:

The first case will include records from 'ta' against *BLANK* (i.e. completely null) records from 'tb' which DO NOT match the NULL clause, because of the left join.

 

[kwtx04rm]

Can anyone help in translating a Crystal Report "Command" into Crystal Report "Record Selection Criteria"? My SQL knowledge is limited, but I can determine tables and fields the command uses.

There are 7 tables as follows (in parentheses is the non alias table name):
amportfolio p (AMPORTFOLIO)
ammodel m (AMMODEL)
ammodel m1 (AMMODEL_1)
amcomputer c (AMCOMPUTER)
amasset a (AMASSET)
amastprojdesc pd (AMASTPROJDESC)
amproject pr (AMPROJECT)

And this is what I've been able to translate into Crystal Report record selection criteria:
{AMMODEL_1.NAME} IN ["HOSTS"] AND
{AMPORTFOLIO.SEASSIGNMENT} IN [0,1] AND
{AMPORTFOLIO.BOFA_MGMTGROUP} <> 5

The problem I am having is figuring out the rest of the Crystal Report record selection criteia...especially the part where the Command has nested Select statements. Below is what displays when editing the command:

SELECT a.serialno, c.tcpiphostname, m1.name as parent_model, c.computertype, p.bofa_mgmtgroup, m.name as model, p.lparentid as parent_portfolio
FROM amportfolio p
LEFT OUTER JOIN ammodel m ON p.lmodelid = m.lmodelid
LEFT OUTER JOIN ammodel m1 ON m.lparentid = m1.lmodelid
LEFT OUTER JOIN amcomputer c ON p.lportfolioitemid = c.litemid
LEFT OUTER JOIN amasset a on p.lastid = a.lastid
WHERE M1.NAME = 'HOSTS'
AND P.SEASSIGNMENT IN (0,1)
AND P.BOFA_MGMTGROUP <> 5
AND (P.lparentid NOT IN
(SELECT p.lportfolioitemid
FROM amportfolio p
LEFT OUTER JOIN ammodel m ON p.lmodelid = m.lmodelid
LEFT OUTER JOIN ammodel m1 ON m.lparentid = m1.lmodelid
WHERE m1.name = 'SERVER') OR P.LPARENTID = 0)
AND LPORTFOLIOITEMID NOT IN
(SELECT P.LPORTFOLIOITEMID
FROM amportfolio p
LEFT OUTER JOIN ammodel m ON p.lmodelid = m.lmodelid
LEFT OUTER JOIN ammodel m1 ON m.lparentid = m1.lmodelid
LEFT OUTER JOIN amastprojdesc pd on p.lastid = pd.lastid
LEFT OUTER JOIN amproject pr on pd.lprojid = pr.lprojid
WHERE m1.name = 'HOSTS'
AND PR.BOFA_SOURCE = 2
AND PR.CODE = '32')

Many thanks if you can assist!!!

 

[lbass]

Please start a new thread, as your topic is totally unrelated to the current thread. When you do, please explain why you are trying to eliminate the command, when it is the best solution for the current requirements.

-LB