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
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.
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
