OLEDB CASE WHEN in SELECT Query

sql oledb ms-jet-ace
VENKAT picture VENKAT · Nov 16, 2009 · Viewed 9.1k times · Source

How can use case when statement with oledb to excel file ?
Like select prodid, case prodid when 1 then 'fine' when 2 then 'good' end

Answer

mjv picture mjv · Nov 16, 2009

OLEDB is only a "relay" of sort and merely repeats the query to the underlying source.

I don't believe Excel supports the CASE WHEN construct, and thence you cannot use it, even through OLEDB.

You can instead use an Excel-specific idiom, the IIF() function, as described in CodeByMoonlight's answer. As noted the need to nest IIF() calls makes is a rather messy proposal for situations that require more than 3 or 4 cases.

Related questions

How to find which OLE provider is available for SQL Server?

I try to access an Excel file in SSMS. After searching the internet, I could not get it working. Here is what I did: My environment: Windows 7(64bit) SP 1, Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Office 2010 Pro Plus with Access …

Read More
Return value from OleDbCommand

sqlQuery = "SELECT [ID] from [users] WHERE CallerName=@CallerName"; OleDbConnection conn = new OleDbConnection(connectionString); conn.Open(); cmd = new OleDbCommand(sqlQuery, conn); cmd.CommandText = sqlQuery; cmd.Parameters.Add("@CallerName", OleDbType.VarChar).Value = labelProblemDate.Text.Trim(); cmd.Parameters["@CallerName"].Value = name; cmd.ExecuteNonQuery(); …

Read More
Sql error "Arithmetic operation resulted in an overflow."

I am running a service that does some computation and communicates with an ms sql server every minute or so (24/7, uptime is very important) and writes to error log if anything funny happens (like a timeout or lost connection). This …

Read More