Home » SQL & PL/SQL » SQL & PL/SQL » Merged: help in joining two tables
Merged: help in joining two tables [message #301949] Fri, 22 February 2008 07:41 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi all,

First of Sorry for NOt writing the suitable Title.

Can any one help me in writing the query .
create table memo_vw (
PLCY_NBR varchar2(20),MEMO_DESC_CD varchar2(4),
memo_txt varchar2(50),eff_dt date,prsncode varchar2(8))

PLCY_NBR	MEMO_DESC_CD	MEMO_TXT	                             EFF_DT	PRSNCODE
AAA4500144	KY00	      CHOICE 2.1 144, BOBBIE   04 OCT 2007	 10/8/2007	PRIMARY
AAA4500144	CLR3	      CLIENT RECORD ADDED	                   10/08/2007	PRIMARY
AAA4500144	CLR3	      CLIENT RECORD ADDED	                   10/08/2007	
AAA4500144	ENT3	      ENTRY OF NEW POLICY TO SYSTEM	         10/08/2007	PRIMARY
AAA4500144	CDF1	      COUPLES DISC CRITERIA NOT MET	         08/08/2007	PRIMARY
AAA4500144	HIP2	      HIPAA FORM RECEIVED	                   10/08/2007	PRIMARY
AAA4500144	HIP2	      HIPAA FORM RECEIVED	                   10/08/2007	
AAA4500144	MISC	      SCREENING COMPLETE	                   10/08/2007	PRIMARY
AAA4500144	NOTE	      1,070.44 APPLIED	                     10/08/2007


CREATE TABLE PLCY_MSG_DESC_LKP
  ( MEMO_DESC_CODE  VARCHAR2(4),
    MEMO_CTG        VARCHAR2(200), 
    MEMO_TXT        VARCHAR2(50),
    ISSUE_DESC        VARCHAR2(50)
  );
insert into PLCY_MSG_DESC_LKP values ('KY00','MEDICAL UNDERWRITING',NULL,'CHOICE 2.1 144, BOBBIE');
insert into PLCY_MSG_DESC_LKP values ('CLR3','UNDERWRITING CATEGORY',NULL,'CHOICE 3.1 14, BOB');
insert into PLCY_MSG_DESC_LKP values ('HIP2','HIPAA FORM RECEIVED',NULL,'CHOICE 4.1 14, JON');

select * from PLCY_MSG_DESC_LKP;

MEMO_DESC_CODE	 MEMO_CTG	                 MEMO_TXT	        ISSUE_DESC
KY00	           MEDICAL UNDERWRITING		                      CHOICE 2.1 144, BOBBIE
CLR3	           UNDERWRITING CATEGORY		                    CHOICE 3.1 14, BOB
HIP2	           HIPAA FORM RECEIVED		                      CHOICE 4.1 14, JON
  
  
CREATE TABLE PLCY_MSG_STATUS_LKP
(   OPENG_CODE    VARCHAR2(4), 
    CLSNG_CODE   VARCHAR2(4)
 ); 

insert into    PLCY_MSG_STATUS_LKP values('CLR3','ENT3');
insert into    PLCY_MSG_STATUS_LKP values('CLR3','CDF1');
insert into    PLCY_MSG_STATUS_LKP values('CLR3','NOTE');
insert into    PLCY_MSG_STATUS_LKP values('HIP2','MISC');
SQL> select * from PLCY_MSG_STATUS_LKP;

OPEN CLSN
---- ----
CLR3 ENT3
CLR3 CDF1
HIP2 MISC
CLR3 NOTE


And I want ot write a query which returns the values from these 3 tables as
Req.out put:

OPENG_CODE  CLSNG_CODE  MEMO_CTG                      ISSUE_DESC            EFF_DT        PRSNCODE
CLR3        CDF1        UNDERWRITING CATEGORY         CHOICE 3.1 14, BOB    08/08/2007    PRIMARY
CLR3        NOTE        UNDERWRITING CATEGORY         CHOICE 3.1 14, BOB    10/08/2007
HIP2        MISC        HIPAA FORM RECEIVED           CHOICE 4.1 14, JON    10/08/2007	  PRIMARY

Explanation :
1) My requirement is that i want to find all the openg_codes,it's memo_ctg,Issue_desc from PLCY_MSG_DESC_LKP and from these
opening_codes find all the closing codes in PLCY_MSG_STATUS_LKP and for e.g., if an opening_code have multiple closing_code tags
then take there minimum date from the Main_table(memo_vw)based on the PRSNCODE..

[Updated on: Fri, 22 February 2008 08:13] by Moderator

Report message to a moderator

