OLEDB CASE WHEN in SELECT Query

Question

how can use case when statement with oledb to excel file using vb.net 2.0 like select prodid, case prodid when 1 then 'fine' when 2 then 'good' end

Answer 1

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.

Answer 2

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.