OraFAQ Forum: SQL & PL/SQL » Outer Join on Values

Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join on Values

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Outer Join on Values [message #624549]

Tue, 23 September 2014 08:04

Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra

Member

I have two tables as 1. Grade_MST 2. Transactions_MST
In first table (Grade_MST) column are:
-----------------------
grade_cd | description |
---------|-------------|
001 | A |
002 | B |
003 | C |
004 | D |
005 | E |
-------------------------
In Second table (Transactions_MST) column are:
--------------------------------------------------------
eff_dt | grade_cd |cr_type |RATE|Auth_by| Auth_dt
---------|-----------------------------------------
01-oct-14| 002 | C |100 | M | sysdate
01-oct-14| 002 | R |200 | M | sysdate
01-dec-14| 003 | C |300 | |
01-dec-14| 003 | R |400 | |
---------------------------------------------------------

When I enter date as : 01-oct-14 then o/p will be as follows:
-----------------------
grade_cd | description |
---------|-------------|
001 | A |
003 | C |
004 | D |
005 | E |
-------------------------

When I enter date as : 01-Dec-14 then o/p will be as follows:
-----------------------
grade_cd | description |
---------|-------------|
001 | A |
002 | B |
003 | C |
004 | D |
005 | E |
-------------------------

Please suggest me....

Report message to a moderator

Re: Outer Join on Values [message #624551 is a reply to message #624549]

Tue, 23 September 2014 08:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: Outer Join on Values [message #624552 is a reply to message #624549]

Tue, 23 September 2014 08:12

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

As Blackswan says, follow the guidelines. To be more specific at this point:
1. post a test case
2. Use code tags
3. explain the logic of your outputs.

Report message to a moderator

Re: Outer Join on Values [message #624556 is a reply to message #624549]

Tue, 23 September 2014 08:19

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: Outer Join on Values [message #624559 is a reply to message #624556]

Tue, 23 September 2014 08:27

Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra

Member

As mentioned above, there are two tables. I have to join them according to eff_dt, Auth_By, Auth_Dt and grade_cd.
1. If entered date is in Transactions_MST table and Auth_By, Auth_Dt is null then display all record except Auth_By, Auth_Dt is not null.

2. If entered date is not in Transactions_MST table and Auth_By, Auth_Dt is not null then display all record.

Report message to a moderator

Re: Outer Join on Values [message #624561 is a reply to message #624559]

Tue, 23 September 2014 08:33

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

You are asking for assistance.
Others have posted that you need to perform some tasks in order to be able to get that assistance.
You have, so far, failed to perform those tasks.
What do you think is the likely esult of this exchange?

What difficulty arre you having with following the guidelines, maybe we can help explain.
Do you understand what we mean by code tags?
Do you understand what we mean by a test case?

Report message to a moderator

Re: Outer Join on Values [message #624715 is a reply to message #624561]

Wed, 24 September 2014 23:50

Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra

Member

Dear all,
I apologies for my last post. And I resolved my problem with query.

Thank you.

Report message to a moderator

Re: Outer Join on Values [message #624722 is a reply to message #624715]

Thu, 25 September 2014 00:23

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So please post it.

Report message to a moderator

Re: Outer Join on Values [message #625370 is a reply to message #624722]

Mon, 06 October 2014 22:49

Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra

Member

 SELECT S.GRADE_CD,S.GRADE_DESC
	  FROM   STEEL_GRADE_MST S
	  WHERE  S.STATUS_FLAG='A'
	  AND    S.GRADE_CD NOT IN( 
				   SELECT DISTINCT T.GRADE_CD
				   FROM   TRANSACTIONS_MST T
				   WHERE  T.AUTH_BY IS NOT NULL 
				   AND    T.AUTH_DT IS NOT NULL
				   AND    T.EFF_DT = :BLOCK_PARAM.EFF_DT
				  )
ORDER BY S.GRADE_CD

Report message to a moderator

Previous Topic:	how can i secure some row from my table
Next Topic:	How to avoid OR condition in the below scenario, how we can write in different ways

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 18:09:28 CDT 2024