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
 

 

Using Order by in a view

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
tomehta

New User


Joined: 18 Aug 2008
Posts: 98
Location: India

PostPosted: Mon May 03, 2010 6:40 pm    Post subject: Using Order by in a view
Reply with quote

Hi
I am trying to use a 'order by' in a view creation which DB2 does not allow for the following syntax.
Code:
CREATE VIEW t395158.VJAEAOINSTRDETAIL AS
SELECT 'TJAEAOINSTRDETAIL' concat '  ' as NameOfTable
,INSTRUMENTID
,ABICHNATBANKTYPE
,CASE WHEN DENOMINATION IS NULL THEN '0'
      ELSE char(DENOMINATION) END AS DENOMINATION
,CASE WHEN DENOMINATIONSC IS NULL THEN '0'
      ELSE char(DENOMINATIONSC) END AS DENOMINATIONSC
,HASPAYINSTRSEGA
,HOLDINAPPLTYPE
,ISAMSELIGIBLE
,ISAPPROVEDBYWM
,ISPRICINGGRPLKD
,ISSETTLEDAFTFUN
,ISSTRUCTURED
,MANDATOR_ID
,CASE WHEN MINFRACUNITAMT IS NULL THEN '0'
      ELSE char(MINFRACUNITAMT) END AS MINFRACUNITAMT
,CASE WHEN MINFRACUNITAMTSC IS NULL THEN '0'
      ELSE char(MINFRACUNITAMTSC) END AS MINFRACUNITAMTSC
,PRICINGGROUPTYPE
,PROCNTRVALFROMDATE
,RISKCCY
,RISKLOCATION
,SPECDIVPAYMENTTYPE
,TARGETDEPOSITARY
,UBSSPECMIDTYPE
FROM D1.TJAEAOINSTRDETAIL
     WHERE INSTRUMENTID IN (SELECT FIID FROM TJAEFIIDRECON)
ORDER BY INSTRUMENTID
;


Getting the below error
Code:
DSNT408I SQLCODE = -109, ERROR:  ORDER BY CLAUSE IS NOT PERMITTED           
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                             
DSNT415I SQLERRP    = DSNHSM6 SQL PROCEDURE DETECTING ERROR                 
DSNT416I SQLERRD    = 2605 0  0  -1  1  0 SQL DIAGNOSTIC INFORMATION         
DSNT416I SQLERRD    = X'00000A2D'  X'00000000'  X'00000000'  X'FFFFFFFF'     
         X'00000001'  X'00000000' SQL DIAGNOSTIC INFORMATION


Thru documentation I came to know ORDER BY cannot be used in CREATE VIEW, CREATE TABLE statements.
However there must be some workaround to provide a order by clause in view definition. Gurus please advise
Regards
Rohit Mehta
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon May 03, 2010 7:05 pm    Post subject:
Reply with quote

Try google with 'ORDER BY in CREATE VIEW DB2'
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Mon May 03, 2010 8:01 pm    Post subject:
Reply with quote

no there isn't .
you can however use :

select * from t395158.VJAEAOINSTRDETAIL order by INSTRUMENTID

the real question is however : Why do you need this?
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 686
Location: Earth

PostPosted: Tue May 04, 2010 1:37 am    Post subject:
Reply with quote

