+ Reply to Thread
Results 1 to 7 of 7

ADODB from SQLServer - IF function?

  1. #1
    Dianne Butterworth
    Guest

    ADODB from SQLServer - IF function?

    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
    --
    Dianne



  2. #2
    Robin Hammond
    Guest

    Re: ADODB from SQLServer - IF function?

    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

    "Dianne Butterworth" <dbutterworth@invalid.com> wrote in message
    news:OdK4cjC%23EHA.1292@TK2MSFTNGP10.phx.gbl...
    > 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
    > --
    > Dianne
    >




  3. #3
    Raul
    Guest

    RE: ADODB from SQLServer - IF function?

    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

    "Dianne Butterworth" wrote:

    > 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
    > --
    > Dianne
    >
    >
    >


  4. #4
    onedaywhen
    Guest

    Re: ADODB from SQLServer - IF function?

    Dianne Butterworth wrote:
    > 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/de...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.

    --


  5. #5
    Dianne Butterworth
    Guest

    Re: ADODB from SQLServer - IF function?

    Thanks Robin,

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

    --
    Dianne

    Robin Hammond wrote:
    > 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
    >
    > "Dianne Butterworth" <dbutterworth@invalid.com> wrote in message
    > news:OdK4cjC%23EHA.1292@TK2MSFTNGP10.phx.gbl...
    >> 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
    >> --
    >> Dianne




  6. #6
    Dianne Butterworth
    Guest

    Re: ADODB from SQLServer - IF function?

    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


    Raul wrote:
    > 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
    >
    > "Dianne Butterworth" wrote:
    >
    >> 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
    >> --
    >> Dianne




  7. #7
    Dianne Butterworth
    Guest

    Re: ADODB from SQLServer - IF function?

    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!

    --
    Dianne

    onedaywhen wrote:
    > Dianne Butterworth wrote:
    >> 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/de...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.




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1