pak m4z3,
tampaknya pernyataan problem dan result set yg diinginkan 'kurang' jelas
bagi saya.
shg saya menduga MUNGKIN yang pak m4z3 maksudkan adalah begini:....
Asumsi saya:
- ORDER_NO dan REL_NO (mungkin SEQUENCE juga) mengidentifikasi satu PROCESS
yang harus End-to-end.
- Payment atas purchase diindikasikan LUNAS jika ada 2 row di View.
- ORDER_NO, REL_NO, DIRECTION menunjukkan masuknya barang (+) dan adanya
pembayaran (-).
shg jika hanya ada 1 row yg berisi ORDER_NO dan REL_NO dipastikan ROW
ini OUTSTANDING.
- Payment dilaksanakan per ORDER_NO dan langsung LunaS atau QTY ORDERED (+)
= QTY PAYED(-)
- Indikasi LUNAS (Bukan Outstanding) terdeteksi jika ada 2 row
yang memiliki ORDER_NO dan REL_NO yang sama. Jika row ini memiliki tgl
tranx utk
BULAN YG DIMINTA maka row ini DITAMPILKAN, sebaliknya row ini TIDAK
DITAMPILKAN (sbg result set).
- Indikasi OUTSTANDING = jika hanya ada 1 row yang berisi ORDER_NO dan
REL_NO .
drop table tmp purge;
create table tmp (dated date, TRAN_CODE varchar2(10),
ORDER_NO NUMBER, REL_NO NUMBER);
ALTER SESSION SET NLS_DATE_FORMAT ='dd/mm/rr';
insert into tmp VALUES ('05-01-05', 'ARR-NONINV', 10256, 1);
insert into tmp VALUES ('28/02/05' ,'SUPPPAY', 10256, 1);
insert into tmp VALUES ('05/01/05','ARR-NONINV', 10256, 2);
insert into tmp VALUES ('28/02/05','SUPPPAY' , 10256, 2);
insert into tmp VALUES ('15/07/05', 'ARR-NONINV', 11213, 1);
insert into tmp VALUES ('30/07/05', 'SUPPPAY', 11213, 1);
insert into tmp VALUES ('30/07/05', 'ARR-NONINV', 21551, 1);
insert into tmp VALUES ('08/06/05', 'ARR-NONINV', 11111, 1);
Untuk bulan ini
SQL> SELECT * FROM tmp WHERE TO_CHAR(dated,'rrrrmm') = '200507';
DATED TRAN_CODE ORDER_NO REL_NO
-------- ---------- ---------- ----------
15/07/05 ARR-NONINV 11213 1
30/07/05 SUPPPAY 11213 1
30/07/05 ARR-NONINV 21551 1
Query ini menghasilkan result set semua yang outstanding, kecuali bulan-thn
yg diminta
utk bulan sampai dengan bulan-tahun ini, dapat dicoba yaa....
SQL> SELECT order_no, rel_no, COUNT (*) FROM tmp
2 WHERE TO_CHAR(dated,'rrrrmm') <> '200507'
3 GROUP BY (order_no, rel_no)
4 HAVING COUNT (*) =1;
ORDER_NO REL_NO COUNT(*)
---------- ---------- ----------
11111 1 1
Sekarang result set itu dapat di UNION, namun dibawah ini saya gunakan
fungsi analitik
SQL> SELECT *
2 FROM (
3 SELECT dated, order_no,rel_no,
4 COUNT(*) OVER (PARTITION BY order_no, rel_no) as chk
5 FROM tmp)
6 WHERE TO_CHAR(dated,'rrrrmm') = '200507' OR
7 (chk =1 AND TO_CHAR(dated,'rrrrmm') <> '200507');
DATED ORDER_NO REL_NO CHK
-------- ---------- ---------- ----------
08/06/05 11111 1 1
15/07/05 11213 1 2
30/07/05 11213 1 2
30/07/05 21551 1 1
ferry sends.
----- Original Message -----
From: "m4z3" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Wednesday, October 19, 2005 11:54 AM
Subject: Re: [indo-oracle] Re: Cari record outstanding
> Yulius Wibowo said the following on 19/10/05 8:04 AM:
> > SQL> SELECT * FROM view_union_name
> > WHERE TO_CHAR(dated,'mm/yyyy') = TO_CHAR(SYSDATE,'mm/yyyy');
> >
> > CMIIW
> >
> > Bowo
>
> Klo sintak SQL spt ini yg muncul hanya 'dated' dibulan yg
> bersangkutan, tetap saya yg outstanding dibulan sebelumnya
> tdk muncul.
>
> --
> m4z3
------------------------ Yahoo! Groups Sponsor --------------------~-->
Fair play? Video games influencing politics. Click and talk back!
http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/PhFolB/TM
--------------------------------------------------------------------~->
--
-----------I.N.D.O - O.R.A.C.L.E---------------
Keluar: [EMAIL PROTECTED]
Website: http://indo-oracle.lizt.org (NEW)
-----------------------------------------------
Bergabung dengan Indonesia Thin Client User Groups,
Terminal Server, Citrix, New Moon Caneveral, di:
http://indo-thin.vze.com
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/indo-oracle/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
