SQL error driving me crazy - solved!

All:

Never mind...If all else fails, read the manual...

I just needed to drop the "order by" clauses in my temporary tables.  Thanks
anyway,
--Bruce Guetzkow





All:

I'm getting an error message with an SQL statement and I'm hoping that
someone can spot my mistake.  This will be a QMQRY that will write out to an
*OUTFILE.

The error message that I'm getting is "Column PFDDAT or expression in SELECT
list not valid."  You'll find this field as part of the 2nd temporary table
being generated.

PFDDAT is a valid field (8/0 zoned) in file PAYDIST.  I'm sure I've got a
typo somewhere or put some keyword out of order, but I'm not seeing it.
After 4 hours of looking, it's time to bring in the Marines.  Any help that
anyone can offer is appreciated.  Although this isn't an RPG question, I've
seen other SQL questions here, so I'm hoping this post is appropriate.  If
not, please feel free to redirect me (David?).

Thanks in advance,
--Bruce Guetzkow

Here's the SQL statement:

 with

   tmp01 (client
        , listyr
        , lstamt
         ) as (
     select
            dbclnt
          , substr(digits(dbddat),1,4)
          , sum(dbbbal)
       from debtmast
      where dbclnt in (63554)
   group by dbclnt
          , substr(digits(dbddat),1,4)
   order by dbclnt
          , substr(digits(dbddat),1,4)
              ),


   tmp02 (client
        , listyr
        , colamt
        , comamt
         ) as (
     select
            pfclnt
          , substr(digits(pfddat),1,4)
          , sum(pfdamt)
          , sum(pfcomm)
       from paydist
      where pfclnt in (63554)
        and pfaply in (1, 2, 3)
   group by pfclnt
          , substr(digits(pfddat),1,4)
   order by pfclnt
          , substr(digits(pfddat),1,4)
              ),


   tmp03 (client
        , listyr
        , cnlamt
         ) as (
     select
            dbclnt
          , substr(digits(dbddat),1,4)
          , sum(dbcbal + dbintr + dbfees)
       from debtmast
       join debtstat
         on (dbcomp = dtcomp
         and dbdsta = dtdsta
         and dtcncl = 'Y')
      where dbclnt in (63554)
        and dbdsta <> '019'
   group by dbclnt
          , substr(digits(dbddat),1,4)
   order by dbclnt
          , substr(digits(dbddat),1,4)
              ),


   tmp04 (client
        , listyr
        , adjamt
         ) as (
     select
            ajclnt
          , substr(digits(ajddat),1,4)
          , sum(ajdamt)
       from adjdist
      where ajclnt in (63554)
       and  ajaply in (1, 2, 3)
   group by ajclnt
          , substr(digits(ajddat),1,4)
   order by ajclnt
          , substr(digits(ajddat),1,4)
              )

     select
            a.client
          , a.listyr
          , a.lstamt
          , d.adjamt
          , c.cnlamt
          , b.colamt
          , b.comamt
          , b.colamt - b.comamt
              as netback
          , ((b.colamt - b.comamt) / (a.lstamt + d.adjamt)) * 100
              as nbpct
          , (b.colamt / (a.lstamt + d.adjamt)) * 100
              as rcvpct
          , (b.comamt / b.colamt) * 100
              as compct
       from tmp01 a
  left join tmp02 b
         on (a.client = b.client
         and a.listyr = b.listyr)
  left join tmp03 c
         on (a.client = c.client
         and a.listyr = c.listyr)
  left join tmp04 d
         on (a.client = d.client
         and a.listyr = d.listyr)
   order by a.client
          , a.listyr