.NET and Oracle: Joining a table to an empty set of another table

Question

Hi all,

I am trying to join tableA with some data to an empty set of another tableB. The main purpose is to get all the columns of tableB. I do not need any data of tableB.

I have constucted the following SQL:

SELECT uar.*, s.screen_id, s.screen_name

FROM crs_screen s

LEFT JOIN crs_user_access_right uar

ON s.rid IS NOT NULL AND uar.rid IS NULL

This SQL runs perfectly on TOAD but it returns an error when I use it in my VB.NET OracleDataAdapter.Fill(DataTable) statement.

It is fine if there is a work-around to achieve same effect. Thank you very much.

---

Error message:

OCI-22060: argument [2] is an invalid or uninitialized number

---

Config:

.NET framework: 1.1

Oracle 9i

Answer 1

Perhaps the problem is not with the statement, but something about the 2nd column of the "uar" table.
Can you try the query with a different table to confirm this?

Answer 2

Try listing out all your columns explicitly and review all the data types - particular the second column of uar.

Answer 3

I'm not too familiar with Oracle (so I can't say if it is an Oracle issue specifically), but perhaps rephrasing your SQL like this would work:

SELECT
  uar.*, s.screen_id, s.screen_name
FROM 
  crs_screen s 
    LEFT JOIN crs_user_access_right uar ON uar.rid <> uar.rid

You shouldn't need the comparison on s.rid, since being on the left side, crs_screen will always be included in the left outer join.

Answer 4

Which version of the client driver are you running? By that I mean the actual full version. For instance, if you're using OleDB to talk to Oracle, the version of the ORAOLEDB.DLL file. For instance, is it 9.2.0.7?

Answer 5

I have abandon this approach since it takes too much time to find out why this happens. I am joining the tables in logic level.

Thanks for all of your help. :-)