Code:
CREATE VIEW t395158.VJAEAOINSTRDETAIL AS
SELECT t.NameOfTable
,t.INSTRUMENTID
,t.DENOMINATION
,t.DENOMINATIONC
,t.HASPAYINSTRSEGA
,t.HOLDINAPPLTYPE
,t.ISAMSELIGIBLE
,t.ISAPPROVEDBYWM
,t.ISPRICINGGRPLKD
,t.ISSETTLEDAFTFUN
,t.ISSTRUCTURED
,t.MANDATOR_ID
,t.MINFRACUNITAMT
,t.MINFRACUNITAMTSC
,t.PRICINGGROUPTYPE
,t.PROCNTRVALFROMDATE
,t.RISKCCY
,t.RISKLOCATION
,t.SPECDIVPAYMENTTYPE
,t.TARGETDEPOSITARY
,t.UBSSPECMIDTYPE
FROM (
SELECT 'TJAEAOINSTRDETAIL' concat '  ' as NameOfTable
,INSTRUMENTID
,ABICHNATBANKTYPE
,CASE WHEN DENOMINATION IS NULL THEN '0'
      ELSE char(DENOMINATION) END AS DENOMINATION
,CASE WHEN DENOMINATIONSC IS NULL THEN '0'
      ELSE char(DENOMINATIONSC) END AS DENOMINATIONSC
,HASPAYINSTRSEGA
,HOLDINAPPLTYPE
,ISAMSELIGIBLE
,ISAPPROVEDBYWM
,ISPRICINGGRPLKD
,ISSETTLEDAFTFUN
,ISSTRUCTURED
,MANDATOR_ID
,CASE WHEN MINFRACUNITAMT IS NULL THEN '0'
      ELSE char(MINFRACUNITAMT) END AS MINFRACUNITAMT
,CASE WHEN MINFRACUNITAMTSC IS NULL THEN '0'
      ELSE char(MINFRACUNITAMTSC) END AS MINFRACUNITAMTSC
,PRICINGGROUPTYPE
,PROCNTRVALFROMDATE
,RISKCCY
,RISKLOCATION
,SPECDIVPAYMENTTYPE
,TARGETDEPOSITARY
,UBSSPECMIDTYPE
FROM D1.TJAEAOINSTRDETAIL
     WHERE INSTRUMENTID IN (SELECT FIID FROM TJAEFIIDRECON)
ORDER BY INSTRUMENTID ) AS t

;

I liked GuyC's solution. However, you could also try above SQL to achieve the same thing. It is convoluted but let us know if this serves your purpose.
Back to top
View user's profile Send private message
tomehta

New User


Joined: 18 Aug 2008
Posts: 98
Location: India

PostPosted: Tue May 04, 2010 6:30 pm    Post subject:
Reply with quote

Tried this one, however,
Code:
CREATE VIEW t395158.TEST1 AS                                     
SELECT t.NameOfTable                                                 
,t.INSTRUMENTID                                                     
,t.DENOMINATION                                                     
,t.DENOMINATIONC                                                     
,t.HASPAYINSTRSEGA                                                   
,t.HOLDINAPPLTYPE                                                   
,t.ISAMSELIGIBLE                                                     
,t.ISAPPROVEDBYWM                                                   
,t.ISPRICINGGRPLKD                                                   
,t.ISSETTLEDAFTFUN                                                   
,t.ISSTRUCTURED                                                     
,t.MANDATOR_ID                                                       
,t.MINFRACUNITAMT                                                   
,t.MINFRACUNITAMTSC                                                 
,t.PRICINGGROUPTYPE                                                 
,t.PROCNTRVALFROMDATE                                               
,t.RISKCCY                                                           
,t.RISKLOCATION                                                     
,t.SPECDIVPAYMENTTYPE                                               
,t.TARGETDEPOSITARY                                                 
,t.UBSSPECMIDTYPE                                                   
FROM (                                                               
SELECT 'TJAEAOINSTRDETAIL' concat '  ' as NameOfTable               
,INSTRUMENTID                                                       
,ABICHNATBANKTYPE                                                   
,CASE WHEN DENOMINATION IS NULL THEN '0'                             
      ELSE char(DENOMINATION) END AS DENOMINATION                   
,CASE WHEN DENOMINATIONSC IS NULL THEN '0'                           
      ELSE char(DENOMINATIONSC) END AS DENOMINATIONSC               
,HASPAYINSTRSEGA                                                     
,HOLDINAPPLTYPE                                                     
,ISAMSELIGIBLE                                                       
,ISAPPROVEDBYWM                                                     
,ISPRICINGGRPLKD                                                     
,ISSETTLEDAFTFUN                                                     
,ISSTRUCTURED                                                           00000237
,MANDATOR_ID                                                            00000238
,CASE WHEN MINFRACUNITAMT IS NULL THEN '0'                              00000239
      ELSE char(MINFRACUNITAMT) END AS MINFRACUNITAMT                   00000240
,CASE WHEN MINFRACUNITAMTSC IS NULL THEN '0'                            00000241
      ELSE char(MINFRACUNITAMTSC) END AS MINFRACUNITAMTSC               00000242
,PRICINGGROUPTYPE                                                       00000243
,PROCNTRVALFROMDATE                                                     00000244
,RISKCCY                                                                00000245
,RISKLOCATION                                                           00000246
,SPECDIVPAYMENTTYPE                                                     00000247
,TARGETDEPOSITARY                                                       00000248
,UBSSPECMIDTYPE                                                         00000249
FROM D1.TJAEAOINSTRDETAIL                                               00000250
     WHERE INSTRUMENTID IN (SELECT FIID FROM d1.TJAEFIIDRECON)          00000251
ORDER BY INSTRUMENTID ) AS t                                            00000252
;                                                                       00000253
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -199, ERROR:  ILLEGAL USE OF KEYWORD ORDER.  TOKEN ) UNION   
         EXCEPT WAS EXPECTED                                                   
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                               
DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR                   
DSNT416I SQLERRD    = 506 0  0  -1  3673  0 SQL DIAGNOSTIC INFORMATION         
DSNT416I SQLERRD    = X'000001FA'  X'00000000'  X'00000000'  X'FFFFFFFF'       
         X'00000E59'  X'00000000' SQL DIAGNOSTIC INFORMATION                   


