INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

select statement runs but if use create get error -104

select statement runs but if use create get error -104

select statement runs but if use create get error -104

(OP)
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

CODE

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 

RE: select statement runs but if use create get error -104

(OP)
hello Fredericofonseca,

the error I get is this

CODE

> 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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close