Concatenating the IN Clause using Oracle with Composite Keys

Friday, September 17, 2010

Most .NET developers are familiar with how to concatenate fields in SQL Server 2008:

select firstName + ' ' + lastName as UserName from Reviewers

In your ASP.NET development, you may be required to work with various database types. I’m going to show how to use the CONCAT function in Oracle, specifically within the IN clause. In your SQL query WHERE clause, first you must use the CONCAT function to combine the desired fields, then in your SELECT clause again CONCAT the selected fields which must match from the first table. This is typically used with a composite key from the first table which must be found to match data in the second table.

And, because this isn’t an Oracle blog, I will also include how to open and close connections and run queries within .NET using Oracle’s Data Access Components. At the top of your class, be sure to import the Oracle.DataAccess.Client:

VB.NET

Imports Oracle.DataAccess.Client

Build your query:

Dim sql As String = String.Empty
 sql = "SELECT * FROM TBL_OPERATION " & _
 " WHERE CONCAT(op_symbol,CONCAT(' - ', op_code))" & _
" IN (SELECT  CONCAT(op_symbol,CONCAT(' - ', op_code))" & _
" FROM TBL_REVIEW_OPERATION " & _
" WHERE op_symbol = :op_symbol AND prod_number = :prod_number"

Dim cmd As New OracleCommand()
Using con As New OracleConnection(GetConnectionString)
con.Open()
cmd = New OracleCommand(sql, con)
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("op_symbol", OracleDbType.Varchar2,5).Value = _
	op_symbol
cmd.Parameters.Add("prod_number ", OracleDbType.Varchar2,3).Value = _
	prod_number
  
cmd.ExecuteNonQuery()
End Using 
' using blocks automatically call Dispose() and close the connection

In the above example, we separate each field with a hyphen, so the result of two columns might look like this:

"233-421"

Instead of this:

"233421"

It is important to have some form of divider so that you don’t pull unintended data. Such as if the first field were 23 and the second 3421, or 2334 and 21. By using dividers, you’re always sure to get 233 and 421 as the two concatenated field values.

Other common Oracle parameter types include:

cmd.Parameters.Add("RecordUpdatedUserId", OracleDbType.Decimal).Value = RecordUpdatedUserId

cmd.Parameters.Add("RecodUpdatedDate", OracleDbType.Date).Value = Now()

cmd.Parameters.Add("ActionDescription", OracleDbType.Clob, 3000).Value = ActionDescription

For further information on SQL Concatenation, visit this article.

May your dreams be in ASP.NET!

Nannette Thacker

1 Comment