Page 1 of 1

DB2 VIEW ERROR

Posted: Mon Nov 02, 2020 8:24 am
by Theju112
I have a DB2 SQL statement which selects the required rows successfully. Since its a time consuming query, I am trying to convert it into a view using the create view statement.

Code: Select all

WITH firstequiprrn 
     AS (SELECT oeord#      equiporder, 
                Min(RRN(a)) firstrow 
         FROM   iesqafile.OPEQUIP a 
         GROUP  BY oeord# 
         ORDER  BY 1), 
         
     firstequiprow 
     AS (SELECT oeord#, 
                oetrlr equipmentnumber 
         FROM   firstequiprrn 
                INNER JOIN iesqafile.OPEQUIP b 
                        ON equiporder = oeord# 
                           AND firstrow = RRN(b)), 
                           
     ordermiles 
     AS (SELECT mmord#, 
                mmtotal 
         FROM   iesqafile.mmiles 
         WHERE  mmord# IN(SELECT orodr# 
                          FROM   iesqafile.order) 
                AND mmrectype = 'O' 
                AND mmdsp# = '00'), 

     stopgroup 
     AS (SELECT soord       stoporder, 
                COUNT(*)    stopsremain, 
                Min(sostp#) nextstop, 
                Max(soappr) apptreq, 
                Max(soaptm) apptmade 
         FROM   iesqafile.stopoff 
                INNER JOIN iesqafile.order 
                        ON orodr# = soord 
         WHERE  soardt = 0 
         GROUP  BY soord 
         ORDER  BY 1) SELECT a.orodr# orodr_, 
       orcust, 
       orldat, 
       CASE orpdat 
         WHEN 0 THEN Varchar(0) 
         ELSE Varchar_format(CHAR(orpdat), 'YYYY-MM-DD') 
       END      erdat2, 
       CASE orptim 
         WHEN 0 THEN Varchar(0) 
         ELSE CONCAT(CONCAT(SUBSTRING(orptim, 1, 2), ':'), 
              SUBSTRING(orptim, 3, 4)) 
       END      AS ertim2, 
       CASE orapdt 
         WHEN 0 THEN Varchar(0) 
         ELSE Varchar_format(CHAR(orapdt), 'YYYY-MM-DD') 
       END      ltdat2, 
       CASE oraptm 
         WHEN 0 THEN Varchar(0) 
         ELSE CONCAT(CONCAT(SUBSTRING(oraptm, 1, 2), ':'), 
              SUBSTRING(oraptm, 3, 4)) 
       END      AS lttim2, 
       CASE orddat 
         WHEN 0 THEN Varchar(0) 
         ELSE Varchar_format(CHAR(orddat), 'YYYY-MM-DD') 
       END      dldat2, 
       CASE ordtim 
         WHEN 0 THEN Varchar(0) 
         ELSE CONCAT(CONCAT(SUBSTRING(ordtim, 1, 2), ':'), 
              SUBSTRING(ordtim, 3, 4)) 
       END      AS dltim2, 
       orestr, 
       a.ordv#  ordv_, 
       orcons, 
       oreqty, 
       orspec, 
       a.orstp# orstp_, 
       orcomc, 
       ( CASE 
           WHEN mmtotal IS NOT NULL THEN mmtotal 
           ELSE 0 
         END )  mmtotal, 
       orwgt, 
       orocty, 
       orost, 
       ordcty, 
       ordst, 
       orara    asarea, 
       orpdrv   oprdrv, 
       orld#    orld_, 
       ordsp#   ordsp_, 
       orshdt, 
       orshtm, 
       ornwpk, 
       ( CASE 
           WHEN equipmentnumber IS NOT NULL THEN equipmentnumber 
           ELSE '' 
         END )  EquipmentNumber, 
       ( CASE 
           WHEN apptreq IS NOT NULL THEN apptreq 
           ELSE 'N' 
         END )  apptreq, 
       ( CASE 
           WHEN apptmade IS NOT NULL THEN apptmade 
           ELSE 'N' 
         END )  apptmade, 
       ( CASE 
           WHEN ununit IS NOT NULL THEN ununit 
           ELSE ' ' 
         END )  UNUNIT, 
       ( CASE 
           WHEN unsupr IS NOT NULL THEN unsupr 
           ELSE ' ' 
         END )  asdrmgr, 
       ( CASE 
           WHEN unfmgr IS NOT NULL THEN unfmgr 
           ELSE ' ' 
         END )  asflmgr, 
       ( CASE 
           WHEN untrl1 IS NOT NULL THEN untrl1 
           ELSE ' ' 
         END )  UNTRL1 
FROM   iesqafile.order a 
       LEFT OUTER JOIN ordermiles 
                    ON mmord# = a.orodr# 
       LEFT OUTER JOIN firstequiprow 
                    ON a.orodr# = oeord# 
       LEFT OUTER JOIN stopgroup 
                    ON a.orodr# = stoporder 
       LEFT OUTER JOIN iesqafile.units 
                    ON ununit = a.orpdrv 
WHERE  orld# <> ordsp# 
       AND ( orspec = 'N# C' 
              OR orspec = 'N# P' ) 
       AND orpdrv <> '' 
       AND orpdat >= 2018001 
UNION 
SELECT d.orodr# as ordddd, 
       orcust, 
       orldat, 
       CASE orpdat 
         WHEN 0 THEN Varchar(0) 
         ELSE Varchar_format(CHAR(orpdat), 'YYYY-MM-DD') 
       END     erdat2, 
       CASE orptim 
         WHEN 0 THEN Varchar(0) 
         ELSE CONCAT(CONCAT(SUBSTRING(orptim, 1, 2), ':'), 
              SUBSTRING(orptim, 3, 4)) 
       END     AS ertim2, 
       CASE orapdt 
         WHEN 0 THEN Varchar(0) 
         ELSE Varchar_format(CHAR(orapdt), 'YYYY-MM-DD') 
       END     ltdat2, 
       CASE oraptm 
         WHEN 0 THEN Varchar(0) 
         ELSE CONCAT(CONCAT(SUBSTRING(oraptm, 1, 2), ':'), 
              SUBSTRING(oraptm, 3, 4)) 
       END     AS lttim2, 
       CASE orddat 
         WHEN 0 THEN Varchar(0) 
         ELSE Varchar_format(CHAR(orddat), 'YYYY-MM-DD') 
       END     dldat2, 
       CASE ordtim 
         WHEN 0 THEN Varchar(0) 
         ELSE CONCAT(CONCAT(SUBSTRING(ordtim, 1, 2), ':'), 
              SUBSTRING(ordtim, 3, 4)) 
       END     AS dltim2, 
       orestr, 
       ordv#, 
       orcons, 
       oreqty, 
       orspec, 
       orstp#, 
       orcomc, 
       ( CASE 
           WHEN mmtotal IS NOT NULL THEN mmtotal 
           ELSE 0 
         END ) mmtotal, 
       orwgt, 
       orocty, 
       orost, 
       ordcty, 
       ordst, 
       orara, 
       orpdrv, 
       orld#, 
       ordsp#, 
       orshdt, 
       orshtm, 
       ornwpk, 
       ( CASE 
           WHEN equipmentnumber IS NOT NULL THEN equipmentnumber 
           ELSE ' ' 
         END ) EquipmentNumber, 
       ( CASE 
           WHEN apptreq IS NOT NULL THEN apptreq 
           ELSE 'N' 
         END ) apptreq, 
       ( CASE 
           WHEN apptmade IS NOT NULL THEN apptmade 
           ELSE 'N' 
         END ) apptmade, 
       ( CASE 
           WHEN ununit IS NOT NULL THEN ununit 
           ELSE ' ' 
         END ) UNUNIT, 
       ( CASE 
           WHEN unsupr IS NOT NULL THEN unsupr 
           ELSE ' ' 
         END ) UNSUPR, 
       ( CASE 
           WHEN unfmgr IS NOT NULL THEN unfmgr 
           ELSE ' ' 
         END ) UNFMGR, 
       ( CASE 
           WHEN untrl1 IS NOT NULL THEN untrl1 
           ELSE ' ' 
         END ) UNTRL1 
FROM   iesqafile.opplan 
       LEFT OUTER JOIN iesqafile.order d 
                    ON d.orodr# = opord# 
       LEFT OUTER JOIN ordermiles 
                    ON mmord# = d.orodr# 
       LEFT OUTER JOIN firstequiprow 
                    ON d.orodr# = oeord# 
       LEFT OUTER JOIN stopgroup 
                    ON d.orodr# = stoporder 
       LEFT OUTER JOIN iesqafile.units 
                    ON ununit = d.orpdrv
So my first attempt to create the view was simply to add a CREATE VIEW AS at the beginning of the first statement. This resulted in the error below:

A column list must be specified because the result columns are unnamed.
I am failing to understand this error as the column headings are indeed specified as the select works without issues. All columns are displayed with appropriate headings.

However I tried to modify the query by adding the below instead of the simple create view.

Code: Select all

create view pavt.v1 (v1,v2,v3,v4,v5,v6,v7,v8,v9,v10,v11,v12,v13,v14,v15,v16,v17,v18,v19,v20,v21,v22,v23,v24,v25,v26,v27,v28,v29,v30,v31,v32,v33,v34,v35,v36) as
This seemed to have worked and the view was created in library PAVT. However when I run a select on the view, I get an aritmetic overflow error with DB2 error code of -802.

I dont understand this as I am able to view the results but the issue comes only while attempting to create the query. Can somebody guide please?

A more detailed error description is below:

Code: Select all

Message . . . . :   Select or omit error on field                             
   Cast(Translate(ORDER_14.ORPTIM, *UNNAMED Table) AS member V.                
 Cause . . . . . :   A select or omit error occurred in record 0, record format
   *FIRST, member number 1 of file V in library PAVT, because of condition 1 of
   the following conditions:                                                   
     1 - The data was not valid in a decimal field.    
At this moment what I really want to know is if the method I am following to create the view is correct (atleast as far as the syntax goes). Maybe the error I am seeing is a data error?