Query Help

[ClowneUser]

What I need to do is match invoice numbers to delivery not numbers, but recently we had had an upgrade to the database/erp system, and all the ‘old’ delivery numbers are in one table and the new are in a different table.


Invoice_item_tab – this has the ‘old’ del_note_no
Customer_order_delivery_tab – this has the ‘new’ del_note_no
Invoice_tab – this has all the invoice_no

So what I would like to do is to write a simple query that looks at the invoice_tab for the invoice_no and see if it can match a del_note_no in ether the invoice_item_tab or the customer_order_delivery_tab.

Is this possible?

Any help would be much appreciated.
Mick.

 

[LKBrwnDBA]

You could try something like this:

Select Invoice_No, O.Del_Note_No Old_Note, N.Del_Note_No New_Note
  From Invoice_Tab I, Invoice_Item_Tab O
     , Customer_Order_Delivery_Tab N
 Where O.Del_Note_No(+) = I.Del_Note_No
    Or N.Del_Note_No(+) = I.Del_Note_No;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ClowneUser]

hi,

thanks for your reply, But I get the error 'outer join operator (+) not allowed in operand of OR or IN..


Any ideas how to get round this?

Thanks

Mick.

 

[LKBrwnDBA]

Try 'and' instead of 'or'.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb