Sql query

annamaria · #1 (**permalink**) 04-22-07, 05:52

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)

Peter.Vanroose · #2 (**permalink**) 04-22-07, 06:19

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

stolze · #3 (**permalink**) 04-22-07, 12:10

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.)

annamaria · #4 (**permalink**) 04-22-07, 12:56

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)

Peter.Vanroose · #5 (**permalink**) 04-22-07, 14:52

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 · #6 (**permalink**) 04-22-07, 15:03

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

stolze · #7 (**permalink**) 04-22-07, 16:02

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.

stolze · #8 (**permalink**) 04-22-07, 16:16

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"?

Quote:

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.

Quote:

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).

Quote:

'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.

Quote:

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.