CODEWITHSUNDEEP
ibm-midrangecrystal-reports-xidb2-400
user2734217
user2734217

Reputation: 121

this view on IBM i has an error

Initially, I am getting an error " Column qualifier or table OEINDLID undefined. "

what i have done here, is take the SQL created by the Crystal Report and attempt to make a view out of it...

     CREATE VIEW astccdta.acsusage                                       
AS                                                                  
SELECT ICPRTMIA.IARCC9, OEINDLID.IDDOCD, OEINDLID.IDCOM#,           
ADRESSAD.ADSFX#, OEINDLID.IDPRT#, OEINDLID.IDGRC#, OEINDLID.IDENT#, 
OEINDLID.IDSFX#, OEINDLID.IDPRLC, OEINDLID.IDNTU$, OEINDLID.IDSHP#, 
ADRESSAD.ADFNM, ADRESSAD.ADLNM, OEINHDIH.IHVIAC, ADRESSAD.ADSTTC,   
OEINDLID.IDINV#, (                                                  
SELECT                                                              
    SUM (T01.IDNTU$)                                                
FROM                                                                
    ASTDTA.OEINDLID T01                                             
WHERE                                                               
    T01.IDCOM# = '001' AND                                          
    T01.IDDOCD = OEINDLID.IDDOCD AND                                
    T01.IDINV# = OEINDLID.IDINV# AND                                
    T01.IDPRLC LIKE 'F%'                                            

)
 FROM   (ASTDTA.OEINDLID                                      
INNER JOIN ASTDTA.ICPRTMIA ON OEINDLID.IDPRT#=ICPRTMIA.IAPRT#)
INNER JOIN (ASTDTA.ADRESSAD                                   
INNER JOIN ASTTRN.OEINHDIH ON ADRESSAD.ADENT#=OEINHDIH.IHENT#)
ON OEINDLID.IDIDC#=OEINHDIH.IHIDC#                            
 WHERE  ICPRTMIA.IARCC9='ACS'                                 
OEINDLID.IDCOM#='001'                                         
AND OEINDLID.IDPRT# LIKE 'ACS%'

Upvotes: 0

Views: 54

Answers (2)

WarrenT
WarrenT

Reputation: 4532

There were a bunch of syntax errors, but try this

CREATE or REPLACE VIEW astccdta.acsusage  AS                                     
  SELECT M.IARCC9
       , D.IDDOCD, D.IDCOM#           
       , A.ADSFX#
       , D.IDPRT#, D.IDGRC#, D.IDENT# 
       , D.IDSFX#, D.IDPRLC, D.IDNTU$, D.IDSHP# 
       , A.ADFNM, A.ADLNM
       , H.IHVIAC
       , A.ADSTTC   
       , OEINDLID.IDINV#
       ,(SELECT SUM (T.IDNTU$)                                                
           FROM ASTDTA.OEINDLID AS T                                             
           WHERE T.IDCOM# = '001'                                           
             AND T.IDDOCD = D.IDDOCD
             AND T.IDINV# = D.IDINV#                                 
             AND T.IDPRLC LIKE 'F%'                                            
        )
    FROM ASTDTA.OEINDLID AS D                                     
    JOIN ASTDTA.ICPRTMIA AS M   ON D.IDPRT# = M.IAPRT#
    JOIN ASTTRN.OEINHDIH AS H   ON D.IDIDC# = H.IHIDC#
    JOIN ASTDTA.ADRESSAD AS A   ON A.ADENT# = H.IHENT#
    WHERE  M.IARCC9 = 'ACS'                                 
      AND  D.IDCOM# = '001'                                         
      AND  D.IDPRT# LIKE 'ACS%'

Notice that I took out the parenthesis on the joins. Let the optimizer do it's job.

Upvotes: 1

Buck Calabro
Buck Calabro

Reputation: 7648

You see how Crystal created the T01 correlation name here:

SELECT                                                              
    SUM (T01.IDNTU$)                                                
FROM                                                                
    ASTDTA.OEINDLID T01

The code you added below doesn't use the correlation name:

 FROM   (ASTDTA.OEINDLID                                      
INNER JOIN ASTDTA.ICPRTMIA ON OEINDLID.IDPRT#=ICPRTMIA.IAPRT#)

It has OEINDLID.IDPRT#. Add a correlation name like T01 and use that instead of OEINDLID in all of the places you reference OEINDLID.some_column_name and see if that helps.

Upvotes: 1

Related Questions