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 Go to next message
mamalik
Messages: 266
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 #381129 is a reply to message #381108] Thu, 15 January 2009 06:16 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Please Help.
Re: How can I apply outer join in this query [message #381133 is a reply to message #381129] Thu, 15 January 2009 06:33 Go to previous messageGo to next message
Maaher
Messages: 7062
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

Re: How can I apply outer join in this query [message #381135 is a reply to message #381108] Thu, 15 January 2009 06:48 Go to previous message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear

I have no words to thank you. You have solved my big problem.

Thanks a lot.

You may live a long.

Best Regards
Muhammad Asif.
Previous Topic: merge issue
Next Topic: DBMS_RLS - Reset/Invalidate the static policy predicate
Goto Forum:
  


Current Time: Thu Dec 08 12:34:53 CST 2016

Total time taken to generate the page: 0.17949 seconds