Re: help in writing the query [message #301978 is a reply to message #301949] Fri, 22 February 2008 10:21 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi all,

reframing the above:

PLCY_NBR	MEMO_DESC_CD	MEMO_TXT	                             EFF_DT	PRSNCODE
AAA4500144	KY00	      CHOICE 2.1 144, BOBBIE   04 OCT 2007	 10/8/2007	PRIMARY
AAA4500144	CLR3	      CLIENT RECORD ADDED	                   10/08/2007	PRIMARY
AAA4500144	CLR3	      CLIENT RECORD ADDED	                   10/08/2007	
AAA4500144	ENT3	      ENTRY OF NEW POLICY TO SYSTEM	         10/08/2007	PRIMARY
AAA4500144	CDF1	      COUPLES DISC CRITERIA NOT MET	         08/08/2007	PRIMARY
AAA4500144	HIP2	      HIPAA FORM RECEIVED	                   10/08/2007	PRIMARY
AAA4500144	HIP2	      HIPAA FORM RECEIVED	                   10/08/2007	
AAA4500144	MISC	      SCREENING COMPLETE	                   9/08/2007	PRIMARY
AAA4500144	NOTE	      1,070.44 APPLIED	                     10/08/2007




select * from PLCY_MSG_STATUS_LKP;
Table:2
MEMO_DESC_CD CLSN
----          ----
CLR3          ENT3
CLR3          CDF1
HIP2          MISC
CLR3          NOTE



TABLE1: Contains all the policy_message related information

Table:2 contains OPENG_CODE and it's closing CLSNG_CODE.One opening_code may contains multiple closing_codes.

Now i want to find from table1 PLCY_NBR,MEMO_DESC_CD,PRSNCODE,EFF_DT,CLSONG_DT  all the rows for which 
there is an OPENIG_CODE exist in the TABLE2.
In the above : EFF_DT is the date for the opening_code which is derived from the table1
Condition for CLSONG_DT is :In case of Table2(PLCY_MSG_STATUS_LKP) two contains multiple closing_codes then 
                            find the minium effective date from the TABLE1 based on the PRSNCODE,MEMO_DESC_CD and use it.

All the above information can be shown as:
PLCY_NBR    MEMO_DESC_CD  EFF_DT    PRSNCODE CLSONG_DT   CLSNG_CD
AAA4500144	CLR3	 10/08/2007 PRIMARY  08/08/2007  CDF1
AAA4500144	CLR3	 10/08/2007          10/08/2007  CDF1
AAA4500144	HIP2     10/10/2007 PRIMARY  09/08/2007  MISL  


Appreciate for any help
problem with Joining of two tables [message #302372 is a reply to message #301949] Mon, 25 February 2008 07:00 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
hi all,
Any help in the below requirements is higly appreciable.

Table1 contains:

PLCY_NBR	MEMO_DESC_CD	MEMO_TXT	                             EFF_DT	PRSNCODE
AAA4500144	KY00	      CHOICE 2.1 144, BOBBIE   04 OCT 2007	 10/8/2007	PRIMARY
AAA4500144	CLR3	      CLIENT RECORD ADDED	                   10/08/2007	PRIMARY
AAA4500144	CLR3	      CLIENT RECORD ADDED	                   10/08/2007	
AAA4500144	ENT3	      ENTRY OF NEW POLICY TO SYSTEM	         10/08/2007	PRIMARY
AAA4500144	CDF1	      COUPLES DISC CRITERIA NOT MET	         08/08/2007	PRIMARY
AAA4500144	HIP2	      HIPAA FORM RECEIVED	                   10/08/2007	PRIMARY
AAA4500144	HIP2	      HIPAA FORM RECEIVED	                   10/08/2007	
AAA4500144	MISC	      SCREENING COMPLETE	                   10/08/2007	PRIMARY
AAA4500144	NOTE	      1,070.44 APPLIED	                     10/08/2007


select * from PLCY_MSG_STATUS_LKP;

Table2: 

OPEN CLSN
---- ----
CLR3 ENT3
CLR3 CDF1
HIP2 MISC
CLR3 NOTE


table1: contains all the data related to the policy which includes all the opening code and closing codes(in the above column name:MEMO_DESC_CD).
table2 contains: opening and closing code.
Note: one opening code can contains multiple closing codes.
Now i want to find the minimum effective date,closing_code from table1 for which there is a opening code exist in the table2.
Required output is :

CDF1 08/08/2007
MISC 10/10/2007

Please Help.

[Updated on: Mon, 25 February 2008 07:03] by Moderator

Report message to a moderator

Re: problem with Joining of two tables [message #302375 is a reply to message #302372] Mon, 25 February 2008 07:10 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Post what you have tried, what you got, what you expected and why.
Re: problem with Joining of two tables [message #302383 is a reply to message #302372] Mon, 25 February 2008 07:27 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Thanks,

In the first step1:
I found all the opening codes for these table and inserted these values into global temporary table.
Step2: Opened a cursor which fetches opening_code fetches from these GTB table.
based on this opening code and PRSNCODE
I used to find my closing_code and min(effective_date) from the table1:

select min(eff_dt) from memo_vw
where memo_desc_cd in (select clsng_code from PLCY_MSG_STATUS_LKP where openg_code =&OPENING_CODE)
and prsncode = &PRSNCODE;


e.g.,
select min(eff_dt) from memo_vw
where memo_desc_cd in (select clsng_code from PLCY_MSG_STATUS_LKP where openg_code ='CLR3')
and prsncode = 'PRIMARY
'
OP: 08/08/20007

But I required this min(effective_date) and it's clsoing_code

Req: 08/08/20007 CDF1

[Updated on: Mon, 25 February 2008 07:38]

Report message to a moderator

Re: problem with Joining of two tables [message #302390 is a reply to message #302372] Mon, 25 February 2008 07:52 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Any updates/suggestions

Thanks,
Re: Merged: help in joining two tables [message #302403 is a reply to message #301949] Mon, 25 February 2008 08:42 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi All,

Any help or suggestions please.

Re: Merged: help in joining two tables [message #302406 is a reply to message #302403] Mon, 25 February 2008 08:57 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
If you need help that bad, I'd suggest you pay for support. No one here owes you anything. Asking for help once is enough. It's not going to get you help any faster by asking for people to answer the question when you haven't had an answer for a few hours. If anything, it will usually have the inverse effect as you will now be ignored.
Previous Topic: Changes in Password Verification Function (please help urgently)
Next Topic: wrm$ views
Goto Forum:
  


Current Time: Sat Dec 03 09:44:34 CST 2016

Total time taken to generate the page: 0.07466 seconds