SQL ORACLE - Datatable in where clause

Question

Currently I have a sql call returning a dataset from a MSSQL database and I want to take a column from that data and return ID's based off that column from the ORACLE database. I can do this one at a time but that requires multiple calls, I am wondering if this can be done with one call.

String sql=String.Format(@"Select DIST_NO
    FROM DISTRICT
    WHERE DIST_DESC = '{0}'", row.Table.Rows[0]["Op_Centre"].ToString());

Above is the string I am using to return one ID at a time. I know the {0} can be used to format your value into the string and maybe there is a way to do that with a datatable.

Also to use multiple values in the where clause it would be:

String sql=String.Format(@"Select DIST_NO
   FROM DISTRICT
   WHERE DIST_DESC in ('{0}')", row.Table.Rows[0] ["Op_Centre"].ToString());

Although I realize all of this can be done I am wondering if theres an easy way to add it all to the sql string in one call.

As I am writing this I am realizing I could break the string into sections then just add every row value to the SQL string within the "WHERE DIST_DESC IN (" clause...

I am still curious to see if there is another way though, and because someone else may come across this problem I will post a solution if I develop one.

Thanks in advance.

Answer 1

K I understand its pointless to ask a question and then answer it yourself but by asking the question I realized the answer.

String sql = "Select DIST_NO FROM DISTRICT WHERE DIST_DESC in ('" +ds.Tables[0].Rows[0]["Op_Centre"].ToString()+"'";

for (int i = 1; i < ds.Tables[0].Rows.Count;i++ )

{ sql +=",'"+ds.Tables[0].Rows[i]["Op_Centre"].ToString()+"'";

} sql += ")";

I am still curious to see if there is a better way though...

Answer 2

The most RDBMS-agnostic approach you could do is to create a temporary table. Then just query: select * from district where dist_desc in (select dist_desc from temp_table)

There's a solution here in Oracle http://forums.oracle.com/forums/thread.jspa?threadID=930372 , but I don't have Oracle in my box, so I can't try how it will work in .NET.

Have tried this in Postgresql http://fxjr.blogspot.com/2009/05/npgsql-tips-using-in-queries-with.html , have very seamless support for comparing value to list

If you will go the dynamic query approach, try this:

String sql=String.Format(@"Select DIST_NO
FROM DISTRICT
WHERE DIST_DESC IN ({0})", 
  string.Join( ",",
      ds.Tables[0].Select()
      .Select(r => "'" + (string)r["Op_Centre"] + "'").ToArray() ) // string.Join
  ); // string.Format