|
View previous topic :: View next topic
|
| Author |
Message |
Auryn
New User
Joined: 11 Jan 2006
Posts: 88
Location: Lower Saxony (DE)
|
|
|
|
Hello,
I've got a little blockade, maybe my question is pretty easy...
I've got a table with several integer but nullable columns.
Any of them can contain some values with special meaning (i.e. NULL, 0, 99999) or any other ordinary valid integer values.
I'd like to analyse the columns for their content so the expected result is i.e.
| Code: |
Value-grp col_1 col_2 col_3
NULL 1,111 2,222 3,333
0 4,444 5,555 6,666
99999 7,777 8,888 9,999
valid 11,111 22,222 33,333 |
Of cause the value group are DECODEd values of the real content of the three columns, in this case of type character(5).
Has anybody of you a quick idea how to solve my problem.
I know about SUM(DECODE(<col>, <value>, 1, 0)) and GROUP BY, but this transponses the result so it is not what I'm looking for.
Thank you all for your support and your hints. |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2286
Location: USA
|
|
|
|
I tried to read the post three times, but yet not able to understand the initial idea: what and how needs to be verified/analyzed?
Please, at least present here:
- the exact sample of you test input data,
- the code sample you have tried,
- the result you expected to receive,
- the result actually produced by your code.
Sorry. |
|
| Back to top |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 88
Location: Lower Saxony (DE)
|
|
|
|
One table has among others three cols of type nullable integer so the contained values can be NULL, 0, 99999 or any other "valid"
I'd like to know how often col1 has no value (NULL), the value 0, the value 99999 or any other value.
The same for 2nd and 3rd col.
But think I found a solution. Gimme some minutes... |
|
| Back to top |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 88
Location: Lower Saxony (DE)
|
|
|
|
| Code: |
WITH wertgru ( wert )
AS ( SELECT '1- NULL' FROM sysibm.sysdummy1
UNION SELECT '2- 0' FROM sysibm.sysdummy1
UNION SELECT '3- 99999' FROM sysibm.sysdummy1
UNION SELECT '4-valid ' FROM sysibm.sysdummy1)
, werttab ( col_1
, col_2
, col_3
AS (SELECT DECODE(col_1 , NULLIF(0, 0), '1- NULL'
, 0 , '2- 0'
, 99999 , '3- 99999'
, '4-valid ')
, DECODE(col_2 , NULLIF(0, 0), '1- NULL'
, 0 , '2- 0'
, 99999 , '3- 99999'
, '4-valid ')
, DECODE(col_3 , NULLIF(0, 0), '1- NULL'
, 0 , '2- 0'
, 99999 , '3- 99999'
, '4-valid ')
FROM schema.mytable )
SELECT wrt.wert
, SUM(DECODE(tab.col_1, wrt.wert, 1, 0))
anz_col_1
, SUM(DECODE(tab.col_2, wrt.wert, 1, 0))
anz_col_2
, SUM(DECODE(tab.col_3, wrt.wert, 1, 0))
anz_col_3
FROM wertgru wrt
, werttab tab
GROUP BY GROUPING SETS ((wrt.wert), ())
WITH UR; |
wert-suffix #- only for ordering
I know, contains a cartesian product of both cte.
Feel free to provide a more performant or "better" solution. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2286
Location: USA
|
|
|
|
| Auryn wrote: |
| One table has among others three cols of type nullable integer |
First of all: your sample demonstrates the table WITH FOUR COLUMNS, not three...
Please, at least present here:
- the exact sample of you test input data,
(- the code sample you have tried, - done, but the idea is unclear)
- the result you expected to receive,
- the result actually produced by your code. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
