BubbaSparxx BubbaSparxx - 4 months ago 10
SQL Question

SQL SERVER 2008 select statement with case

I have a Select statement in my VB6 application....

Here's what it looks like.....

Dim str As string

str = "SELECT CompID, Department from tblCompanies " & _
"Where CompID in (123, 234, 345, 456) " & _
"Order by CompID "


So what I'm tryign to do here is add a CASE WHEN statement to WHERE clause - basically I'm looking to add a string to each Department name, depending on the COMPID. So I need to specify that these are the COMPID's that I want to select, then I want to do soemthing like

Case when CompID = 123 Then ----ADD "GC" to that Department Name


I guess I need to do this before I open my recordset with

rs.open str, g_CN, adOpenStatic


Because once it's open it seems to be giving me errors when I try to edit it.

All in all, if my recordset looks like this...

Accounting
Finance
IT
R&D


I'm trying to make it look like

"GC" - Accounting
"GC" - Finance
"BP" - IT
"DC" - R&D

Answer

change it to,

Dim str As string

str = "SELECT CompID, CASE WHEN CompID IN (123,234) THEN 'GC' " & _
                " WHEN CompID = 345 THEN 'IT' " & _
                " WHEN CompID = 456 THEN 'DC' " & _
                " ELSE '' END + ' - ' + Department AS Department from tblCompanies " & _ 
      "Where CompID in (123, 234, 345, 456) " & _
      "Order by CompID "
Comments