ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQLRPGLE - Compile files saying fields not defined but they are in join file

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQLRPGLE - Compile files saying fields not defined but they are in join file

    Hi,

    SQL;
    Code:
    Exec SQL                                           
     DECLARE CC01 CURSOR FOR                           
     SELECT F1PONO                                     
       FROM FILE1 
     INNER JOIN FILE2 ON F1COMP = F2COMP            
                        AND F1DEPT = F2DEPT            
                        AND F1PONO = F2PONO            
     WHERE F1COMP = :COMP AND F1DEPT = :DEPT       
       AND F1STAT NOT IN('C', 'X') AND F2STYL = :STYL 
     GROUP BY F1PONO;
    Compiler errors;
    Code:
    *RNF7030 30   2879 065900  The name or indicator F2COMP is not defined.
    *RNF7030 30   2881 066100  The name or indicator F2DEPT is not defined.
    *RNF7030 30   2880 066000  The name or indicator F2PONO is not defined.
    There are no other errors in the compiler (above 0) just these 3.

    If I run the SQL in STRSQL, it works fine.

    FILE1 and FILE2 aren't defined in the F specs of my program as I thought they didn't need to be for SQL? Also, it wouldn't explain why FILE1 doesn't have any issues.

    Compiling with RPGPPOPT *LVL2 doesn't help either

    So I'm confused here as to what to try next? I've probably made a stupid mistake, which is why I tried the SQL manually initially.

    Any ideas?

    Cheers,
    Ryan

  • #2
    Both files are logicals, if that matters?

    Comment


    • RDKells
      RDKells commented
      Editing a comment
      Tried with physicals, same error.

  • #3
    Is the PF in your *LIBL?

    Comment


    • #4
      Are those column names, f2Comp, f2Dept, f2PoNo used as variables somewhere else in the program?

      Walt

      Comment


      • #5
        FILE1 and FILE2 are in the same library, along with their logicals and are in my library list.

        The only place fields from either of those files are used is that SQL statement.

        Baffling, no?

        Comment


        • wegrace
          wegrace commented
          Editing a comment
          Yes, it is.

      • #6
        If I swap the SQL around so it's selecting F2PONO from FILE2 , I get the same error - if that means anything.

        I've logged off, set my library list backup, done DSPFD FILE2 and it finds it;

        Code:
        Record format . . . . . . . . . . . . . . . :            FILE2R  
          Key field . . . . . . . . . . . . . . . . :            F2COMP   
            Sequence  . . . . . . . . . . . . . . . :            Ascending
            Sign specified  . . . . . . . . . . . . :            SIGNED   
            Zone/digit specified  . . . . . . . . . :            *NONE    
            Alternative collating sequence  . . . . :            No       
          Key field . . . . . . . . . . . . . . . . :            F2PONO   
            Sequence  . . . . . . . . . . . . . . . :            Ascending
            Sign specified  . . . . . . . . . . . . :            SIGNED   
            Zone/digit specified  . . . . . . . . . :            *NONE    
            Alternative collating sequence  . . . . :            No       
          Key field . . . . . . . . . . . . . . . . :            F2DEPT   
            Sequence  . . . . . . . . . . . . . . . :            Ascending
            Sign specified  . . . . . . . . . . . . :            UNSIGNED 
            Zone/digit specified  . . . . . . . . . :            *NONE
        Doing WRKOBJ FILE2 shows it's the only file in my library list with that name.

        Comment


        • #7
          *SQL naming or *SYS?

          Comment


          • #8
            TIL (again): Don't assume anything

            I was assuming that it was failing on my SQL but it was actually failing on a KLIST that happened to use the exact same fields as my SQL. As I removed FILE2 from the F specs in favour of the SQL, I broke the KLIST...What an amateur...

            Panic over everyone, stand down.

            Comment


            • wegrace
              wegrace commented
              Editing a comment
              LOL...that is why I asked about the fields in the rest of the program. 0-)

            • wegrace
              wegrace commented
              Editing a comment
              This one has bitten me a few times over the years, so I got in the habit of scanning the whole source code for the field in error.

            • RDKells
              RDKells commented
              Editing a comment
              Hahaha, a true face palm moment.

              Yep, from now on I'm checking the source statements in error.
          Working...
          X