Have problems to run *NOT IN* using Join statement

[sommererdbeere]

hi,
i have problems running the oracle sql below. i wanna do outter join for many to many cases. let say (an example), in database, i have

cust trans
1A 1A
2B 2B
2B
3C

by using the UNION ALL operator and NOT IN, i wanna get the result above for both side like cust = trans(+) and cust(+) = trans => cust(+) = trans(+)
however, i cannot use this in sql.. can anyone have any advice on to how to approach on this? many many many thanks.. i try to figure out this for 2 days already, still can't find any solution.. the following is what i had:


Select
a.ord trans_ord,
a.ptno trans_ptno,
b.transco transco,
c.ptno join_ptno,
c.state join_state,
b.size size,
acc_API.GET_ACCYEA(b.ACCID,1) accyea,
acc_API.GET_ACCPER(b.ACCID,1) accper,
COST_API.Get_Cost('america', b.ptno, '1', '*', '*') cost,
e.CID cid,
e.ADDID addid,
e.reg addr_reg_code,
c.ord join_ord,
c.cn c_cust_no,
c.pay pay,
From (
select * from (select b.ord t_ord,b.ptno t_ptno from
inv_his b
where (b.ord,b.ptno) not in (select c.ord, c.ptno from cusj c)
union all
select c.ord ord,c.ptno ptno from cusj c
) a, cusj c, custadd e
where b.ord = c.ord and
c.ptno = b.ptno (+) and
e.cID = c.cn and
e.aID = c.sh and
b.transcode= 'mail' and
c.CON='america' and c.ORD_I = 'yes' and
c.state != 'Cancelled'and
(upper( c.ORD ) not like upper( 'kr1%' ) and
upper( c.ORD ) not like upper( 'er2%' ) and
upper( c.ORD ) not like upper( 'TW3%' ) and
)

please be advice. many many many thanks..

somm

 

[ikkyu65]

On the one hand, there is a very simple way to solve cust(+) = trans(+) (full outer join)

SELECT a,b,c,d,... FROM CUST C, TRANS T, ...
WHERE
C.IDa = T.IDa (+)
AND C.IDb = T.IDb (+)
...

UNION -- no UNION ALL

SELECT a,b,c,d,... FROM CUST C, TRANS T, ...
WHERE
C.IDa (+) = T.IDa
AND C.IDb (+) = T.IDb
...

or with FULL OUTER JOIN in the FROM clause

SELECT a,b,c,d,...
FROM (CUST C FULL OUTER JOIN TRANS T ON
C.IDa = T.IDa
AND C.IDb = T.IDb)...


On the other hand , I can't understand well your query but I guess it has several mistakes, may be, because of the complex solution. I think it's not worthwhile to comment them if you decide to re-code youur query. So, when you do it the mistakes will be disappeared naturally. If not, write again.

Best regards.

Enrique.

 

[sommererdbeere]

hi,

i can't use FULL OUTER JOIN 'coz it is in oracle 8 version.. also, i only wanna do 1 outterjoin, so, how will i appoarch on that? below is the correct query taht i'm using.. please be advice... this is medium importance.. pls help.. many many thanks..

Select
b.ord trans_ord,
b.ptno trans_ptno,
b.transco transco,
c.ptno join_ptno,
c.state join_state,acc_API.GET_ACCYEA(b.ACCID,1) accyea,
COST_API.Get_Cost('america', b.ptno, '1', '*', '*') cost,
e.CID cid,
e.ADDID addid,
c.ord join_ord,
c.cn c_cust_no
From (
select * from (select b.ord t_ord,b.ptno t_ptno from
inv_his b
where (b.ord,b.ptno) not in (select c.ord, c.ptno from cusj c)

union all

select c.ord ord,c.ptno ptno from cusj c
) cusj c, custadd e
where b.ord = c.ord and
c.ptno = b.ptno (this is the line where i wanna do FULL OUTER JOIN) and

e.cID = c.cn and
e.aID = c.sh and
b.transcode= 'mail' and
c.CON='america' and c.ORD_I = 'yes'
(upper( c.ORD ) not like upper( 'kr1%' ) and
upper( c.ORD ) not like upper( 'er2%' )
)


many many thanks for those who help.. and please help.. thank you..

 

[ikkyu65]

Hi,

My english is not very good but I'll try to explain correctly.

I have an Oracle 9i DB, but, sorry, I can't understand why you say your query is ok:

- SELECT clause can't see INV_HIST columns because this table is inside an expression and this query expression only returns ORD and PTNO columns.SELECT can see columns returned by the query expression, NOT columns who simply belong to a table inside the expression.
- The same for WHERE clause.

Let's go with the FULL OUTER JOIN:

The trick you have made is to get all the keys within the subquery expression, between brackets, after FROM clause but, afterwards, you don't make anything with this.
I think you want to include this expression in the OUTER JOIN. Put an alias to this expression (for example, X) and code the OUTER JOIN:

WHERE
X.ORD = C.ORD (+)
AND X.PTNO = C.PTNO (+)

The rest of conditions:

Apart from the sintax errors mentioned above, realize that when there are exclusive INV_HIST keys, CUSTJ columns are NULL, so every condition that involves CUSTJ columns will be FALSE and therefore the row will be discarded.

I hope it can help you.

Regards.

Enrique

 

[sommererdbeere]

hi enrique,
your explanation is very clear, however, probably, i make you confuse.. let say from the above query, i only want: c.ptno = b.ptno (this is the line where i wanna do FULL OUTER JOIN)

also, include all of the conditions, do i use UNION ALL? if so, how would i go about? because i don't quit understand

WHERE
X.ORD = C.ORD (+)
AND X.PTNO = C.PTNO (+)

meaning, where does the "X" comes from? i'm very new to oracle, taht's why i'm seeking help from all of the experts here. thanks for ur help and time. thank you

somm