DI Joins not working:

system · February 20, 2008, 7:29pm

I had the same problem.but my tables were small like 300,000 rows.
I moved those tables to a same database.In you case it may be DB1 or DB2.Then made the join.It was fast enough and very end of process Iam dropping those temporary table.So the data base performance does not effect…

Thanks,
Shawn

DI.net (BOB member since 2007-07-26)

system · February 21, 2008, 9:54am

I think you are looking for a outerjoin construction rather than <>.

You are looking for any Job_no that doesn’t excist isn Table1 right?
If so, try this:

set Table3 as inner join
Table1 as outerjoin
where:
Table3.entity_no = Table2.entity_no
and Table3.job_no = Table1.job_no
and Table1.job_no is null

Regards

Evopmeer (BOB member since 2008-01-15)

system · February 21, 2008, 2:21pm

Thanks Evopmeer,

Yes I’m am looking for any job that doesn’t exists in table 1, and I tried your suggestion as follows:

(STAGE_FACTGLTRANS_TMP.GLENTITY = SF_ENTITY_TMP.GLENTITY__C) AND
   (STAGE_FACTGLTRANS_TMP.JOBNO = SF_CAPEX_C_TMP.PROJECT_ID_JOB_NUMBER__C) AND
   (SF_CAPEX_C_TMP.PROJECT_ID_JOB_NUMBER__C IS  NULL )

Where I set SF_CAPEX_C_TMP as the outer source in the OUTER Join tab and STAGE_FACTGLTRANS_TMP as the inner source.

But Now I recieve the followign error when attemptiong to validate:

[Query:Query]
BODI-1111077: Invalid query <Inner table <STAGE_FACTGLTRANS_TMP> of an outer join is being joined with table <SF_ENTITY_TMP>. Inner tables of an outer join cannot be involved in an inner join.>.

Any ideas on what is wrong here? Thanks again for you help.

citrix32 (BOB member since 2007-06-18)

system · February 21, 2008, 3:30pm

DI mukes up with inner and outer source…

SF_CAPEX_C_TMP should be listed as “inner source” in DI and vice versa for STAGE_FACTGLTRANS_TMP.

I am not completely sure on how DI handles the third table. In most SQL you can mix inner/outer joins no problem… but not sure for DI.

Good luck

Erik

Evopmeer (BOB member since 2008-01-15)

system · February 21, 2008, 5:03pm

Erik- Its Still not working

When I switch up the table sin the Outer Join tab I recieve the following errors

[Query:Query]
BODI-1111077: Invalid query <Unsupported combination of inner and outer joins. Table <SF_ENTITY_TMP> is being inner joined in the query transform that contains outer join(s). Please split the query transform into separate queries for the inner and outer joins.>.

citrix32 (BOB member since 2007-06-18)

system · February 22, 2008, 1:12pm

I tested it and I got it working. DI dont like inner and outer joins mixed you have to make all outer joins…

i.e.
I created 3 tables with 1 column only
X, XX and XXX with respectively columns “1”, “2” and “3”
I filled X with values a, b and c
XX with a and b
XXX only with a.

Now in DI, join the tables as you are used to… :
X.1 = XX.2
XX.2 = XXX.3

Now if you want to outer join XXX only you cannot do that. You also have to outerjoin XX to X.

So in the outerjoin tab you have to create 2 rows.

X - XX
XX - XXX

In order to “force” the innerjoin between X - XX you have to add something to your where… :
XX.2 is not null

Now the result should look like:

a - a - a
b - b - null

i.e. a single outerjoin to XXX. (missing the c - null - null option)

I hope this is not as clear as mud and you can make some sence of my example…

Regards

Erik

Evopmeer (BOB member since 2008-01-15)

system · February 22, 2008, 5:45pm

Thanks Erik, maybe I didn’t fully understand your explanation. I know you spelled it out for me, but can you align it to my exact case. Thanks for your patience.

Here’s what I have: 3 tables

X, column job_no
XX, column entity_no
XXX columns job_no, entity_no

my query in DI needs to return XXX columns job_no & entity_no

where
XXX.job_no <> X.job_no
XXX.entity_no = XX.entity_no

then I need to the opposite as well in a seperate query but once i have this working I should be okay.

citrix32 (BOB member since 2007-06-18)

system · February 24, 2008, 12:20pm

take a look at https://bobj-board.org/t/100493 this seems to be the same question.

Doing an inner join where table1.field1 <> table2.field2 is very different to outer join table1.field1 = table2.field2. This first is matching table1 with every record in table2 that does not have a matching field2 value, whereas the second is only finding a match if there is a matching field2 value…and you’d add a second query to then filter out the ones where table2.field2 is null (it did not find a match)

HuwD (BOB member since 2007-04-19)

system · February 25, 2008, 8:55am

No no no… you are back to your original problem of not having a “proper” outer join…

This is a proper outer join statement:

where 
XXX.job_no = X.job_no
and x.job_no is null
and XXX.entity_no = XX.entity_no

Now you would put XXX as “outer join” and X as inner join at the Outerjoin tab…

Altho that should work DI dont like it so to fix it:

Add a second row to the Outer join tab:
XX as outer and XXX as inner.
Alter your where clause:
The complete where clause would be

where 
XXX.job_no = X.job_no
and x.job_no is null
and XXX.entity_no = XX.entity_no
and xx.job_no is not null

This is to reinforce the Inner join which is now specified at 1 as an outer join.

I hope this will get you where you want to be.

Regards

Erik

Evopmeer (BOB member since 2008-01-15)

system · February 25, 2008, 5:08pm

Thanks again Erik,

The Last line of your code though does is not clear as Table xx does not have job_no field

citrix32 (BOB member since 2007-06-18)

system · February 26, 2008, 10:16am

bobjoe:

Thanks again Erik,

The Last line of your code though does is not clear as Table xx does not have job_no field
where 
XXX.job_no = X.job_no 
and x.job_no is null 
and XXX.entity_no = XX.entity_no 
and xx.job_no is not null  

ugh offcourse that should read:
and xx.entity_no is not null

bobjoe:

By the way here is the table names and all possible links that can be made.

I need fields from Table XXX only where job_no does not match and entity does match the other corresponding tables.

Tables:
XXX = STAGE_FACTGLTRANS_TMP
XX = SF_CAPEX_C_TMP
X = SF_ENTITY_TMP

Possible Links:
STAGE_FACTGLTRANS_TMP.JOBNO = SF_CAPEX_C_TMP.PROJECT_ID_JOB_NUMBER__C

STAGE_FACTGLTRANS_TMP.GLENTITY = SF_ENTITY_TMP.GLENTITY__C

SF_CAPEX_C_TMP.ENTITY__C = SF_ENTITY_TMP.ID

If you can show me the straight SQL - that would be helpful. My SQL attempt is below but I think its caught in a infinity loop. I’ve attached sample data from each of the three tables for review.
SELECT  

STAGE_FACTGLTRANS_TMP.JOBNO,
STAGE_FACTGLTRANS_TMP.GLCODE,
STAGE_FACTGLTRANS_TMP.ENTITYCODE,
STAGE_FACTGLTRANS_TMP.YR,
STAGE_FACTGLTRANS_TMP.GLENTITY,
STAGE_FACTGLTRANS_TMP.HOMECURRENCYAMOUNT,
STAGE_FACTGLTRANS_TMP.USDAMOUNT,
STAGE_FACTGLTRANS_TMP.PRD,
STAGE_FACTGLTRANS_TMP."HOME-CURR"

FROM 
STAGE_FACTGLTRANS_TMP, SF_CAPEX_C_TMP, SF_ENTITY_TMP
where 
STAGE_FACTGLTRANS_TMP.JOBNO NOT IN (SELECT PROJECT_ID_JOB_NUMBER__C FROM SF_CAPEX_C_TMP 
where 
STAGE_FACTGLTRANS_TMP.gltransid Not In (select STAGE_FACTGLTRANS_TMP.gltransid from STAGE_FACTGLTRANS_TMP, SF_CAPEX_C_TMP
where 
STAGE_FACTGLTRANS_TMP.JOBNO = SF_CAPEX_C_TMP.PROJECT_ID_JOB_NUMBER__C  and STAGE_FACTGLTRANS_TMP.gltransid in 
(select STAGE_FACTGLTRANS_TMP.gltransid from STAGE_FACTGLTRANS_TMP,SF_ENTITY_TMP 
where STAGE_FACTGLTRANS_TMP.GLENTITY = SF_ENTITY_TMP.GLENTITY__C)))

This should be the “proper” sql, tho that doesnt work in DI for some reason.


FROM      STAGE_FACTGLTRANS_TMP
join      SF_CAPEX_C_TMP on STAGE_FACTGLTRANS_TMP.JOBNO    = SF_CAPEX_C_TMP.PROJECT_ID_JOB_NUMBER__C 
left join SF_ENTITY_TMP  on SF_CAPEX_C_TMP.ENTITY__C       = SF_ENTITY_TMP.ID
where     SF_ENTITY_TMP.ID is null

This is what the DI query would look like:

FROM      STAGE_FACTGLTRANS_TMP
left join SF_CAPEX_C_TMP on STAGE_FACTGLTRANS_TMP.JOBNO    = SF_CAPEX_C_TMP.PROJECT_ID_JOB_NUMBER__C 
left join SF_ENTITY_TMP  on SF_CAPEX_C_TMP.ENTITY__C       = SF_ENTITY_TMP.ID
where     SF_ENTITY_TMP.ID is null
and       SF_CAPEX_C_TMP.PROJECT_ID_JOB_NUMBER__C is not null

Or you can re-write that to:

FROM      STAGE_FACTGLTRANS_TMP
,         SF_CAPEX_C_TMP
,         SF_ENTITY_TMP  
where     STAGE_FACTGLTRANS_TMP.JOBNO    = SF_CAPEX_C_TMP.PROJECT_ID_JOB_NUMBER__C 
and       SF_CAPEX_C_TMP.PROJECT_ID_JOB_NUMBER__C is not null
and       SF_CAPEX_C_TMP.ENTITY__C       = SF_ENTITY_TMP.ID
and       SF_ENTITY_TMP.ID is null

Now above where-clause you can copy paste into DI and in the Outer join you should put 2 rows
Outer join - inner join

STAGE_FACTGLTRANS_TMP - SF_CAPEX_C_TMP
SF_CAPEX_C_TMP - SF_ENTITY_TMP

You lost me tho… you want everything that doesnt match in STAGE_FACTGLTRANS_TMP but you are selecting data from that same table which I think will only return empty info?


STAGE_FACTGLTRANS_TMP.JOBNO
1
3
4
5


SF_CAPEX_C_TMP.PROJECT_ID_JOB_NUMBER__C SF_CAPEX_C_TMP.ENTITY__C 
1                                        a
2                                        b
3                                        c
4                                        d
5                                        e
6                                        f

SF_ENTITY_TMP.ID 
a
b
c
d
e
f
g

Given above data for the tables, you want to see:
b - 2 -
f - 6 -

Right? Atleast that is what I get from you… but I may be wrong.

As a general remark STAGE = TMP generaly so for one I dont get the added TMP part

Evopmeer (BOB member since 2008-01-15)

system · February 26, 2008, 4:40pm

No actually I need to return all rows from table three that have the following:

Match entity in table 2 (i.e. a,b,c,e,g)
Do not match job in table 1 (i.e. 3,5,6, Null)

So if:

Table 3
Job entity
1 a
2 b
3 c
4 –
5 e
6 –
Null g

Table 1
Job
1
2
4

Table 2
Entity
a
b
c
d
e
f
g

Result I want is
Job Entity
3 c
5 e
Null g

If you can help with the sql, I can use SQL transform in DI instead.

citrix32 (BOB member since 2007-06-18)

system · February 27, 2008, 9:38am

But that is exactly what I posted earlier

select table3.Job, Table3.entity
from      table3
join      table2 on table3.entity = table2.entity
left join table1 on table3.Job = Table1.job
where     table1.job is null

For DI this would be there where:

 table1.job is null
and table2.entity is not null

and make 2 rows in Outer join
Table3 - Table2
Table3 - Table1

Effectively making this query:

select table3.Job, Table3.entity
from      table3
left join table2 on table3.entity = table2.entity
left join table1 on table3.Job = Table1.job
where     table1.job is null
and       table2.entity is not null

Both return your desired
3 - c
5 - e
Null - g

Regards

Erik

Evopmeer (BOB member since 2008-01-15)

system · February 27, 2008, 9:45am

DI can not cope with an outer join and a filter to restrict WHERE Field IS NULL in the SAME QUERY. You need to split this into 2 querys to achieve.

In Query1 put the two tables to perform the outer join, put in the matching fields in the where clause.

In Query2 put in the 3rd table to inner join, in the where clause put in the join matching fields and there you can put in the field from the 1st (outer) join to filter out the non-matches (where field IS NULL).

As posted earlier, this is mentioned in the other thread

HuwD (BOB member since 2007-04-19)

system · February 27, 2008, 1:27pm

ugh you are not kidding… I just tested it and you are right

There is however no need to do this in 2 seperate queries… DI will perform the second join in memory (IIRC) which may be very slow.

Just make 2 outer-joins then enforce the inner join by doing “is not null” like I suggested. But appearently you need 2 queries to do “is null” and “is not null” ??

Evopmeer (BOB member since 2008-01-15)

system · February 27, 2008, 1:59pm

yes, this is one of my pet hates about DI

a suggestion to seperate join and where conditions has been added to the suggestions thread in the top-level DI forum

HuwD (BOB member since 2007-04-19)

system · February 28, 2008, 9:17am

I know, I know. Damn.

Werner Daehn (BOB member since 2004-12-17)

system · February 28, 2008, 10:01am

But why doesnt the sql get “pushed” to the DB? The DB should handle this OK?

Evopmeer (BOB member since 2008-01-15)

system · February 28, 2008, 10:08am

First the DI optimizer has to understand the join clause. Only then it can pushdown the sql. It is not as easy as copy/paste the where clause into the sql statement, the optimizer has to fully understand the case and then can pushdown e.g. just parts of the join in case of an hereogenous join.
As the DI optimizer does not understand that, we are screwed.

Werner Daehn (BOB member since 2004-12-17)

system · February 28, 2008, 1:26pm

Thanks All,

I split it into seperate Queires as suggested and got it working finally. That is seperated the outer join and the is null statement.

Thanks again…

citrix32 (BOB member since 2007-06-18)