× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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
 




As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.