IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

problem with sql select query?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Wed Apr 16, 2008 5:01 pm
Reply with quote

EXEC SQL
SELECT CNTL_TBL_NME,
MIN(CNTL_DTE),
CNTL_FLAG
INTO :CNTL-TBL-NME,
:CNTL-DTE,
:CNTL-FLAG
FROM ARW_CNTL_TABLE
WHERE CNTL_FLAG IN ('E','H')
GROUP BY CNTL_DTE
ORDER BY CNTL_DTE
END-EXEC.

please let me know what is wrong in the above query?
Back to top
View user's profile Send private message
kalukakkad

New User


Joined: 10 Mar 2005
Posts: 81

PostPosted: Wed Apr 16, 2008 5:18 pm
Reply with quote

You should GROUP BY CNTL_TBL_NME and CNTL_FLAG.

Whenever you are using a column function, you have to GROUP BY all the other columns you are trying to fetch.
Back to top
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Wed Apr 16, 2008 5:24 pm
Reply with quote

I am not sure why you are grouping by CNTL_DTE and looking for min of CNTL_DTE. I think probably you will be grouping for CNTL_TBL_NME and CNTL_FLAG. Please let us know what you trying to achive.

Thanks,
Prajesh
Back to top
View user's profile Send private message
pranav yadav

New User


Joined: 04 Apr 2008
Posts: 9
Location: Noida, India

PostPosted: Wed Apr 16, 2008 11:40 pm
Reply with quote

hi
the field used for group by should be the one that you have already selected.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Apr 17, 2008 1:08 am
Reply with quote

pranav:
Quote:

the field used for group by should be the one that you have already selected.


Your statement is not completely correct. I can have a table defined with 30 columns and write the following select that is perfectly valid. You can group by any column or computed value based on a column, but you must also group by each column between your SELECT and FROM that is not an aggregate function.

Code:

SELECT col25
FROM table
WHERE col21 = 'Y'
GROUP by col1, col25.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Apr 17, 2008 1:10 am
Reply with quote

Quote:
Whenever you are using a column function, you have to GROUP BY all the other columns you are trying to fetch.


Not quite, see above. When you use a function in your select list it is a materialized column. But, if that function is an aggregate function, you don't need to specify it in the GROUP BY clause.
Back to top
View user's profile Send private message
pranav yadav

New User


Joined: 04 Apr 2008
Posts: 9
Location: Noida, India

PostPosted: Thu Apr 17, 2008 10:09 am
Reply with quote

stodlas,

thanks for correcting me.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Map Vols and Problem Dataset All Other Mainframe Topics 2
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
Search our Forums:

Back to Top