Are you able to run at your shop..? similar kinda solution i found on internet as well.. but are not working in my shop..
here the db2 version is 8.10, i guess the above solution may work for db 9 onwards. if you are able to run at your shop.. let me know.
- thanks
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 686
Location: Earth

PostPosted: Tue May 04, 2010 9:03 pm    Post subject:
Reply with quote

I posted my solution because below SQL works for me-
Code:
CREATE VIEW TESTVIE1 AS                                                 
  (SELECT T.T1 FROM (SELECT CURRENT_DATE AS T1 FROM SYSIBM.SYSDUMMY1     
     ORDER BY 1 ) AS T )       
Back to top
View user's profile Send private message
tomehta

New User


Joined: 18 Aug 2008
Posts: 98
Location: India

PostPosted: Wed May 05, 2010 6:54 pm    Post subject:
Reply with quote

this is what i get...

Code:
---------------                  -----                                  00000200
 CREATE VIEW TESTVIE1 AS                                                00000201
  (SELECT T.T1 FROM (SELECT CURRENT_DATE AS T1 FROM SYSIBM.SYSDUMMY1    00000202
   ORDER BY 1 ) AS T )                                                  00000203
;;;;                                                                    00000204
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -199, ERROR:  ILLEGAL USE OF KEYWORD ORDER.  TOKEN ) UNION   
         EXCEPT WAS EXPECTED                                                   
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                               
DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR                   
DSNT416I SQLERRD    = 506 0  0  -1  148  0 SQL DIAGNOSTIC INFORMATION           
DSNT416I SQLERRD    = X'000001FA'  X'00000000'  X'00000000'  X'FFFFFFFF'       
         X'00000094'  X'00000000' SQL DIAGNOSTIC INFORMATION                   


DB2 version
Code:
DSN7100I  +DD01 DSN7GCMD                                                   
*** BEGIN DISPLAY OF GROUP(DD0     ) GROUP LEVEL(810) MODE(N)             
                  PROTOCOL LEVEL(2)  GROUP ATTACH NAME(DD  )               
--------------------------------------------------------------------       
DB2                                    DB2 SYSTEM    IRLM                 
MEMBER   ID  SUBSYS CMDPREF   STATUS   LVL NAME      SUBSYS IRLMPROC       
-------- --- ----   --------  -------- --- --------  ----   --------       
DD01       1 DD01   +DD01     ACTIVE   810 TD01      DR01   DD01IRLM       
DD02       2 DD02   +DD02     ACTIVE   810 TD02      DR02   DD02IRLM       
--------------------------------------------------------------------       

can you check version you got..
thanks
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Thu May 06, 2010 12:49 pm    Post subject:
Reply with quote

order by in a subselect is only allowed since V9 NFM
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 Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts Help Needed with View Direct shailesh_do CA Products 2 Wed Jul 13, 2016 10:39 am
No new posts how to find clist's or view library Andrew Hsia CLIST & REXX 4 Tue Jun 28, 2016 6:07 pm
No new posts Report view in Mobile devices Robert Sample All Other Mainframe Topics 0 Thu Jun 23, 2016 7:05 pm
No new posts COBOL Report view in Mobile devices balaji81_k COBOL Programming 4 Thu Jun 23, 2016 6:41 pm


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