Re: Sorting by Non-DataBase Fields

[Michael Ness <Michael_Ness@SBEXPOS.COM>]

One thing that SRajago774 @ AOL.COM and nathant @ ONTKO.COM left out is that
the subquery/join to ap_invoices may not have corresponding rows. The join to
ap_invoices should be an OUTER-JOIN, and then everything should work as you
want:

     Procedure-Main
     Begin-Select
     A.Name
     B.Payment_Method
     B.Vendor_ID
     decode(nvl(max(E.Invoice_id), 0), '0', 'N', 'Y') &pay_flag
      Do Check-For-Payments
      Print $pay_flag
     From per_people_f A,  po_vendors   B, ap_invoices D
     Where...
     and D.vendor_id (+) = B.Vendor_id
     group by A.Name, B.Payment_Method, B.Vendor_ID
     Order By B.Payment_Method, decode(nvl(max(E.Invoice_id), 0), '0', 'N',
'Y'), A.Name
     End-Select
     End-Procedure



To: SQR-USERS @ USA.NET (Multiple recipients of list SQR-USERS) @ INTERNET
cc:
From: cleghorn.jay @ BSC.BLS.COM (Jay Cleghorn) @ INTERNET
Date: 03/11/97 06:33:00 PM
Subject: Sorting by Non-DataBase Fields

     I've got a question that I can't seem to find an answer to in any
     book.  Hopefully someone here has run across something like this or
     will at least have some suggestions for me.

     I'm writing a report that shows the number of vendors that we have on
     direct deposit and whether they've ever had a payment issued to them.

     Here's what I'd like it to look like:

     Name               Payment Method          Payments Made or Not
     ABC Comp           Check                   N
     Zebras'R'Us        Check                   N
     Boy Corp           Check                   Y
     Live Group         Check                   Y
     City Council       EFT                     N
     Dirt Cheap         EFT                     Y

     Anyway, you get the idea.  I'd like to sort this by payment method,
     then by whether payments have been made and then the vendor name.

     The problem is that the Payments Made or Not is not a database column.
      I'm setting that value based on the following decode statement:
     decode(nvl(max(E.invoice_id), 0), '0', 'N', 'Y').

     Is there a way to include the 'Payments Made or Not' in the order by?

     Here's the way the program currently flows:

     Procedure-Main
     Begin-Select
     A.Name
     B.Payment_Method
     B.Vendor_ID
      Do Check-For-Payments
      Print $pay_flag
     From per_people_f A,
          po_vendors   B
     Where...
     Order By B.Payment_Method, $pay_flag, A.Name
     End-Select
     End-Procedure

     Begin-Procedure Check-For-Payments
     Begin-Select
     decode(nvl(max(E.Invoice_id), 0), '0', 'N', 'Y') &pay_flag
      let $pay_flag = &pay_flag
     from ap_invoices D
     where D.Vendor_id = &B.Vendor_id
     End-Select
     End-Procedure

     The problem is that the report isn't ordering by $pay_flag.  I'm not
     getting an error.  Is it possible to include this in the sort?

     Thanks in advance to anyone who can help!

     -Jay
     cleghorn.jay@bsc.bls.com
     jcleghorn@aol.com