SQL0012 Warning - Correlation without Qualification

[Viking]

I'm hoping one of the SQL experts here can help me with something. I have an SQL statement defining a cursor in my RPG program. The SQL statement by itself seems to run fine, but I do see a warning. And when I compile the program, I get the warning SQL0012 "SQL0012: Position 61 Correlation without qualification occurred for column ZB$GNR to table MYACCOUNTS." I did some reading and it seems that I need to qualify something in my subselect at the very end of the statement, but I'm not quite clear on how to do what it wants. Here it is:

       Exec Sql
         declare accounts cursor for
           select ZB$GNR,
                  ZB$STR,
                  ZB$ASC,
                  ZB$CRP,
                  ZB$BCY,
                  ZB$AHB/100,
                  ZB$CCL/100,
                  case
                    when ZB$AHB/100 * 1.2 > 500 then
                       floor((ZB$AHB/100*1.2 + 99)/100)*100
                    else 500
                  end as NewLimit
             from myAccounts
             where ZB$BCY not in ('05','**')
               and ZB$CRP = '21 '
               and ZB$ASC not in ('X','L','N')
               and ZB$AHB > 0
               and 'CRT' not in (ZB$CA1,ZB$CA2,ZB$CA3,ZB$CA4,ZB$CA5,
                                 ZB$CA6,ZB$CA7,ZB$CA8,ZB$CA9)
               and 'BNK' not in (ZB$CA1,ZB$CA2,ZB$CA3,ZB$CA4,ZB$CA5,
                                 ZB$CA6,ZB$CA7,ZB$CA8,ZB$CA9)
               and 'B07' not in (ZB$CA1,ZB$CA2,ZB$CA3,ZB$CA4,ZB$CA5,
                                 ZB$CA6,ZB$CA7,ZB$CA8,ZB$CA9)
               and 'B13' not in (ZB$CA1,ZB$CA2,ZB$CA3,ZB$CA4,ZB$CA5,
                                 ZB$CA6,ZB$CA7,ZB$CA8,ZB$CA9)
               and (Current_Date <= retdate(
                   case when zb$dlc = 1 then 20 else 19 end
                   *1000000 + zb$dly*10000 + zb$dlm*100 + zb$dld) + 7 years
                    or Current_Date <= retdate(
                   case when zb$plc = 1 then 20 else 19 end
                   *1000000 + zb$ply*10000 + zb$plm*100 + zb$pld) + 7 years)
               and ZB$GNR not in (
                   select CLACCT from FCLMAN where CLACCT = ZB$GNR);

That very last piece is just to make sure I don't select any accounts that are in exception file FCLMAN, and that's where I'm getting the error.

Anyway, I'm hoping somebody here will know what to do.

Thanks in advance.

[Scott Klement]

If I understand correctly, its warning you that if there is a ZB$GNR column (field) in FCLMN (or if you ever added one) it wouldn't know that you meant the one in FCLMAN vs the one in myAccounts. Perhaps you know this will never happen? But, the computer does not.

If you wanted to eliminate the warning, you could do something like this:

   and ZB$GNR not      select f.CLACCT from FCLMAN as f where f.CLACCT = myAccounts.ZB$GNR);

That 'as f' (the word 'as' can be omitted if you like, though I sort of like it) is a correlation -- a shortcut for the the table name. So when fields are prefixed by 'f' it means they are in that table. You can also correlate using the whole table name as in 'myAccounts.ZB$GNR'. If you wanted to, you could change the larger statement to say "from myAccounts as A" or since 'as' can be omitted "from myAccounts A". Then use "A" as a correlation for all of the 'zb' fields throughout, which would maybe be a bit clearer.

[Vectorspace]

You could also code it as a left exception join, which (I suspect) would be faster anyway assuming the correct keys exist.

       Exec Sql
         declare accounts cursor for
           select ZB$GNR,
                  ZB$STR,
                  ZB$ASC,
                  ZB$CRP,
                  ZB$BCY,
                  ZB$AHB/100,
                  ZB$CCL/100,
                  case
                    when ZB$AHB/100 * 1.2 > 500 then
                       floor((ZB$AHB/100*1.2 + 99)/100)*100
                    else 500
                  end as NewLimit
             from myAccounts
             left exception join FCLMAN
               on CLACCT = ZB$GNR
             where ZB$BCY not in ('05','**')
               and ZB$CRP = '21 '
               and ZB$ASC not in ('X','L','N')
               and ZB$AHB > 0
               and 'CRT' not in (ZB$CA1,ZB$CA2,ZB$CA3,ZB$CA4,ZB$CA5,
                                 ZB$CA6,ZB$CA7,ZB$CA8,ZB$CA9)
               and 'BNK' not in (ZB$CA1,ZB$CA2,ZB$CA3,ZB$CA4,ZB$CA5,
                                 ZB$CA6,ZB$CA7,ZB$CA8,ZB$CA9)
               and 'B07' not in (ZB$CA1,ZB$CA2,ZB$CA3,ZB$CA4,ZB$CA5,
                                 ZB$CA6,ZB$CA7,ZB$CA8,ZB$CA9)
               and 'B13' not in (ZB$CA1,ZB$CA2,ZB$CA3,ZB$CA4,ZB$CA5,
                                 ZB$CA6,ZB$CA7,ZB$CA8,ZB$CA9)
               and (Current_Date <= retdate(
                   case when zb$dlc = 1 then 20 else 19 end
                   *1000000 + zb$dly*10000 + zb$dlm*100 + zb$dld) + 7 years
                    or Current_Date <= retdate(
                   case when zb$plc = 1 then 20 else 19 end
                   *1000000 + zb$ply*10000 + zb$plm*100 + zb$pld) + 7 years);

[Viking]

Thanks for the responses. I like the left exception join suggestion as it seems very clear and easy to me, and it solved the issue. The program compiled with no warnings/errors. Thanks!