Results 1 to 8 of 8

Thread: Sql query

  1. #1
    Join Date
    Apr 2007
    Posts
    51

    Smile Unanswered: Sql query

    I'm making a query with some nested tables: now I have five new columns
    but in each record only one of these columns has a value.
    I want to create a unique column out the five. I tried to sum the columns
    field1 + field2 + field3 as field6 but columns6 resulted empty. Maybe the columns with no value can't be summed to a number. Maybe I could say if field1 <> null (??) then field 1 when field2 <> null then field 2 end as field6. Would it work?
    How can I check what kind of value has a field? How can I choose the format
    of a field?

    Thank you.

    Anna - Verona - (Italy)

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by annamaria
    I tried to sum the columns field1 + field2 + field3 as field6 but columns6 resulted empty.
    Try this:
    Code:
    SELECT coalesce(field1,0)+coalesce(field2,0)+coalesce(field3,0) AS field6
    FROM mytable
    Or, if the fields are textual instead of numerical:
    Code:
    SELECT coalesce(field1,'')||coalesce(field2,'')||coalesce(field3,'') AS field6
    FROM mytable
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What should the sum actually achieve?
    What exactly do you mean that the field doesn't have a value? Is it NULL? If so, then it is no surprise that you got NULL as result of the additions. After all, you are adding "some number + unknown + ...". Unknown is not a neutral element for addition (0 is), so the result will always be unknown, i.e. NULL.

    I think you want to have this?
    Code:
    COALESCE(column1, column2, column3, column4, column5)
    Adding things up is not necessary if you really can rely on only 1 of the 5 values being non-NULL. (You should add constraints to let the DBMS enforce that.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Apr 2007
    Posts
    51
    Thank you, Stolze.
    The new five columns were created by making math operations among
    numbers with when clauses:
    when field A = 8 then (field B * field C) as field R1
    when field A = 7 then (field C / field D) as field R2
    So now I have columns R1 and R2. I don't know what kind of format there is
    in column R1 but in record A = 7 it is empty (doesn't it mean NULL?).
    I hope that the number in my new columns is in the number format,and it can
    be used to make other math operations. Is it so?
    How can I check what kind of value has a field? How can I choose the format
    of a field?


    'Adding things up is not necessary if you really can rely on only 1 of the 5 values being non-NULL. (You should add constraints to let the DBMS enforce that.)'
    What do you mean. What is it that should be enforced?

    Stolze, please could you read my other post in the
    same forum (title: Sql query from DB2)?

    Have you watched the motoGP race in Turkey today?
    I can't believe Valentino Rossi did what he did!!!! (He was the 10th to arrive!).

    Thank you.
    bye.
    Anna - Verona (Italy)

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by stolze
    Unknown is not a neutral element for addition (0 is), so the result will always be unknown, i.e. NULL.
    Just a slight nuance to this statement:
    This is true for a scalar function (here addition) but not for aggregate functions: SUM(col1) will add up all non-NULL fields in col1, thereby ignoring the NULLs.
    This nuance is important since we were speaking of addition, and both "+" and "SUM()" are doing additions.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by annamaria
    when fieldA = 8 then (fieldB * fieldC) as field R1
    when fieldA = 7 then (fieldC / fieldD) as field R2
    Most probably a better solution would be to put both results in the same column (say, R1). And maybe put an indicator (with value 1 or 2) in an extra column to indicate in which case that row is.
    Example:
    Code:
    SELECT case when fieldA=8 then fieldB*fieldC
                when fieldA=7 then fieldC/fieldD
                              else 0      end   AS R1,
           case when fieldA=8 then 1
                when fieldA=7 then 2
                              else 3      end   AS indicator
    FROM   ...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Peter.Vanroose
    Just a slight nuance to this statement:
    This is true for a scalar function (here addition) but not for aggregate functions: SUM(col1) will add up all non-NULL fields in col1, thereby ignoring the NULLs.
    This nuance is important since we were speaking of addition, and both "+" and "SUM()" are doing additions.
    You are correct, of course.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by annamaria
    The new five columns were created by making math operations among numbers with when clauses:
    when field A = 8 then (field B * field C) as field R1
    when field A = 7 then (field C / field D) as field R2
    So now I have columns R1 and R2. I don't know what kind of format there is
    in column R1 but in record A = 7 it is empty (doesn't it mean NULL?).
    I wouldn't use the term "empty" to denote unknown values. NULL is intended for that. Saying "empty" has the huge problem that a string may be empty but not NULL. An empty string happens to be a string that contains no characters. Having NULL there indicates the absence of a string. You have the same thing in many programming languages: an empty string in Java is "". A not-existing string is null.

    So I suggest that we stick with the relational terminology here.

    Note: SQL doesn't have fields: there are tables with columns and rows (or records), and each row has values. Each column of a table has a (declared) data type, which is probably what you are referring to with "format"?

    I hope that the number in my new columns is in the number format,and it can be used to make other math operations. Is it so?
    There are a few different numeric data types in DB2: SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, and DOUBLE. Which one will be chosen depends on the data types of the input values for a mathematical operation, e.g. adding two BIGINT values yields a BIGINT.

    How can I check what kind of value has a field? How can I choose the format of a field?
    Checking the value can be done by simply selecting the value. You can choose the data type by applying the CAST function (or some other function).

    'Adding things up is not necessary if you really can rely on only 1 of the 5 values being non-NULL. (You should add constraints to let the DBMS enforce that.)'
    What do you mean. What is it that should be enforced?
    A constraint in SQL is a rule that tells the DBMS certain conditions that must be met by the data in a given table. For example, you could say that the values in a column ID in table T must be larger than 0 by adding such a constraint:
    Code:
    ALTER TABLE t
       ADD CONSTRAINT positive_id CHECK ( id > 0 )
    Everytime someone tries to insert an ID value <= 0, DB2 will raise an except and prevent the operation. Same thing with other, more complex conditions...

    I believe that you are asking a lot of very basic questions, which implies to me that some foundations on relational database systems is not yet present. Therefore, it is my recommendation that you grab a good book on SQL. It should cover at least the relational model, relational algebra, and relational calculus as well as an introduction to SQL.

    Have you watched the motoGP race in Turkey today?
    I can't believe Valentino Rossi did what he did!!!! (He was the 10th to arrive!).
    He made a wrong choice in tyre selection. That's life... err... racing.
    (I had a similar problem two weeks ago and ended up in the gravel.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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