ADODB from SQLServer - IF function?

[Dianne Butterworth]

I'm trying to query an SQL Server database and I wondered if there was some
kind of if function that I could use.

Here's my code (stripped down a bit and cobbled together from various
sources):

Sub ADOImportFromDB()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim intColIndex As Integer
Dim TargetRange As Range
Dim strSQL As String

ActiveSheet.Cells.Clear
Set TargetRange = ActiveSheet.Cells(1, 1)

strSQL = "SELECT "
strSQL = strSQL & "jch.DESCRIPTION Description "
strSQL = strSQL & ", jct.COST_CODE CostCode "
strSQL = strSQL & ", jct.CLASS Class"
strSQL = strSQL & ", jct.AMOUNT Amount "
'********************************** 'HERE'S WHAT I WOULD LIKE TO DO
'strSQL = strSQL & ", iif(jct.CLASS='16', jct.AMOUNT, -jct.AMOUNT) as
AdjAmount "
strSQL = strSQL & "FROM "
strSQL = strSQL & "JC_HEADERMASTER jch"
strSQL = strSQL & ", JC_TRANSACTIONS jct "
strSQL = strSQL & "WHERE "
strSQL = strSQL & "jch.JOB = jct.JOB "

' open the database
Set cn = New ADODB.Connection

With cn
.Provider = "SQLOLEDB"
.ConnectionString = _
"DSN=myDSN;UID=myusername;PWD=mypassword;DATABASE=mydatabase;Network=DBMSSOCN;Address=servername,1433"
.Open
End With

Set rs = New ADODB.Recordset
With rs
.Open strSQL, cn, , , adCmdText

For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value =
rs.Fields(intColIndex).Name
Next
TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

The query works fine without the line in question.

Googling suggests things like IF, IIF, DECODE, CASE, but I can't seem to put
these into practice.

I know I could pull the data in and then do my if within Excel, but it seems
to me to be better practice to do this in the query, if possible.

Any suggestions? Or should I do this in Excel instead? Jamie?

Thanks

 

[Robin Hammond]

Dianne,

Here's an example lifted from one of my SQL views of the use of the case
statement

SELECT 'ItemValue' =
CASE
WHEN UseFxConversion = 1 THEN
ItemValue / PSFactor
ELSE
ItemValue
END
FROM
etc....

It looks like yours should be
'AdjAmount' =
CASE
WHEN jct.CLASS=16 THEN
jct.Amount
ELSE
-jct.Amount
END

I have removed the quotes around the 16 here assuming that you have numeric
data in the field rather than nvarchar. I am not sure that the negative of a
string value will return the correct result if it is string. If it is then
you might need to do a string concatenation, not a negative multiply.

Robin Hammond
www.enhanceddatasystems.com

 

[Guest]

Diane,
you could try:

strSQL = "SELECT jch.DESCRIPTION Description, jct.COST_CODE CostCode, " & _
"jct.CLASS Class, jct.AMOUNT Amount, CASE" & _
"when jct.CLASS='16' then jct.AMOUNT else jct.AMOUNT * -1 " & _
"End As AdjAmount" & _
"FROM JC_HEADERMASTER jch, JC_TRANSACTIONS jct " & _
"WHERE jch.JOB = jct.JOB "
I used (jct.AMOUNT * -1) because I don't know any better.
I also put my sql statements in one string to make it easier to follow.

Hope this helps,
Raul

 

[onedaywhen]

Googling suggests things like IF, IIF, DECODE, CASE, but I can't seem to put
these into practice.

Just to clarify, the ANSI standard SQL syntax is CASE, which is
supported in SQL Server, so you should use it:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp

DECODE is proprietary Oracle syntax. IIF is proprietary Jet (MS Access)
syntax. For portability (in the loosest sense) it is best to use the
standard syntax but this is not always possible e.g. Jet does not
support CASE.

Jamie.

--

 

[Dianne Butterworth]

Thanks Robin,

I was on the right track, but I was missing the END keyword. It's working
fine now.

 

[Dianne Butterworth]

Raul,

Both your and Robin's example included END after CASE, which I hadn't used
when I tried CASE before. I changed my query and presto! Thanks for your
help.

 

[Dianne Butterworth]

Jamie,

I had tried CASE, but hadn't realised it required an END.

Thanks for the pointer to the MSDN article. I have bookmarked this for
future TSQL queries -- very useful!