ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL0012 - Correlation without Qualification

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

  • SQL0012 - Correlation without Qualification

    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:

    Code:
           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 where I'm getting the error warning is just to make sure I don't select any accounts that are in exception file FCLMAN.

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

    Thanks in advance.
    Last edited by Viking; December 22, 2020, 09:41 PM.
Working...
X