Home » SQL & PL/SQL » SQL & PL/SQL » How can I apply outer join in this query (8.1.7.0)
| How can I apply outer join in this query [message #381108] |
Thu, 15 January 2009 05:03  |
mamalik
Messages: 270
Registered: November 2008
Location: Pakistan
|
Senior Member |
|
|
Dear I have follwoing tables.
Apr_Str_01
Name Null? Type
------------------------------- -------- ----
UNT_COD NUMBER
DPT_COD NUMBER
DSG_COD NUMBER
STR_ROL NUMBER
STR_FRM_DTE DATE
STR_TOO_DTE DATE
LIN_NUM NUMBER
CAT_COD NUMBER
CR_ID NUMBER
CR_DT DATE
Dsg_00_01
Name Null? Type
------------------------------- -------- ----
UNT_COD NUMBER
DPT_COD NUMBER
DSG_COD NUMBER
I Write follwing Query
Select D.Unt_Cod,D.Dpt_Cod,D.Dsg_Cod,A.Str_Rol,A.Str_Frm_Dte
From Dsg_00_01 D,Apr_Str_01 A
Where A.Unt_Cod=D.Unt_Cod
And A.Dpt_Cod =D.Dpt_cod
And A.Dsg_Cod = D.Dsg_Cod
And Trunc(Sysdate) Between Str_Frm_Dte And Decode(Str_too_Dte,Null,Trunc(Sysdate),Str_Too_Dte);
Above query is not returning a designation 'Helper' because 'Helper' designation don't have approved strength in Apr_Str_01 Table.
I applied outer join on
A.Unt_Cod(+)=D.Unt_Cod
And A.Dpt_Cod(+) =D.Dpt_cod
And A.Dsg_Cod(+) = D.Dsg_Cod
But how can i include follwing in join
And Trunc(Sysdate) Between Str_Frm_Dte And Decode(Str_too_Dte,Null,Trunc(Sysdate),Str_Too_Dte);
Please Help.
Thanks In Advance.
[Updated on: Thu, 15 January 2009 05:52] Report message to a moderator |
|
|
|
|
|
| Re: How can I apply outer join in this query [message #381133 is a reply to message #381129] |
Thu, 15 January 2009 06:33  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Ok, we'll help but begging is not necessary. This is a free forum, none of us is paid to help you out. We do this when we have time for it and feel like it.
That said, let's have a look at your query. At first glance I'd replace the DECODE construction with a NVL.
I would probably do something like the code below. I've used my own tables (with clause) since you didn't provide sample data. But you can easily adapt it to your own requirements:
WITH table_1 AS
(SELECT 1 a_code, TO_DATE ('01/01/2009', 'dd/mm/yyyy') from_dt,
TO_DATE ('19/01/2009', 'dd/mm/yyyy') to_dt
FROM DUAL
UNION ALL
SELECT 2 a_code, TO_DATE ('03/01/2009', 'dd/mm/yyyy') from_dt,
TO_DATE ('04/01/2009', 'dd/mm/yyyy') to_dt
FROM DUAL
UNION ALL
SELECT 3 a_code, TO_DATE ('19/01/2009', 'dd/mm/yyyy') from_dt,
NULL to_dt
FROM DUAL
UNION ALL
SELECT 4 a_code, TO_DATE ('15/01/2009', 'dd/mm/yyyy') from_dt,
NULL to_dt
FROM DUAL
UNION ALL
SELECT 5 a_code, TO_DATE ('11/01/2009', 'dd/mm/yyyy') from_dt,
NULL to_dt
FROM DUAL),
table_2 AS
(SELECT 1 something, 1 a_code, 'Record 1' a_column
FROM DUAL
UNION ALL
SELECT 2 something, 5 a_code, 'Record 2' a_column
FROM DUAL
UNION ALL
SELECT 3 something, 4 a_code, 'Record 3' a_column
FROM DUAL
UNION ALL
SELECT 4 something, 2 a_code, 'Record 4' a_column
FROM DUAL
UNION ALL
SELECT 5 something, NULL a_code, 'Record 5' a_column
FROM DUAL
UNION ALL
SELECT 6 something, NULL a_code, 'Record 6' a_column
FROM DUAL
UNION ALL
SELECT 7 something, 3 a_code, 'Record 7' a_column
FROM DUAL)
SELECT t2.something, t1.a_code, t2.a_column, t1.from_dt, t1.to_dt
FROM table_2 t2, table_1 t1
WHERE t2.a_code = t1.a_code(+)
AND TRUNC (SYSDATE) BETWEEN t1.from_dt(+) AND NVL (t1.to_dt(+), TRUNC (SYSDATE))
/
Is that what you are looking for?
MHE
[Updated on: Thu, 15 January 2009 06:33] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Mon Jul 07 17:47:51 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
