Problem while selecting 3 rows of each group

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
User avatar
Gurugars
Active Member
Posts: 107
Joined: Sat Oct 23, 2010 2:17 pm
Location: Chennai,India.

Problem while selecting 3 rows of each group

Post by Gurugars » Thu Sep 04, 2014 7:46 pm

Hi All,

I am trying to select 3 rows of each group from a table. Please see below what I've tried.

Code: Select all

---------+---------+---------+---------+---------+---------+---------+---------+
  SELECT  POLISNR, VOLGNR_VORD                                          00010003
           FROM                                                         00011003
           ( SELECT POLISNR, VOLGNR_VORD,                               00012003
                    RANK() OVER(PARTITION BY POLISNR                    00013005
                                  ORDER BY VOLGNR_VORD) AS NUM          00014004
             FROM YSS1S1C1.T2941_VORD                                   00014103
           ) AS X                                                       00015005
           WHERE X.NUM <=3                                              00016005

But I'm getting the DB error as below while trying to run it thru SPUFI.

Code: Select all

---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -4700, ERROR&#58;  ATTEMPT TO USE NEW FUNCTION BEFORE NEW        
         FUNCTION MODE                                                          
DSNT418I SQLSTATE   = 56038 SQLSTATE RETURN CODE                                
DSNT415I SQLERRP    = DSNHSMF2 SQL PROCEDURE DETECTING ERROR                    
DSNT416I SQLERRD    = 58 0  0  -1  235  507 SQL DIAGNOSTIC INFORMATION          
DSNT416I SQLERRD    = X'0000003A'  X'00000000'  X'00000000'  X'FFFFFFFF'        
         X'000000EB'  X'000001FB' SQL DIAGNOSTIC INFORMATION                    
---------+---------+---------+---------+---------+---------+---------+---------+

Some one please help me out in resolving the problem. Or any other alternative query to do the need.

We are running on DB2 V10

Code: Select all

DSN7100I  -D01T DSN7GCMD                                              
*** BEGIN DISPLAY OF GROUP&#40;........&#41; CATALOG LEVEL&#40;101&#41; MODE&#40;CM8 &#41;    
                  PROTOCOL LEVEL&#40;2&#41;  GROUP ATTACH NAME&#40;....&#41;          
--------------------------------------------------------------------  
DB2                                    DB2 SYSTEM    IRLM             
MEMBER   ID  SUBSYS CMDPREF   STATUS   LVL NAME      SUBSYS IRLMPROC  
-------- --- ----   --------  -------- --- --------  ----   --------  
........   0 D01T   -D01T     ACTIVE   101 TST1      I01T   D01TIRLM  
--------------------------------------------------------------------  
SPT01 INLINE LENGTH&#58;        32138                                     
*** END DISPLAY OF GROUP&#40;........&#41;                                    
DSN9022I  -D01T DSN7GCMD 'DISPLAY GROUP ' NORMAL COMPLETION           
***                                                                   

Bunch of thanks in advance.
Guru:-)

You're never fully dressed without a smile :)

NicC
Active Member
Posts: 650
Joined: Sun Jul 24, 2011 5:27 pm
Location: Down on the pig farm

Post by NicC » Thu Sep 04, 2014 11:38 pm

Go see your DBA. The message is clear - you are trying to use something, that although is included with the release, has not been activated.
Regards
Nic

User avatar
Gurugars
Active Member
Posts: 107
Joined: Sat Oct 23, 2010 2:17 pm
Location: Chennai,India.

Post by Gurugars » Tue Sep 09, 2014 2:10 pm

Thank You Nic.

Let me have a chat with them on this.
Guru:-)

You're never fully dressed without a smile :)

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others