select statement runs but if use create get error -104

[evr72]

Hello,

I am trying to create a view, if I run my sql statement it runs fine, no issues but if I add the create view I get a -104 error

my sql statement looks like this
not sure why it would not let me do a create view

SELECT

  B.CDAGQ7, A.C6AAYG, D.CJAKDT, D.CJAJDT, A.C6DCCD, A.C6IDST,  

D.CJAIDT, E.SLSNO, C6CVNB AS "ORDER", CJCBTX AS PO, A.C6CANB,

DEHITX AS CUSTUMER, CDAITX AS ITEM,                          

SUBSTR(VARCHAR(CDAITX),1,6) AS PARTNUM, B.CDHJTX, (CDACQT),  

B.CDKHVA, ((B.CDKHVA*CDACQT)), A.C6DBVA, I.ALBYCD, A.C6ALDT, 

A.C6ACDT, A.C6D0NB, SUBSTR(DIGITS(A.C6ACDT),4,2) AS ORDMO,   

SUBSTR(DIGITS(A.C6ACDT),6,2) AS ORDDAY,                      

SUBSTR(DIGITS(A.C6ACDT),2,2) AS ORDYR,                       

SUBSTR(DIGITS(A.C6ACDT),4,2) AS MANUFMO,                     

SUBSTR(DIGITS(A.C6ACDT),6,2) AS MANUFDAY,                    

SUBSTR(DIGITS(A.C6ACDT),2,2) AS MANUFYR,                     

SUBSTR(DIGITS(A.C6ALDT),4,2) AS CREATEMO,                    

SUBSTR(DIGITS(A.C6ALDT),6,2) AS CREATEDAY,                         

SUBSTR(DIGITS(A.C6ALDT),2,2) AS CREATEYR, C6ACDT AS "DATE", G.GYMD,

H.GYMD, G.GREG, H.GREG, SUBSTR(DIGITS(D.CJAKDT),6,2) AS MANUFORDDY,

SUBSTR(DIGITS(D.CJAKDT),4,2) AS MANUFORDMT,                        

SUBSTR(DIGITS(D.CJAKDT),2,2) AS MANUFORDYR, J.ADBIDT,              

((C6DCVA-C6DBVA)/C6DCVA)*100 AS MRGN                               

FROM AMFLIB2.MBC6REP A CROSS JOIN                                       

AMFLIB2.MBCDREP B CROSS JOIN                                       

AMFLIB2.MBDEREP C CROSS JOIN                                       

AMFLIB2.MBCJCPP D CROSS JOIN                                       

AMFLIB2.CUSMAS E CROSS JOIN                                        

mydbfile.SLSCOMM F CROSS JOIN                                       

mydbfile.CALENDAR G CROSS JOIN                                      

mydbfile.CALENDAR H CROSS JOIN                                      

AMFLIB2.MBALREP I CROSS JOIN                                       

AMFLIB2.MBADREP J                                                  

WHERE C6DCCD = CDDCCD    

AND C6CVNB = CDCVNB    

AND C6CANB = DECANB    

AND C6B9CD = DEB9CD    

AND C6DCCD = CJDCCD    

AND C6CVNB = CJCVNB    

AND A.C6CANB = E.CUSNO 

AND E.SLSNO = F.SLSNO  

AND A.C6ACDT = G.GYMD  

AND D.CJAKDT = H.GYMD  

AND C.DECANB = I.ALCANB

AND C.DECUCD = I.ALCUCD

AND CDDCCD = ADDCCD    

AND CDCVNB = ADCVNB    

AND CDAENB = ADAENB    

AND CDFCNB = ADFCNB    

AND CDAITX = ADAITX

AND (A.C6DCCD = '1' 

AND B.CDAGQ7 = 1)
ORDER BY 009 ASC

 

[fredericofonseca]

