| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-11-09, 10:53
|
|
Registered User
|
|
Join Date: Sep 2009
Posts: 6
|
|
|
Remove null rows from table
|
|
Hi,
I have a very strange problem
I have a table say a that has values
table a
startdate enddate period
27/03/2008 22/04/2008 1
10/05/2008 01/06/2008 2
23/05/2008 05/06/2008 3
I have another table b from which a pick up code and year
YEAR PERIOD CODE START_DATE END_DATE
2008 1 a 26/03/2008 22/04/2008
2008 1 b 09/04/2008 06/05/2008
2008 2 a 23/04/2008 20/05/2008
2008 2 b 07/05/2008 03/06/2008
2008 3 a 21/05/2008 17/06/2008
2008 3 b 04/06/2008 01/07/2008
The problem is that when i try to join the above offercodes to get the respective code and year for table 1
I get something like this
YEAR CODE
--------------- ---------------------
2008 a
- -
- -
2008 b
2008 a
- -
I need to remove the NULL rows.I am using CASE expressions for getting the values.
Can anybody help?
I cannot share the query so if anybody could help me out without seeing the query it would be great!!
|
|
11-11-09, 11:13
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
You have to think, just a little beat:
Code:
select date('10/05/2008'), date('01/06/2008')
from sysibm.sysdummy1
Result:
we have Start period > End period
Lenny
|
|
11-11-09, 12:10
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
I guessed lifzgud is using local date format dd/mm/yyyy.
(Searching by "Date and time formats by territory code" in Information Center, I found some territory is using that format.)
But, I want to recommend to use ISO format to share with peoples in other countries.
Anyway, this would remove NULL rows from your result.
Code:
SELECT *
FROM (your query
/* I cannot share the query */
) q
WHERE year IS NOT NULL
OR code IS NOT NULL
|
|
11-11-09, 12:21
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
I could reproduce your result with NULL rows by using CASE expressions, like this:
Changed: data type of(start_date and end_date) in sample data
Code:
------------------------------ Commands Entered ------------------------------
WITH
/**************************************************
Start of sample data
**************************************************/
a(start_date, end_date, period) AS (
SELECT DATE(start_date)
, DATE(end_date)
, period
FROM (
VALUES
('27.03.2008', '22.04.2008', 1)
,('10.05.2008', '01.06.2008', 2)
,('23.05.2008', '05.06.2008', 3)
) q(start_date, end_date, period)
)
,b(year, period, code, start_date, end_date) AS (
SELECT year, period, code
, DATE(start_date)
, DATE(end_date)
FROM (
VALUES
(2008, 1, 'a', '26.03.2008', '22.04.2008')
,(2008, 1, 'b', '09.04.2008', '06.05.2008')
,(2008, 2, 'a', '23.04.2008', '20.05.2008')
,(2008, 2, 'b', '07.05.2008', '03.06.2008')
,(2008, 3, 'a', '21.05.2008', '17.06.2008')
,(2008, 3, 'b', '04.06.2008', '01.07.2008')
) q(year, period, code, start_date, end_date)
)
/**************************************************
End of sample data
**************************************************/
SELECT CASE
WHEN a.start_date >= b.start_date
AND a.end_date <= b.end_date THEN
b.year
END AS year
, CASE
WHEN a.start_date >= b.start_date
AND a.end_date <= b.end_date THEN
b.code
END AS code
FROM a
INNER JOIN
b
ON b.period = a.period
ORDER BY
a.period
, b.code;
------------------------------------------------------------------------------
YEAR CODE
----------- ----
2008 a
- -
- -
2008 b
2008 a
- -
6 record(s) selected.
But, I thought that it is too complex.
Join with additional conditions would result without NULL rows, like this:
Code:
/* same data as previous example */
SELECT b.year
, b.code
FROM a
INNER JOIN
b
ON b.period = a.period
AND a.start_date >= b.start_date
AND a.end_date <= b.end_date
ORDER BY
a.period
, b.code;
------------------------------------------------------------------------------
YEAR CODE
----------- ----
2008 a
2008 b
2008 a
3 record(s) selected.
|
Last edited by tonkuma; 11-11-09 at 12:31.
|
11-11-09, 12:30
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Quote:
Originally Posted by tonkuma
I guessed lifzgud is using local date format dd/mm/yyyy.
(Searching by "Date and time formats by territory code" in Information Center, I found some territory is using that format.)
But, I want to recommend to use ISO format to share with peoples in other countries.
Anyway, this would remove NULL rows from your result.
Code:
SELECT *
FROM (your query
/* I cannot share the query */
) q
WHERE year IS NOT NULL
OR code IS NOT NULL
|
How I understand he's mixed formats.
Lenny
|
|
11-11-09, 12:44
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
The format of the values of start_date/end_date are
aa/bb/cccc
where 01 <= aa <= 27, 03 <= bb <= 07 and cccc = 2008.
That is the reason that I guessed the format of columns may be "dd/mm/yyyy".
|
|
11-11-09, 14:05
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Quote:
Originally Posted by tonkuma
The format of the values of start_date/end_date are
aa/bb/cccc
where 01 <= aa <= 27, 03 <= bb <= 07 and cccc = 2008.
That is the reason that I guessed the format of columns may be "dd/mm/yyyy".
|
Try to run this...
Code:
select date('10/05/2008'), date('01/06/2008'),
date('27/03/2008'), date('22/04/2008'),
date('23/05/2008'), date('05/06/2008')
from sysibm.sysdummy1
...compare to ...
Code:
select date('10/05/2008'), date('01/06/2008')
from sysibm.sysdummy1
...and you will understand he's working with date as with string.
That's why it take a place.
Lenny
|
|
11-11-09, 19:53
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
I already wrote:
Quote:
I guessed lifzgud is using local date format dd/mm/yyyy.
(Searching by "Date and time formats by territory code" in Information Center, I found some territory is using that format.)
|
And I changed sample data '10/05/2008' and '01/06/2008' to '10.05.2008' and '01.06.2008' in my queries.
So, they were interpreted as May 10th, 2008 and June 1st, 2008.
If you want to execute
Code:
select date('10/05/2008'), date('01/06/2008'),
date('27/03/2008'), date('22/04/2008'),
date('23/05/2008'), date('05/06/2008')
from sysibm.sysdummy1
without error,
you should create database USING CODESET codeset TERRITORY territory which support date format "dd/mm/yyyy".
France and Germany would be examples of such territory.
Please see:
"Date and time formats by territory code"
and
"Supported territory codes and code pages"
in
IBM DB2 9.7 for Linux, UNIX and Windows Information Center
|
|
11-12-09, 10:06
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
tonkuma, we do not discuss about your query, which is correct, but about original query which is not such good as yours 
Lenny
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Remove null rows from table
