Loading ...
Sorry, an error occurred while loading the content.

Can anyone tell me what's wrong with this CASE statement?

Expand Messages
  • reniperez
    Hi, I m trying to perform a CASE statement on a single table on SQL Server 2005 and keep getting Incorrect syntax near = as an error message. The
    Message 1 of 5 , Jun 18, 2009
    • 0 Attachment
      Hi,

      I'm trying to perform a CASE statement on a single table on SQL Server 2005 and keep getting "Incorrect syntax near '=' " as an error message. The statement is below:

      SELECT Account_Name, Wire_Transfer_Flag, CASE Account_Name
      WHEN Wire_Transfer_Flag = ` - 1 ' THEN ` yes ' ELSE ` no ' AS Org END
      FROM dbo.Payments

      What I'm trying to do is bring in two fields (Account_Name, Wire_Transfer_Flag) from the Payments Table and create another one (Account_Name) on the fly that will show 'yes' or 'no' based on one of two values that exist in the Wire_Transfer_Flag field which are either -1 or 0.

      Am I missing something obvious or just not writing the statement correctly?

      Reinaldo
    • Eric Kestler
      Try: CASE WHEN Wire_Transfer_Flag= -1 THEN yes ELSE no END ... Remove the Account_Name right after CASE Also, is Wire_Transfer_Flag a CHAR field? Or a
      Message 2 of 5 , Jun 19, 2009
      • 0 Attachment
        Try:

        CASE WHEN Wire_Transfer_Flag='-1' THEN 'yes' ELSE 'no' END ...

        Remove the "Account_Name" right after "CASE"

        Also, is Wire_Transfer_Flag a CHAR field? Or a Boolean? Or an Integer? You
        are comparing to a CHARACTER -1, not a boolean or integer. Is this what you
        want?



        On Thu, Jun 18, 2009 at 5:11 PM, reniperez <reniperez@...> wrote:

        >
        >
        > Hi,
        >
        > I'm trying to perform a CASE statement on a single table on SQL Server 2005
        > and keep getting "Incorrect syntax near '=' " as an error message. The
        > statement is below:
        >
        > SELECT Account_Name, Wire_Transfer_Flag, CASE Account_Name
        > WHEN Wire_Transfer_Flag = ` - 1 ' THEN ` yes ' ELSE ` no ' AS Org END
        > FROM dbo.Payments
        >
        > What I'm trying to do is bring in two fields (Account_Name,
        > Wire_Transfer_Flag) from the Payments Table and create another one
        > (Account_Name) on the fly that will show 'yes' or 'no' based on one of two
        > values that exist in the Wire_Transfer_Flag field which are either -1 or 0.
        >
        > Am I missing something obvious or just not writing the statement correctly?
        >
        > Reinaldo
        >
        >
        >



        --
        Plan A is always more effective when the device you are working on
        understands that Plan B involves either a large hammer or screwdriver....


        [Non-text portions of this message have been removed]
      • Paul Livengood
        Reinaldo I see two syntex errors; both in the case statement. The first is that you are naming the column in the wrong spot. It should be; CASE WHEN
        Message 3 of 5 , Jun 19, 2009
        • 0 Attachment
          Reinaldo
          I see two syntex errors; both in the case statement.

          The first is that you are naming the column in the wrong spot. It should be; CASE WHEN {condition} THEN {True Result} ELSE {False Result} END AS {Column Name}. You have the {Column Name} before the end statement.

          The other is that you are referencing the accountname column after the CASE statement. This is not needed.

          Here is an updated query

          SELECT Account_Name,
          Wire_Transfer_Flag,
          CASE WHEN Wire_Transfer_Flag = '-1' THEN 'yes'
          ELSE 'no'
          END AS Org
          FROM dbo.Payments

          HTH
          Paul


          --- In SQLQueriesNoCode@yahoogroups.com, "reniperez" <reniperez@...> wrote:
          >
          > Hi,
          >
          > I'm trying to perform a CASE statement on a single table on SQL Server 2005 and keep getting "Incorrect syntax near '=' " as an error message. The statement is below:
          >
          > SELECT Account_Name, Wire_Transfer_Flag, CASE Account_Name
          > WHEN Wire_Transfer_Flag = ` - 1 ' THEN ` yes ' ELSE ` no ' AS Org END
          > FROM dbo.Payments
          >
          > What I'm trying to do is bring in two fields (Account_Name, Wire_Transfer_Flag) from the Payments Table and create another one (Account_Name) on the fly that will show 'yes' or 'no' based on one of two values that exist in the Wire_Transfer_Flag field which are either -1 or 0.
          >
          > Am I missing something obvious or just not writing the statement correctly?
          >
          > Reinaldo
          >
        • reniperez
          Paul, Thank you so much for your correction of my query. Your updated query did the trick. Greatly appreciated. Reinaldo
          Message 4 of 5 , Jun 19, 2009
          • 0 Attachment
            Paul,

            Thank you so much for your correction of my query. Your updated query did the trick. Greatly appreciated.

            Reinaldo

            --- In SQLQueriesNoCode@yahoogroups.com, "Paul Livengood" <p_livengood@...> wrote:
            >
            > Reinaldo
            > I see two syntex errors; both in the case statement.
            >
            > The first is that you are naming the column in the wrong spot. It should be; CASE WHEN {condition} THEN {True Result} ELSE {False Result} END AS {Column Name}. You have the {Column Name} before the end statement.
            >
            > The other is that you are referencing the accountname column after the CASE statement. This is not needed.
            >
            > Here is an updated query
            >
            > SELECT Account_Name,
            > Wire_Transfer_Flag,
            > CASE WHEN Wire_Transfer_Flag = '-1' THEN 'yes'
            > ELSE 'no'
            > END AS Org
            > FROM dbo.Payments
            >
            > HTH
            > Paul
            >
            >
            > --- In SQLQueriesNoCode@yahoogroups.com, "reniperez" <reniperez@> wrote:
            > >
            > > Hi,
            > >
            > > I'm trying to perform a CASE statement on a single table on SQL Server 2005 and keep getting "Incorrect syntax near '=' " as an error message. The statement is below:
            > >
            > > SELECT Account_Name, Wire_Transfer_Flag, CASE Account_Name
            > > WHEN Wire_Transfer_Flag = ` - 1 ' THEN ` yes ' ELSE ` no ' AS Org END
            > > FROM dbo.Payments
            > >
            > > What I'm trying to do is bring in two fields (Account_Name, Wire_Transfer_Flag) from the Payments Table and create another one (Account_Name) on the fly that will show 'yes' or 'no' based on one of two values that exist in the Wire_Transfer_Flag field which are either -1 or 0.
            > >
            > > Am I missing something obvious or just not writing the statement correctly?
            > >
            > > Reinaldo
            > >
            >
          • reniperez
            Thanks for the reply, Eric. Paul responded with a similar answer that did the trick. FYI, the Wire Transfer Flag is a data type of small int in our database.
            Message 5 of 5 , Jun 19, 2009
            • 0 Attachment
              Thanks for the reply, Eric. Paul responded with a similar answer that did the trick. FYI, the Wire Transfer Flag is a data type of small int in our database.

              Reinaldo

              --- In SQLQueriesNoCode@yahoogroups.com, Eric Kestler <ekestler@...> wrote:
              >
              > Try:
              >
              > CASE WHEN Wire_Transfer_Flag='-1' THEN 'yes' ELSE 'no' END ...
              >
              > Remove the "Account_Name" right after "CASE"
              >
              > Also, is Wire_Transfer_Flag a CHAR field? Or a Boolean? Or an Integer? You
              > are comparing to a CHARACTER -1, not a boolean or integer. Is this what you
              > want?
              >
              >
              >
              > On Thu, Jun 18, 2009 at 5:11 PM, reniperez <reniperez@...> wrote:
              >
              > >
              > >
              > > Hi,
              > >
              > > I'm trying to perform a CASE statement on a single table on SQL Server 2005
              > > and keep getting "Incorrect syntax near '=' " as an error message. The
              > > statement is below:
              > >
              > > SELECT Account_Name, Wire_Transfer_Flag, CASE Account_Name
              > > WHEN Wire_Transfer_Flag = ` - 1 ' THEN ` yes ' ELSE ` no ' AS Org END
              > > FROM dbo.Payments
              > >
              > > What I'm trying to do is bring in two fields (Account_Name,
              > > Wire_Transfer_Flag) from the Payments Table and create another one
              > > (Account_Name) on the fly that will show 'yes' or 'no' based on one of two
              > > values that exist in the Wire_Transfer_Flag field which are either -1 or 0.
              > >
              > > Am I missing something obvious or just not writing the statement correctly?
              > >
              > > Reinaldo
              > >
              > >
              > >
              >
              >
              >
              > --
              > Plan A is always more effective when the device you are working on
              > understands that Plan B involves either a large hammer or screwdriver....
              >
              >
              > [Non-text portions of this message have been removed]
              >
            Your message has been successfully submitted and would be delivered to recipients shortly.