• Article Type: General
• Product: Aleph
• Product Version: 21

Desired Outcome Goal:
A report of monograph orders that have only one item and one invoice line.

Procedure:
Use the following SQL:

> s+ xxx50
SQL> select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z68_order_number, z68_rec_key from z68, z30, z601 where Z68_ORDER_TYPE = 'M' and rtrim (z68_order_number) = rtrim (Z30_ORDER_NUMBER)
and substr (z30_rec_key,1,9) not in (select substr (z30_rec_key,1,9) from z30 group by substr (z30_rec_key,1,9) having count(*) > 1 )
and substr (Z601_REC_KEY_3,1,9) = substr (z30_rec_key,1,9) and z601_type = 'INV' and substr (Z601_REC_KEY_3,1,9) not in (select substr (z601_rec_key_3,1,9) from z601 where z601_type = 'INV' group by substr (z601_rec_key_3,1,9) having count(*) > 1 )
order by z68_order_number asc;

Category: Acquisitions

Subject: Orders

• Article last edited: 2/12/2014