Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    10

    Unanswered: select sum statement

    I have a table with the following values:-

    order_no val spare
    0001 100
    0002 100
    0003 100 /1.4700000
    0004 150 /1.5800000
    0005 100 /1.4700000
    0006 100
    0007 100
    0008 100
    etc.

    I need to do a select statement that gives me the local currency value of the above therefore value/spare will do except when there is no value in the currency field it obviously returns an error as you cannot divide by zero - is there a way round this so that the statement only divides by the value in the currency column when it is greater than 0? I need the output to show as follows:

    order total
    0001 100
    0002 100
    0003 68.02
    0004 94.93
    0005 68.02
    0006 100
    0007 100
    0008 100
    etc.
    when I generate create table statement, for the table this data is in, I get the following (truncated)
    CREATE TABLE scheme.orders(
    order_no char(10) NOT NULL,
    val float,
    spare char(34) NOT NULL
    )
    GO
    I have tried select str(val/isnull((substring(spare,16,10)),1.0),10,2) as total from orders this does not work because spare is not null because of the table definition (i think...)
    Any help is very much appreciated :>

  2. #2
    Join Date
    Apr 2003
    Location
    Edison, NJ / Oakland, NJ (Work)
    Posts
    32

    Maybe this...

    I don't know if you have constraints that require your fields to be the types that they are, but I have something that works for me, with a few changes to the field declarations. This was tested in MS SQL, let me know if it doesn't work. I can also test this in mySQL.

    You have to make the rate field some sort of numeric type (float is best) and you have to do the math in the query. You can leave val and orderID the way you have them, although I would expect an ID field to be bigint or something of that sort, and if this is the top_level file for orders, it would also be auto_increment.

    Code:
    SELECT     orderID, ROUND(val / x_rate, 2) AS Total
    FROM         orders
    -----------------------------------
    Smith Hayward
    -----------------------------------

  3. #3
    Join Date
    Feb 2004
    Posts
    10

    sql sum statement

    the fields data types cannot be changed - they are from our accounts database.

  4. #4
    Join Date
    Apr 2003
    Location
    Edison, NJ / Oakland, NJ (Work)
    Posts
    32
    scheme is the name of the DB? What RDBMS are you using?

    Also, how is spare defined as a NOT NULL field when you have NULL values in it?

    I'm also assuming that the text string is comprised of the mathematical operator and the value with which to operate on VAL.

    Am I clear?
    Last edited by smithhayward; 02-19-04 at 12:17.
    -----------------------------------
    Smith Hayward
    -----------------------------------

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select val / (CASE spare WHEN 0 THEN 1 ELSE spare END)
    from table;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Feb 2004
    Posts
    10
    Originally posted by smithhayward
    scheme is the name of the DB? What RDBMS are you using?

    Also, how is spare defined as a NOT NULL field when you have NULL values in it?

    I'm also assuming that the text string is comprised of the mathematical operator and the value with which to operate on VAL.

    Am I clear?
    using MS Sql2000 - scheme is the owner of the table. NOT NULL is how the field is defined as out of the box for the accounts package that we have installed.

    The text string has the following format /1.47000000 which I use substring(spare,16,10) to get 1.47000000 then i try the calculation but unforunately it fails because not all fields have a value.

  7. #7
    Join Date
    Feb 2004
    Posts
    10
    Originally posted by r123456
    select val / (CASE spare WHEN 0 THEN 1 ELSE spare END)
    from table;
    this nearly works! however having slightly amended the select statement to read as follows:-

    select val / (CASE (substring(spare,16,10)) WHEN 0 THEN 1 ELSE (substring(spare,16,6)) END)
    from scheme.orders

    I get some rows return data then the following error:-

    Error: Syntax error converting the varchar value '1.59540000' to a column of data type int. (State:22005, Native Code: F5)
    34 Row(s) affected
    Syntax error converting the varchar value '1.59540000' to a column of data type int.

    any ideas?

  8. #8
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    To return the results with greater precision, replace REAL with FLOAT.

    select val /
    ISNULL((CASE CAST(spare As REAL) WHEN 0 THEN 1 ELSE CAST(spare As REAL) END),1)
    from table
    Last edited by r123456; 02-20-04 at 08:33.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  9. #9
    Join Date
    Feb 2004
    Posts
    10

    sql sum statement

    Originally posted by r123456
    To return the results with greater precision, replace REAL with FLOAT.

    select val /
    ISNULL((CASE CAST(spare As REAL) WHEN 0 THEN 1 ELSE CAST(spare As REAL) END),1)
    from table
    This did the trick! Thankyou very much for your help!

Posting Permissions

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