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

RE: [SQLQueriesNoCode] Error - char value to money

Expand Messages
  • John Warner
    I m not clear here, is the data already imported? If so what makes you think mAmount is a Money type? If not, what is this query running against? John Warner
    Message 1 of 8 , Nov 20, 2009
    • 0 Attachment
      I'm not clear here, is the data already imported? If so what makes you
      think mAmount is a Money type? If not, what is this query running against?

      John Warner


      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ravi
      > Sent: Friday, November 20, 2009 12:31 PM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: [SQLQueriesNoCode] Error - char value to money
      >
      > I have a row that contains MONEY values.
      >
      > Somehow during, the import, there is a letter in the money field "N"
      > I tried to update all "N" values to ZERO
      >
      > Update MasterTable
      > Set [mAmount] = 0
      > Where [mAmount] = 'N'
      >
      > "Cannot convert a char value to money. The char value has incorrect
      syntax."
      >
      >
      > tia
      >
      >
      >
      > ------------------------------------
      >
      > Yahoo! Groups Links
      >
      >
      >
    • Ravi
      The data is already in the database. It was imported months ago. Yes, the scheme shows mAmount Data Types = money
      Message 2 of 8 , Nov 21, 2009
      • 0 Attachment
        The data is already in the database. It was imported months ago.
        Yes, the scheme shows mAmount Data Types = money



        --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...> wrote:
        >
        > I'm not clear here, is the data already imported? If so what makes you
        > think mAmount is a Money type? If not, what is this query running against?
        >
        > John Warner
        >
        >
        > > -----Original Message-----
        > > From: SQLQueriesNoCode@yahoogroups.com
        > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ravi
        > > Sent: Friday, November 20, 2009 12:31 PM
        > > To: SQLQueriesNoCode@yahoogroups.com
        > > Subject: [SQLQueriesNoCode] Error - char value to money
        > >
        > > I have a row that contains MONEY values.
        > >
        > > Somehow during, the import, there is a letter in the money field "N"
        > > I tried to update all "N" values to ZERO
        > >
        > > Update MasterTable
        > > Set [mAmount] = 0
        > > Where [mAmount] = 'N'
        > >
        > > "Cannot convert a char value to money. The char value has incorrect
        > syntax."
        > >
        > >
        > > tia
        > >
        > >
        > >
        > > ------------------------------------
        > >
        > > Yahoo! Groups Links
        > >
        > >
        > >
        >
      • John Warner
        Then N cannot be in the field. John Warner ... against?
        Message 3 of 8 , Nov 21, 2009
        • 0 Attachment
          Then 'N' cannot be in the field.

          John Warner


          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ravi
          > Sent: Saturday, November 21, 2009 11:29 AM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: [SQLQueriesNoCode] Re: Error - char value to money
          >
          > The data is already in the database. It was imported months ago.
          > Yes, the scheme shows mAmount Data Types = money
          >
          >
          >
          > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...>
          > wrote:
          > >
          > > I'm not clear here, is the data already imported? If so what makes you
          > > think mAmount is a Money type? If not, what is this query running
          against?
          > >
          > > John Warner
          > >
          > >
          > > > -----Original Message-----
          > > > From: SQLQueriesNoCode@yahoogroups.com
          > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ravi
          > > > Sent: Friday, November 20, 2009 12:31 PM
          > > > To: SQLQueriesNoCode@yahoogroups.com
          > > > Subject: [SQLQueriesNoCode] Error - char value to money
          > > >
          > > > I have a row that contains MONEY values.
          > > >
          > > > Somehow during, the import, there is a letter in the money field "N"
          > > > I tried to update all "N" values to ZERO
          > > >
          > > > Update MasterTable
          > > > Set [mAmount] = 0
          > > > Where [mAmount] = 'N'
          > > >
          > > > "Cannot convert a char value to money. The char value has incorrect
          > > syntax."
          > > >
          > > >
          > > > tia
          > > >
          > > >
          > > >
          > > > ------------------------------------
          > > >
          > > > Yahoo! Groups Links
          > > >
          > > >
          > > >
          > >
          >
          >
          >
          >
          > ------------------------------------
          >
          > Yahoo! Groups Links
          >
          >
          >
        • Ravi
          It is ... I tried to update all N values to ZERO Update MasterTable Set [mAmount] = 0 Where [mAmount] = N Cannot convert a char value to money. The char
          Message 4 of 8 , Nov 22, 2009
          • 0 Attachment
            It is ...

            I tried to update all "N" values to ZERO
            Update MasterTable
            Set [mAmount] = 0
            Where [mAmount] = 'N'

            "Cannot convert a char value to money. The char value has incorrect syntax."



            --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...> wrote:
            >
            > Then 'N' cannot be in the field.
            >
            > John Warner
            >
            >
            > > -----Original Message-----
            > > From: SQLQueriesNoCode@yahoogroups.com
            > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ravi
            > > Sent: Saturday, November 21, 2009 11:29 AM
            > > To: SQLQueriesNoCode@yahoogroups.com
            > > Subject: [SQLQueriesNoCode] Re: Error - char value to money
            > >
            > > The data is already in the database. It was imported months ago.
            > > Yes, the scheme shows mAmount Data Types = money
            > >
            > >
            > >
            > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@>
            > > wrote:
            > > >
            > > > I'm not clear here, is the data already imported? If so what makes you
            > > > think mAmount is a Money type? If not, what is this query running
            > against?
            > > >
            > > > John Warner
            > > >
            > > >
            > > > > -----Original Message-----
            > > > > From: SQLQueriesNoCode@yahoogroups.com
            > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ravi
            > > > > Sent: Friday, November 20, 2009 12:31 PM
            > > > > To: SQLQueriesNoCode@yahoogroups.com
            > > > > Subject: [SQLQueriesNoCode] Error - char value to money
            > > > >
            > > > > I have a row that contains MONEY values.
            > > > >
            > > > > Somehow during, the import, there is a letter in the money field "N"
            > > > > I tried to update all "N" values to ZERO
            > > > >
            > > > > Update MasterTable
            > > > > Set [mAmount] = 0
            > > > > Where [mAmount] = 'N'
            > > > >
            > > > > "Cannot convert a char value to money. The char value has incorrect
            > > > syntax."
            > > > >
            > > > >
            > > > > tia
            > > > >
            > > > >
            > > > >
            > > > > ------------------------------------
            > > > >
            > > > > Yahoo! Groups Links
            > > > >
            > > > >
            > > > >
            > > >
            > >
            > >
            > >
            > >
            > > ------------------------------------
            > >
            > > Yahoo! Groups Links
            > >
            > >
            > >
            >
          • John Warner
            Can this run? SELECT * FROM MasterTable WHERE mAmount = N Also is this SQL Server or what dbms, how did the N char ever get into the table, what I mean is
            Message 5 of 8 , Nov 22, 2009
            • 0 Attachment
              Can this run?

              SELECT * FROM MasterTable WHERE mAmount = 'N'


              Also is this SQL Server or what dbms, how did the 'N' char ever get into
              the table, what I mean is in SQL Server if the field type is defined as
              Money then any import would have failed on rows when it encountered an
              attempt to place the char 'N' into the field.

              John Warner


              > -----Original Message-----
              > From: SQLQueriesNoCode@yahoogroups.com
              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ravi
              > Sent: Sunday, November 22, 2009 10:00 AM
              > To: SQLQueriesNoCode@yahoogroups.com
              > Subject: [SQLQueriesNoCode] Re: Error - char value to money
              >
              > It is ...
              >
              > I tried to update all "N" values to ZERO
              > Update MasterTable
              > Set [mAmount] = 0
              > Where [mAmount] = 'N'
              >
              > "Cannot convert a char value to money. The char value has incorrect
              syntax."
              >
              >
              >
              > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...>
              > wrote:
              > >
              > > Then 'N' cannot be in the field.
              > >
              > > John Warner
              > >
              > >
              > > > -----Original Message-----
              > > > From: SQLQueriesNoCode@yahoogroups.com
              > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ravi
              > > > Sent: Saturday, November 21, 2009 11:29 AM
              > > > To: SQLQueriesNoCode@yahoogroups.com
              > > > Subject: [SQLQueriesNoCode] Re: Error - char value to money
              > > >
              > > > The data is already in the database. It was imported months ago.
              > > > Yes, the scheme shows mAmount Data Types = money
              > > >
              > > >
              > > >
              > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@>
              > > > wrote:
              > > > >
              > > > > I'm not clear here, is the data already imported? If so what makes
              you
              > > > > think mAmount is a Money type? If not, what is this query running
              > > against?
              > > > >
              > > > > John Warner
              > > > >
              > > > >
              > > > > > -----Original Message-----
              > > > > > From: SQLQueriesNoCode@yahoogroups.com
              > > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ravi
              > > > > > Sent: Friday, November 20, 2009 12:31 PM
              > > > > > To: SQLQueriesNoCode@yahoogroups.com
              > > > > > Subject: [SQLQueriesNoCode] Error - char value to money
              > > > > >
              > > > > > I have a row that contains MONEY values.
              > > > > >
              > > > > > Somehow during, the import, there is a letter in the money field
              "N"
              > > > > > I tried to update all "N" values to ZERO
              > > > > >
              > > > > > Update MasterTable
              > > > > > Set [mAmount] = 0
              > > > > > Where [mAmount] = 'N'
              > > > > >
              > > > > > "Cannot convert a char value to money. The char value has
              incorrect
              > > > > syntax."
              > > > > >
              > > > > >
              > > > > > tia
              > > > > >
              > > > > >
              > > > > >
              > > > > > ------------------------------------
              > > > > >
              > > > > > Yahoo! Groups Links
              > > > > >
              > > > > >
              > > > > >
              > > > >
              > > >
              > > >
              > > >
              > > >
              > > > ------------------------------------
              > > >
              > > > Yahoo! Groups Links
              > > >
              > > >
              > > >
              > >
              >
              >
              >
              >
              > ------------------------------------
              >
              > Yahoo! Groups Links
              >
              >
              >
            • Ravi
              ... Update MasterTable Set [mAmount] = 0 where ISNUMERIC([mAmount])=0
              Message 6 of 8 , Nov 22, 2009
              • 0 Attachment
                ----- SOLVED -----
                Update MasterTable
                Set [mAmount] = 0
                where ISNUMERIC([mAmount])=0
                ------------------------------



                --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...> wrote:
                >
                > Can this run?
                >
                > SELECT * FROM MasterTable WHERE mAmount = 'N'
                >
                >
                > Also is this SQL Server or what dbms, how did the 'N' char ever get into
                > the table, what I mean is in SQL Server if the field type is defined as
                > Money then any import would have failed on rows when it encountered an
                > attempt to place the char 'N' into the field.
                >
                > John Warner
                >
                >
                > > -----Original Message-----
                > > From: SQLQueriesNoCode@yahoogroups.com
                > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ravi
                > > Sent: Sunday, November 22, 2009 10:00 AM
                > > To: SQLQueriesNoCode@yahoogroups.com
                > > Subject: [SQLQueriesNoCode] Re: Error - char value to money
                > >
                > > It is ...
                > >
                > > I tried to update all "N" values to ZERO
                > > Update MasterTable
                > > Set [mAmount] = 0
                > > Where [mAmount] = 'N'
                > >
                > > "Cannot convert a char value to money. The char value has incorrect
                > syntax."
                > >
                > >
                > >
                > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@>
                > > wrote:
                > > >
                > > > Then 'N' cannot be in the field.
                > > >
                > > > John Warner
                > > >
                > > >
                > > > > -----Original Message-----
                > > > > From: SQLQueriesNoCode@yahoogroups.com
                > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ravi
                > > > > Sent: Saturday, November 21, 2009 11:29 AM
                > > > > To: SQLQueriesNoCode@yahoogroups.com
                > > > > Subject: [SQLQueriesNoCode] Re: Error - char value to money
                > > > >
                > > > > The data is already in the database. It was imported months ago.
                > > > > Yes, the scheme shows mAmount Data Types = money
                > > > >
                > > > >
                > > > >
                > > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@>
                > > > > wrote:
                > > > > >
                > > > > > I'm not clear here, is the data already imported? If so what makes
                > you
                > > > > > think mAmount is a Money type? If not, what is this query running
                > > > against?
                > > > > >
                > > > > > John Warner
                > > > > >
                > > > > >
                > > > > > > -----Original Message-----
                > > > > > > From: SQLQueriesNoCode@yahoogroups.com
                > > > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ravi
                > > > > > > Sent: Friday, November 20, 2009 12:31 PM
                > > > > > > To: SQLQueriesNoCode@yahoogroups.com
                > > > > > > Subject: [SQLQueriesNoCode] Error - char value to money
                > > > > > >
                > > > > > > I have a row that contains MONEY values.
                > > > > > >
                > > > > > > Somehow during, the import, there is a letter in the money field
                > "N"
                > > > > > > I tried to update all "N" values to ZERO
                > > > > > >
                > > > > > > Update MasterTable
                > > > > > > Set [mAmount] = 0
                > > > > > > Where [mAmount] = 'N'
                > > > > > >
                > > > > > > "Cannot convert a char value to money. The char value has
                > incorrect
                > > > > > syntax."
                > > > > > >
                > > > > > >
                > > > > > > tia
                > > > > > >
                > > > > > >
                > > > > > >
                > > > > > > ------------------------------------
                > > > > > >
                > > > > > > Yahoo! Groups Links
                > > > > > >
                > > > > > >
                > > > > > >
                > > > > >
                > > > >
                > > > >
                > > > >
                > > > >
                > > > > ------------------------------------
                > > > >
                > > > > Yahoo! Groups Links
                > > > >
                > > > >
                > > > >
                > > >
                > >
                > >
                > >
                > >
                > > ------------------------------------
                > >
                > > Yahoo! Groups Links
                > >
                > >
                > >
                >
              • caj_y
                Hi tia, From the Error Message it seems that the mAmount is Character type. To test this, instead of Updateing it with zero, try to replace it with other
                Message 7 of 8 , Nov 28, 2009
                • 0 Attachment
                  Hi tia,

                  From the Error Message it seems that the mAmount is Character type.

                  To test this, instead of Updateing it with zero, try to replace it with other character value like 'Z' or 'P'...

                  Update MasterTable
                  Set [mAmount] = 'Z'
                  Where [mAmount] = 'N'

                  If it Updates, it is not Money type but Character type....

                  Then it will be very simple to solve...

                  Regards
                  Chetan

                  =====================================

                  --- In SQLQueriesNoCode@yahoogroups.com, "Ravi" <ravir_dhali@...> wrote:
                  >
                  > I have a row that contains MONEY values.
                  >
                  > Somehow during, the import, there is a letter in the money field "N"
                  > I tried to update all "N" values to ZERO
                  >
                  > Update MasterTable
                  > Set [mAmount] = 0
                  > Where [mAmount] = 'N'
                  >
                  > "Cannot convert a char value to money. The char value has incorrect syntax."
                  >
                  >
                  > tia
                  >
                Your message has been successfully submitted and would be delivered to recipients shortly.