| View previous topic :: View next topic |
| Author |
Message |
Piscesian
Beginner
Joined: 17 Dec 2004
Posts: 14
Topics: 8
Location: Monrovia,California
|
 Posted: Fri May 18, 2007 4:35 pm Post subject: Help in SQL Query |
 |
|
I'm extracting records from couple of DB2 tables and displaying them on a front end screen.
I'm facing an issue with some records present in a particular DB2 table(SA_SCHEDULE) | Code: |
AGT_ID START_DT START_TM STOP_DT STOP_TM
3648 2006-12-04 00.00.00 2006-12-04 10.30.00
3648 2006-12-05 00.00.00 2006-12-05 10.30.00
3648 2006-12-05 23.30.00 2006-12-06 10.00.00
3648 2006-12-07 00.00.00 2006-12-07 08.30.00 |
As seen in the above example,the third row contains data for the agent who has just signed in
before midnight and working hours cross over the next day.
Currently my query is | Code: |
SELECT (T7.START_DT)
,(SUM(T7.ADHERE_TM)*100.00/SUM(T7.TOTAL_TM)) AS PCT_PROD
,100-(SUM(T7.ADHERE_TM)*100.00/SUM(T7.TOTAL_TM)) AS PCT_UNPROD
....
.....
FROM CCOT.CCC_EMPLOYEE T1
,CCOT.CCC_ORG T4
,CCOT.CCC_SUPERVISORS T5
,CCOT.CCC_MANAGERS T6
,CCOT.SA_SCHEDULE T7
WHERE (T7.AGT_ID = T1.AGT_ID1 OR T7.AGT_ID = T1.AGT_ID2)
AND T1.ACTIVE_IND = 'A'
AND T1.USER_ID = '&USERID'
AND (T7.START_DT) BETWEEN '&FROMDT' AND '&TODT'
|
Current results:
===============
| Code: |
The results I get for AGT_ID 3648 is like this
START_DT PCT_PROD PCT_UNPROD SCH_TIME
---------+---------+---------+---------+---------+---------+-
2006-12-04 100.00 .00 9:30
2006-12-05 80.16 19.84 10:05
2006-12-07 100.00 .00 6:45
|
Expected results:
=================
I need to query the SA_SCHEDULE such that
during time crossover(say 60 minutes before midnight),I want
to show it as next day in START_DT. When START_TM>23.00.00
then START_DT should be START_DT + 1
| Code: |
START_DT PCT_PROD PCT_UNPROD SCH_TIME
---------+---------+---------+---------+---------+---------+-
2006-12-04 100.00 .00 9:30
2006-12-05 100.00 .00 10:30
2006-12-06 100.00 .00 6:45
2006-12-07 100.00 .00 6:45
|
How do I achieve this? |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Fri May 18, 2007 5:14 pm Post subject: |
|
|
Piscesian,
use a case stmt for the column as shown below
| Code: |
SELECT (CASE WHEN HOUR(START_TM) >= 23
THEN DATE(T7.START_DT + 1 DAY)
ELSE T7.START_DT END)
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Piscesian
Beginner
Joined: 17 Dec 2004
Posts: 14
Topics: 8
Location: Monrovia,California
|
| Posted: Fri May 18, 2007 5:25 pm Post subject: |
|
|
Hi Kolusu,
Am getting the following error
DSNT408I SQLCODE = -122, ERROR: A SELECT STATEMENT WITH NO GROUP BY CLAUSE CONTAINS A COLUMN NAME AND A COLUMN FUNCTION IN THE SELECT CLAUSE OR A COLUMN NAME IS CONTAINED IN THE SELECT CLAUSE BUT NOT IN THE GROUP BY CLAUSE
In my query, I have given
GROUP BY T7.START_DT
;; |
|
| Back to top |
|
|
Piscesian
Beginner
Joined: 17 Dec 2004
Posts: 14
Topics: 8
Location: Monrovia,California
|
| Posted: Sat May 19, 2007 2:01 pm Post subject: |
|
|
Hi,
Can somebody throw light on this as to why this CASE statement is failing..
Thanks, |
|
| Back to top |
|
|
CraigG
Intermediate
Joined: 02 May 2007
Posts: 202
Topics: 0
Location: Viginia, USA
|
| Posted: Sat May 19, 2007 9:25 pm Post subject: |
|
|
| Piscesian wrote: | Hi Kolusu,
Am getting the following error
DSNT408I SQLCODE = -122, ERROR: A SELECT STATEMENT WITH NO GROUP BY CLAUSE CONTAINS A COLUMN NAME AND A COLUMN FUNCTION IN THE SELECT CLAUSE OR A COLUMN NAME IS CONTAINED IN THE SELECT CLAUSE BUT NOT IN THE GROUP BY CLAUSE
In my query, I have given
GROUP BY T7.START_DT
;; |
In your query the case causes the column to lose it's name you could add 'as start_dt' to give it a name but that wouldn't help with the 'group by'. I don't think you group by a computed field. |
|
| Back to top |
|
|
pharia01
Beginner
Joined: 07 Sep 2006
Posts: 3
Topics: 0
|
| Posted: Wed May 23, 2007 1:34 am Post subject: |
|
|
Piscesian,
In this case, to be able to group by the derived column, refer to a sample example# 6 at http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQJ14/4.2.6?SHELF=&DT=20061213154312&CASE=.
In general, any non aggregate column values are required to be available for "group by" processing before being derived within select statement list. Group by can not be used on a name defined using the AS clause. In order to work around this, as in above example, a sub select can be used to covert derived column into direct column. This might result in negative impact on run time of the query depending on how the over all query is implemented and volume of the tables.
Thanks,
pharia01 |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
