Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join on BETWEEN command?
Outer Join on BETWEEN command? [message #9475] |
Thu, 13 November 2003 11:03 |
Dave Manning
Messages: 6 Registered: October 2003
|
Junior Member |
|
|
select pid.inv_id,
pci.chargeable_item,
from pv_invoice_details pid,
pv_chargeable_items pci,
where pid.charge_item_id = pci.charge_item_id(+)
and pci.eff_date
between pid.sett_date and
nvl(pid.to_Date,'31-DEC-2099')
In the above query, I want to retreive all records from the table pid, even if they don't join to the table pci. The first outer join works OK, but how do I get the query to return rows from pid if there is no match on the second condition?
|
|
|
Re: Outer Join on BETWEEN command? [message #9477 is a reply to message #9475] |
Thu, 13 November 2003 12:39 |
sverch
Messages: 582 Registered: December 2000
|
Senior Member |
|
|
I did not run this statement, but you can try:
SELECT pid.inv_id,pci.chargeable_item
FROM (SELECT inv_id,charge_item_id FROM pv_invoice_details) pid,
(SELECT pci.chargeable_item, pci.charge_item_id
FROM pv_chargeable_items pci,pv_invoice_details pid
WHERE pid.charge_item_id = pci.charge_item_id
AND pci.eff_date BETWEEN pid.sett_date AND
NVL(pid.to_Date,'31-DEC-2099') ) pci
WHERE pid.charge_item_id = pci.charge_item_id(+)
|
|
|
Re: Outer Join on BETWEEN command? [message #9481 is a reply to message #9475] |
Fri, 14 November 2003 06:50 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
Like this?SQL> CREATE TABLE pv_invoice_details (
2 inv_id NUMBER
3 , charge_item_id NUMBER
4 , sett_date DATE
5 , to_date DATE
6 );
Table created.
SQL> INSERT INTO pv_invoice_details VALUES (1,101,TO_DATE('20030101','YYYYMMDD'),TO_DATE('20031231','YYYYMMDD'));
SQL> INSERT INTO pv_invoice_details VALUES (2,102,TO_DATE('20000904','YYYYMMDD'),TO_DATE(NULL));
SQL> INSERT INTO pv_invoice_details VALUES (3,103,TO_DATE('20020228','YYYYMMDD'),TO_DATE(NULL));
SQL> INSERT INTO pv_invoice_details VALUES (4,104,TO_DATE('20021001','YYYYMMDD'),TO_DATE('20040101','YYYYMMDD'));
SQL> CREATE TABLE pv_chargeable_items (
2 charge_item_id NUMBER
3 , chargeable_item VARCHAR2(1)
4 , eff_date DATE
5 );
Table created.
SQL> INSERT INTO pv_chargeable_items VALUES (101,'Y',TO_DATE('20030630','YYYYMMDD'));
SQL> INSERT INTO pv_chargeable_items VALUES (103,'N',TO_DATE('19990731','YYYYMMDD'));
SQL> INSERT INTO pv_chargeable_items VALUES (104,'N',TO_DATE('20030815','YYYYMMDD'));
SQL> COMMIT;
Commit complete.
SQL> SELECT pid.inv_id
2 , pci.chargeable_item
3 FROM pv_invoice_details pid
4 , pv_chargeable_items pci
5 WHERE pid.charge_item_id = pci.charge_item_id (+)
6 AND pci.eff_date <font color=red><b>(+)</b></font> BETWEEN pid.sett_date
7 AND NVL(pid.to_date
8 , TO_DATE('20991231'
9 , 'YYYYMMDD'))
10 /
INV_ID C
---------- -
1 Y
2
3
4 N
SQL> A.
|
|
|
|
Goto Forum:
Current Time: Wed Apr 17 20:48:05 CDT 2024
|