| |
|
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.
|
 |
02-22-11, 16:53
|
|
Registered User
|
|
Join Date: Feb 2011
Posts: 10
|
|
|
Coelesce with Timestamp
|
|
I am new to DB2 and I am having an issue with Coelesce with Timestamps:
Here is my query and I am receiving an error because some of the timestamp values could be null. I am having an issue as to where to put coelesce in these cases?
Code:
SELECT * FROM (Select LPEM As EMPNAME, SUM(LPQTY) As CASES, coalesce(SUM(timestampdiff (4, char(TIMESTAMP(CHAR(LPFDT)) - TIMESTAMP(CHAR(LPSDT)) ))), 0) as JobTimeMinutes, coalesce(SUM((timestampdiff (2, char(TIMESTAMP(CHAR(LPFDT)) - TIMESTAMP(CHAR(LPSDT)) )) - ((timestampdiff (4, char(TIMESTAMP(CHAR(LPFDT)) - TIMESTAMP(CHAR(LPSDT)) )))*60) )), 0) as JobTimeSeconds from KWABSPRDF.WEMPLPT where LPSDT >=" + startTime + " AND LPFDT <=" + EndTime + " AND LPWH = '001' AND LPQTY <> 0 GROUP BY LPEM ) As PickingTotal LEFT JOIN (Select LPEM As EMPNAME, COUNT(LPUM) as ForkPallets from KWABSPRDF.WEMPLPT WHERE LPSDT >=" + startTime + " AND LPFDT <=" + EndTime + " AND LPWH = '001' AND LPUM <> '' AND LPUM <> '***' AND LPQTY <> 0 AND LPLC = 'DIR' GROUP BY LPEM) AS ForkTotal on PickingTotal.EMPNAME = ForkTotal.EMPNAME
|
|
02-22-11, 17:03
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
I think it is best to put the coalesce on the object in question, that way you know what it will do. My suggestion is to directly coalesce the timestamp.
Andy
|
|
02-22-11, 18:06
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
|
|
Quote:
Originally Posted by wtjohnson
I am new to DB2 and I am having an issue with Coelesce with Timestamps:
Here is my query and I am receiving an error because some of the timestamp values could be null. I am having an issue as to where to put coelesce in these cases?
Code:
SELECT * FROM (Select LPEM As EMPNAME, SUM(LPQTY) As CASES, coalesce(SUM(timestampdiff (4, char(TIMESTAMP(CHAR(LPFDT)) - TIMESTAMP(CHAR(LPSDT)) ))), 0) as JobTimeMinutes, coalesce(SUM((timestampdiff (2, char(TIMESTAMP(CHAR(LPFDT)) - TIMESTAMP(CHAR(LPSDT)) )) - ((timestampdiff (4, char(TIMESTAMP(CHAR(LPFDT)) - TIMESTAMP(CHAR(LPSDT)) )))*60) )), 0) as JobTimeSeconds from KWABSPRDF.WEMPLPT where LPSDT >=" + startTime + " AND LPFDT <=" + EndTime + " AND LPWH = '001' AND LPQTY <> 0 GROUP BY LPEM ) As PickingTotal LEFT JOIN (Select LPEM As EMPNAME, COUNT(LPUM) as ForkPallets from KWABSPRDF.WEMPLPT WHERE LPSDT >=" + startTime + " AND LPFDT <=" + EndTime + " AND LPWH = '001' AND LPUM <> '' AND LPUM <> '***' AND LPQTY <> 0 AND LPLC = 'DIR' GROUP BY LPEM) AS ForkTotal on PickingTotal.EMPNAME = ForkTotal.EMPNAME
|
Try to make folowing chage:
Code:
SELECT * FROM (Select LPEM As EMPNAME, SUM(LPQTY) As CASES, coalesce(SUM(float(timestampdiff (4, char(TIMESTAMP(CHAR(LPFDT)) - TIMESTAMP(CHAR(LPSDT))) ))), 0) as JobTimeMinutes, coalesce(SUM(( float(timestampdiff (2, char(TIMESTAMP(CHAR(LPFDT)) - TIMESTAMP(CHAR(LPSDT))) )) - ((float(timestampdiff (4, char(TIMESTAMP(CHAR(LPFDT)) - TIMESTAMP(CHAR(LPSDT))) )))* 60.0) )), 0) as JobTimeSeconds from KWABSPRDF.WEMPLPT where LPSDT >=" + startTime + " AND LPFDT <=" + EndTime + " AND LPWH = '001' AND LPQTY <> 0 GROUP BY LPEM ) As PickingTotal LEFT JOIN (Select LPEM As EMPNAME, COUNT(LPUM) as ForkPallets from KWABSPRDF.WEMPLPT WHERE LPSDT >=" + startTime + " AND LPFDT <=" + EndTime + " AND LPWH = '001' AND LPUM <> '' AND LPUM <> '***' AND LPQTY <> 0 AND LPLC = 'DIR' GROUP BY LPEM) AS ForkTotal on PickingTotal.EMPNAME = ForkTotal.EMPNAME
Lenny
|
|
02-23-11, 11:15
|
|
Registered User
|
|
Join Date: Feb 2011
Posts: 10
|
|
|
Same Error:
Value in date, time, or timestamp string not valid.
Cause . . . . . : The string representation of a date, time or timestamp value is not in the acceptable range. *N is either the character string constant that is not valid or the column or host variable that contained the string. If the name is *N, then the value was found in an expression specified in the statement. If the value was found in a host variable, then the host variable number is 0. The proper ranges for date, time, or timestamp values are as follows: -- The range for years is from 0001 to 9999. -- The range for months is from 1 to 12. -- The range for days is from 1 - 30 for April, June, September, and November, from 1 - 28 for February and from 1 to 31 for all other months. In a leap year, the range for February can be from 1 to 29. -- The range for days in a Julian date is from 001 to 366 for a leap year or 001 to 365 days for all other years. -- The range for hours is from 0 to 24. If the hour is 24, then the other parts of the time values must be zeros. If the time format is USA, then the hour cannot be greater than 12. -- The range for minutes is from 0 to 59. -- The range for seconds is from 0 to 59. -- The range for microseconds is from 0 to 999999. Recovery . . . : Ensure that the date, time, or timestamp value conforms to the ranges for the data type it represents. Try the request again.
|
|
02-23-11, 11:29
|
|
Registered User
|
|
Join Date: Feb 2011
Posts: 10
|
|
The Queries work independently:
1st Query
Code:
"Select LPEM As EMPNAME, COUNT(LPUM) as ForkPallets from KWABSPRDF.WEMPLPT WHERE LPSDT >=" + startTime + " AND LPFDT <=" + EndTime + " AND LPWH = '001' AND LPUM <> '' AND LPUM <> '***' AND LPQTY <> 0 AND LPLC = 'DIR' GROUP BY LPEM ORDER BY LPEM";
2nd Query
Code:
"Select LPEM As EMPNAME, SUM(LPQTY) As CASES, SUM(timestampdiff (4, char(TIMESTAMP(CHAR(LPFDT)) - TIMESTAMP(CHAR(LPSDT)) ))) as JobTimeMinutes, SUM((timestampdiff (2, char(TIMESTAMP(CHAR(LPFDT)) - TIMESTAMP(CHAR(LPSDT)) )) - ((timestampdiff (4, char(TIMESTAMP(CHAR(LPFDT)) - TIMESTAMP(CHAR(LPSDT)) )))*60) )) as JobTimeSeconds from KWABSPRDF.WEMPLPT where LPSDT >=" + startTime + " AND LPFDT <=" + EndTime + " AND LPWH = '001' AND LPQTY <> 0 GROUP BY LPEM ORDER BY EMPNAME ";
The problem is the join. I am trying to left join the tables and that is where I am running into the error???? How do I overcome this? The join is above.
|
|
02-23-11, 11:35
|
|
Registered User
|
|
Join Date: Feb 2011
Posts: 10
|
|
|
Still Same error but I reposted below.
Quote:
Originally Posted by Lenny77
Try to make folowing chage:
Code:
SELECT * FROM (Select LPEM As EMPNAME, SUM(LPQTY) As CASES, coalesce(SUM(float(timestampdiff (4, char(TIMESTAMP(CHAR(LPFDT)) - TIMESTAMP(CHAR(LPSDT))) ))), 0) as JobTimeMinutes, coalesce(SUM(( float(timestampdiff (2, char(TIMESTAMP(CHAR(LPFDT)) - TIMESTAMP(CHAR(LPSDT))) )) - ((float(timestampdiff (4, char(TIMESTAMP(CHAR(LPFDT)) - TIMESTAMP(CHAR(LPSDT))) )))* 60.0) )), 0) as JobTimeSeconds from KWABSPRDF.WEMPLPT where LPSDT >=" + startTime + " AND LPFDT <=" + EndTime + " AND LPWH = '001' AND LPQTY <> 0 GROUP BY LPEM ) As PickingTotal LEFT JOIN (Select LPEM As EMPNAME, COUNT(LPUM) as ForkPallets from KWABSPRDF.WEMPLPT WHERE LPSDT >=" + startTime + " AND LPFDT <=" + EndTime + " AND LPWH = '001' AND LPUM <> '' AND LPUM <> '***' AND LPQTY <> 0 AND LPLC = 'DIR' GROUP BY LPEM) AS ForkTotal on PickingTotal.EMPNAME = ForkTotal.EMPNAME
Lenny |
Same Error but I reposted below
|
|
| 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
>
Coelesce with Timestamp
