Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

problem with sql select query?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: problem with sql select query?
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    Post subject: Reply to: problem with sql select query?
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SYMNAMES problem jacobdng DFSORT/ICETOOL 7 Thu Dec 22, 2016 7:47 am
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us