[code]Here’s my scenerio.
Table 1 houses job_no from DB 1
Table 2 houses entity_no from DB 1
Table 3 houses fact details from DB 2
Need a query that returns Table 3 values that equal entity_no from table 2 and Does not equal job_no from table 1.
My query WHERE clause:
Table3.entity_no = Table2.entity_no
Table3.job_no <> Table1.job_no
Results are returning job_no’s that match ??? even though where clause has <> specifed. Appreciate any help.
citrix32 (BOB member since 2007-06-18)
Moving this into the DI Designer sub-forum.
dnewton 
(BOB member since 2004-01-30)
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)
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)
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)
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)
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)
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.
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)
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)
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)
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)
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)
ugh 
offcourse that should read:
and xx.entity_no is not null
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
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)
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)
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 nullFor DI this would be there where:
table1.job is null
and table2.entity is not nulland 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 nullBoth return your desired
3 - c
5 - e
Null - g
Regards
Erik
Evopmeer (BOB member since 2008-01-15)
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)
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)
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)
I know, I know. Damn.
Werner Daehn 
(BOB member since 2004-12-17)
But why doesnt the sql get “pushed” to the DB? The DB should handle this OK?
Evopmeer (BOB member since 2008-01-15)