OLEDB CASE WHEN in SELECT Query [closed]

Question

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 1

You'll have to use IIF instead of CASE when querying Excel, eg

SELECT prodid, IIF(prodid = 1, 'fine', IIF(prodid = 2, 'good', '')) FROM MyExcel

As you can see, it can get messy quickly. It's not too bad if you've only got a two-way evaluation, but in your code it's not clear whether you only have 2 possible values for prodid or more than that. My example assumes there are other values, hence the nested IIF.

Answer 2

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.