Clever Geek Handbook

.NET and Oracle: joining a table into an empty set of another table

I am trying to concatenate table A with some data into an empty set of another table B. The main purpose is to get all columns of table B. I don't need any data from table B.

I created the following SQL:

SELECT uar.*, s.screen_id, s.screen_name
FROM crs_screens 
 LEFT JOIN crs\_user\_access\_right uar
 ON s.rid IS NOT NULL AND uar.rid IS NULL

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

It is good if there is a workaround to achieve the same effect. Many thanks.

Error message:

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

Config:

.NET Framework: 1.1

Oracle 9i

0


source to share


6 answers


I am abandoning this approach as it takes too long to figure out why this is happening. I am joining tables at a logical level.



Thank you for your help.: -)

0


source


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



0


source


Try to be clear about all your columns and look at all data types - specifically the second uar column.

0


source


I'm not too familiar with Oracle (so I can't tell if this is an Oracle problem on purpose), but perhaps to rephrase your SQL, how 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 don't need to compare data on s.rid as on the left side the crs_screen will always be included in the left outer join.

0


source


What version of the client driver are you using? By this I mean the actual full version. For example, if you are using OleDB to communicate with Oracle, the version of the ORAOLEDB.DLL file. For example, is it 9.2.0.7?

0


source


I know this is an old thread, but if anyone else had the problem "OCI-22060: argument [2] is invalid or uninitialized number", I found a solution that works for me.

for the original cursor:

select l.t_id, r.t_name, r.t_desc
left_table l left join right_table r
on r.t_id = l.t_id;

... simple refactoring ...

select -99999999 t_id, 'XXXXXXXXX' t_name, 'YYYYYYYYYYYY' t_desc from dual
union all
select l.t_id, r.t_name, r.t_desc
left_table l left join right_table r
on r.t_id = l.t_id;

... works. I just needed to filter out the dummy string in my client.

0


source






More articles:


All Articles