Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2011
    Posts
    10

    Unanswered: 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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb

    Quote Originally Posted by wtjohnson View Post
    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

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

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

  6. #6
    Join Date
    Feb 2011
    Posts
    10

    Still Same error but I reposted below.

    Quote Originally Posted by Lenny77 View Post
    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

Posting Permissions

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