without the full error and the full create statement (and the select isn't enough) only thing I can suggest is to remove the order by

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[evr72]

hello Fredericofonseca,

the error I get is this

> create view daviesf/shpdy as SELECT      B.CDAGQ7, A.C6AAYG, D.CJAKDT, D.CJAJDT, A.C6DCCD, A.C6IDST,      D.CJAIDT, E.SLSNO, C6CVNB AS "ORDER", CJCBTX AS PO, A.C6CANB,    DEHITX AS CUSTUMER, CDAITX AS ITEM,                              SUBSTR(VARCHAR(CDAITX),1,6) AS PARTNUM, B.CDHJTX, (CDACQT),      B.CDKHVA, ((B.CDKHVA*CDACQT)), A.C6DBVA, I.ALBYCD, A.C6ALDT,     A.C6ACDT, A.C6D0NB, SUBSTR(DIGITS(A.C6ACDT),4,2) AS ORDMO,       SUBSTR(DIGITS(A.C6ACDT),6,2) AS ORDDAY,                          SUBSTR(DIGITS(A.C6ACDT),2,2) AS ORDYR,                           SUBSTR(DIGITS(A.C6ACDT),4,2) AS MANUFMO,                         SUBSTR(DIGITS(A.C6ACDT),6,2) AS MANUFDAY,                        SUBSTR(DIGITS(A.C6ACDT),2,2) AS MANUFYR,                         SUBSTR(DIGITS(A.C6ALDT),4,2) AS CREATEMO,                        SUBSTR(DIGITS(A.C6ALDT),6,2) AS CREATEDAY,                             SUBSTR(DIGITS(A.C6ALDT),2,2) AS CREATEYR, C6ACDT AS "DATE", G.GYMD,    H.GYMD, G.GREG, H.GREG, SUBSTR(DIGITS(D.CJAKDT),6,2) AS MANUFORDDY,    SUBSTR(DIGITS(D.CJAKDT),4,2) AS MANUFORDMT,                            SUBSTR(DIGITS(D.CJAKDT),2,2) AS MANUFORDYR, J.ADBIDT,                  ((C6DCVA-C6DBVA)/C6DCVA)*100 AS MRGN                                   FROM AMFLIB2.MBC6REP A CROSS JOIN                                           AMFLIB2.MBCDREP B CROSS JOIN                                           AMFLIB2.MBDEREP C CROSS JOIN                                           AMFLIB2.MBCJCPP D CROSS JOIN                                           AMFLIB2.CUSMAS E CROSS JOIN                                            daviesf.SLSCOMM F CROSS JOIN                                           daviesf.CALENDAR G CROSS JOIN                                          daviesf.CALENDAR H CROSS JOIN                                          AMFLIB2.MBALREP I CROSS JOIN                                           AMFLIB2.MBADREP J                                                      WHERE C6DCCD = CDDCCD        AND C6CVNB = CDCVNB        AND C6CANB = DECANB        AND C6B9CD = DEB9CD        AND C6DCCD = CJDCCD        AND C6CVNB = CJCVNB        AND A.C6CANB = E.CUSNO     AND E.SLSNO = F.SLSNO      AND A.C6ACDT = G.GYMD      AND D.CJAKDT = H.GYMD      AND C.DECANB = I.ALCANB    AND C.DECUCD = I.ALCUCD    AND CDDCCD = ADDCCD        AND CDCVNB = ADCVNB        AND CDAENB = ADAENB        AND CDFCNB = ADFCNB        AND CDAITX = ADAITX    AND (A.C6DCCD = '1'     AND B.CDAGQ7 = 1)  ORDER BY 009 ASC

SQL State: 42833
Vendor Code: -5016
Message: [SQL5016] Qualified object name SHPDY not valid. Cause . . . . . :   One of the following has occurred: -- The syntax used for the qualified object name is not valid for the naming option specified.  With system naming, the qualified form of an object name is schema-name/object-name or schema-name.object-name.  With SQL naming the qualified form of an object name is authorization-name.object-name. -- The syntax used for the qualified object name is not allowed. User-defined types, functions, variables, and sequences cannot be qualified with the schema name using the / separator in the system naming convention when used in a query. Recovery  . . . :   Do one of the following and try the request again: -- If you want to use the SQL naming convention, specify the SQL naming option and qualify the object names in the form authorization-id.object-name. -- If you want to use the system naming convention, specify the system naming option and qualify the object names in the form schema-name/object-name or schema-name.object-name. -- With the system naming convention, either make sure the user-defined types, functions, variables can be found in the current path or use the period form of naming to qualify the object.

Processing ended because the highlighted statement did not complete successfully

 

[fredericofonseca]

replace daviesf/shpdy with daviesf.shpdy


and views should not have order by on them so my previous advise still stands

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886