Home » SQL & PL/SQL » SQL & PL/SQL » Help with this SQL join
Help with this SQL join [message #280805] Wed, 14 November 2007 15:49 Go to next message
amitkr
Messages: 17
Registered: November 2007
Junior Member
Hi All

Will appreciate your time on this

I have 2 tables

create table tablea (col1 number, col2 varchar2(10),col3 varchar2(10),  col4 varchar2(10), col5 varchar2(10));

create table tableb (col11 number, col12 varchar2(10),col13 varchar2(10),  col14 varchar2(10));

insert into tablea values (1,'AK','XY','WE','QR');

insert into tableb values (1,'VALX','VALY', 'ZX');
insert into tableb values (1,'DSF','ERER', 'TRY');
insert into tableb values (1,'TRT','YTY', 'OUT');

TableA

COL1  COL2  COL3  COL4  COL5
1     AK    XY    WE    QR
 

TABLEB

COL11  COL12  COL13 COL14 
1      VALX   VALY  ZX     
1      DSF    ERER  TRY
1      TRT    YTY   OUT


I need the result in the following form:

COL1  COL  COL2  COL3  COL4  COL5  COL12  COL13  COL14 
1     1    AK    XY    WE    QR    VALX   VALY   ZX     
1     2                            DSF    ERER   TRY
1     3                            TRT    YTY    OUT


The tables are joined on COL1 & COL11.
The total no of rows returned will be the no. of rows satisfying the join criteria.
COL is a counter incrementing for COL1.

In a regular join COL2, COL3, COL4 & COL5 values of the 1st row will repeat in row 2 & 3 ... I don't want that.

This is a huge table and if I cant do it in sql then I will have to do it in pl/sql cursor loop (insert , update)

Many Thanks in advance
AK
Re: Help with this SQL join [message #280813 is a reply to message #280805] Wed, 14 November 2007 20:32 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Here's a similar question with an answer that you should be able to rework to your requirement.

Ross Leishman
Re: Help with this SQL join [message #280853 is a reply to message #280805] Thu, 15 November 2007 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you execute it with SQL*Plus, just use BREAK command.
If you execute it with your software, check previous values in it before displaying.

It is just a display issue, doing it in SQL is fine as an exercise but just a waste of resources in real world.

Regards
Michel
Re: Help with this SQL join [message #280856 is a reply to message #280853] Thu, 15 November 2007 00:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Ah yes, I may have mis-read the requirement. I thought the OP wanted to list the two tables side-by-side, not to join them and suppress break columns. Ignore my previous post in that case.

Ross Leishman
Re: Help with this SQL join [message #280863 is a reply to message #280853] Thu, 15 November 2007 01:10 Go to previous messageGo to next message
amitkr
Messages: 17
Registered: November 2007
Junior Member
Thanks Ross, Trying to understand the one you suggested.

BTW its not just to display.. I need to insert the output into a table.

Michael,

Currently this is happing inside a pl/sql nested loop (insert , then another insert and then an update)... And its very slow

Thanks Once again
AK
Re: Help with this SQL join [message #280865 is a reply to message #280863] Thu, 15 November 2007 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Why don't you want to repeat the values? And above all, why do you want to denormalize the model?

2/ If it is a PL/SQL performance, why don't you post the code? (just the relevant part not thousand of lines)

Regards
Michel
Re: Help with this SQL join [message #280977 is a reply to message #280865] Thu, 15 November 2007 05:21 Go to previous messageGo to next message
amitkr
Messages: 17
Registered: November 2007
Junior Member
Thanks

Denormalize coz..its going into a data warehouse.. and lots of reports will be running on this..

Don't want to repeat the values coz the existing code doesn't..

Regards
Amit
Re: Help with this SQL join [message #280985 is a reply to message #280977] Thu, 15 November 2007 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

the existing code doesn't

And how does it know the values for COL2, COL3... for a row? By searching the one with COL=1?

Regards
Michel
Re: Help with this SQL join [message #281116 is a reply to message #280985] Thu, 15 November 2007 14:55 Go to previous messageGo to next message
amitkr
Messages: 17
Registered: November 2007
Junior Member
Hi,

Following is what is happeing in the code...

PROCEDURE AK_TEST IS
         CURSOR case IS SELECT ct.case_id
         	        FROM CASE_TABLE ct,LOG  al  
         		WHERE CT.case_id=al.id
			AND al.flag<>1
         		ORDER BY 1;

         CURSOR event (eve_case_id IN NUMBER) IS SELECT DISTINCT ET.case_id, ST.term,ET.DESC
            					FROM EVENT_TABLE ET, S_TABLE ST
            					WHERE ET.case_id = eve_case_id 
            					AND  ET.TERM=ST.TERM
            					ORDER BY 1,2;

         CURSOR product (prod_case_id IN NUMBER) IS SELECT DISTINCT PT.case_id, PT.product_name,PT.PRODUCT_ID
                                                    FROM PRODUCT_TALE PT
            					    WHERE PT.case_id = prod_case_id	
            					    ORDER BY 1;
            
         BEGIN

		 FOR c_rec IN case LOOP  
			eve_seq_num  := 1;
			prod_seq_num := 1;
                        
			-- loop to insert events
				FOR c_rec_eve IN event (c_rec.case_id)
				LOOP
					INSERT INTO MAIN_TABLE(seq_num, case_id, term,desc)
						VALUES (eve_seq_num, c_rec.case_id, c_rec_eve.term, c_rec_eve.desc);
					eve_seq_num := eve_seq_num + 1;
				END LOOP;
			
			SELECT COUNT(*) INTO v_product_cnt FROM PRODUCT_TABLE WHERE case_id = c_rec.case_id;
			
			-- to check if product count is greater than event count;
			IF (eve_seq_num - v_product_count) < 1 THEN
				-- to insert blank rows in the table with seq num and case id if product count greater
				FOR j IN (eve_seq_num)..v_product_count LOOP
					INSERT INTO MAIN_TABLE (seq_num, case_id)
						VALUES (j, c_rec.case_id);
				END LOOP;
			END IF;

			-- loop to update table and populate products for case
				FOR c_rec_prod IN c_product (c_rec.case_id) LOOP
					UPDATE MAIN_TABLE
					SET product_name = c_rec_prod.product_name,
					    product_id = c_rec_prod.product_id
					 WHERE case_id = c_rec.case_id
				         AND seq_num = prod_seq_num;
				      prod_seq_num := prod_seq_num + 1;
				END LOOP;
		   END LOOP;
END  ;




Regards
Re: Help with this SQL join [message #281117 is a reply to message #281116] Thu, 15 November 2007 15:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Answer my questions. I don't want to reverse engineer your code.

Regards
Michel
Re: Help with this SQL join [message #281118 is a reply to message #281117] Thu, 15 November 2007 15:11 Go to previous messageGo to next message
amitkr
Messages: 17
Registered: November 2007
Junior Member
Sorry .. but I din get ur question...

The rows in the main table is queried using case_id and seq_num...
Re: Help with this SQL join [message #281119 is a reply to message #281118] Thu, 15 November 2007 15:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I meant, in your datawarehouse, when you have to ask/handle a row that is not the first one: how does it know the values for COL2, COL3... for a row? By searching the one with COL=1?

In addition, why not repeating it.
Because the current does not do it, is not a valid answer.
If the current code gives wrong results, you think you have to still give wrong result? (I know some that answer yes to this question, they know it is silly but they don't care.)

Regards
Michel
Re: Help with this SQL join [message #281120 is a reply to message #281119] Thu, 15 November 2007 15:37 Go to previous messageGo to next message
amitkr
Messages: 17
Registered: November 2007
Junior Member
The current processing is coorect
There are many reports based on main table and so we require data in this form...

eg..

gor a given case_id there are 2 events and 3 products

we populate it like
121  1   abc  etc  etc etc
121  2   wer  etc  etc etc
121  3             etc etc

looking at this table data one can say case 121 has 2 events and 3 products.

whenevr we have to query this table we will use case_id and seq_num to get the desired row..

The code i posted in previous reply is straight forward ... but very heavy... Insert..Insert and then update ...takes too long and i am sure there will be a better way to acheive faster result.

Regards
Amit
Re: Help with this SQL join [message #281149 is a reply to message #281120] Thu, 15 November 2007 19:10 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I take it all back. The link I posted possibly will help you.

Ross Leishman
Re: Help with this SQL join [message #289365 is a reply to message #280805] Thu, 20 December 2007 23:45 Go to previous messageGo to next message
emix
Messages: 3
Registered: July 2007
Junior Member
amitkr:

Something like this maybe can help you ....



select COL1, 
       seqnum as COL, 
       decode(previous,col2,null,col2) COL2, 
       decode(previous,col2,null,col3) COL3,
       decode(previous,col2,null,col4) COL4,
       col12, 
       col13, 
       col14       
from 
(
    SELECT a.col1, 
           ROW_NUMBER () OVER (ORDER BY col1) AS seqnum,
           LAG (a.col2) OVER (ORDER BY col1) PREVIOUS, 
           a.col2, 
           a.col3, 
           a.col4,
           a.col5, 
           b.col12, 
           b.col13, 
           b.col14
  FROM tablea a, tableb b
 WHERE a.col1 = b.col11) 


COL1 COL  COL2 COL3 COL4 COL12 COL13 COL14 
---- ---- ---- ---- ---- ----- ----- ----- 
   1    1 AK   XY   WE   TRT   YTY   OUT        
   1    2                DSF   ERER  TRY        
   1    3                VALX  VALY  ZX         



Regards,
Emix
Re: Help with this SQL join [message #289485 is a reply to message #289365] Sat, 22 December 2007 05:33 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
And that might have been helpful to the OP a month ago
Previous Topic: sql function
Next Topic: How to catch the exception ORA-00923?
Goto Forum:
  


Current Time: Wed Dec 07 12:43:43 CST 2016

Total time taken to generate the page: 0.14309 seconds