OraFAQ Forum: SQL & PL/SQL » More than one column in Connect by prior

Home » SQL & PL/SQL » SQL & PL/SQL » More than one column in Connect by prior (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)

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

More than one column in Connect by prior [message #632516]

Mon, 02 February 2015 06:04

mamalik
Messages: 268
Registered: November 2008
Location: Pakistan

Senior Member

Dear All.
I have following table with data.

create table test(
trn_num number,
trn_srl number,
itm_cod number, 
itm_rat number,
pnt_trn_num number, 
pnt_trn_srl number);

Insert into TEST
   (TRN_NUM, TRN_SRL, ITM_COD, ITM_RAT, PNT_TRN_NUM, PNT_TRN_SRL)
 Values
   (1, 1, 1, 1.5, 0, 0);
Insert into TEST
   (TRN_NUM, TRN_SRL, ITM_COD, ITM_RAT, PNT_TRN_NUM, PNT_TRN_SRL)
 Values
   (1, 2, 2, 1, 0, 0);
Insert into TEST
   (TRN_NUM, TRN_SRL, ITM_COD, ITM_RAT, PNT_TRN_NUM, PNT_TRN_SRL)
 Values
   (2, 1, 3, 2, 1, 1);
Insert into TEST
   (TRN_NUM, TRN_SRL, ITM_COD, ITM_RAT, PNT_TRN_NUM, PNT_TRN_SRL)
 Values
   (3, 1, 1, 1, 0, 0);
COMMIT;

Select * From Test;
Result is
TRN_NUM	TRN_SRL	ITM_COD	ITM_RAT	PNT_TRN_NUM	PNT_TRN_SRL

1	  1	  1	 1.5	   0	        0
1	  2	  2	 1	   0	        0
2	  1	  3	 2	   1	        1
3	  1	  1	 1	   0	        0

Now i write following query.

Select Trn_Num,Trn_Srl,Itm_Rat,itm_cod
  From test
  Where  Nvl(Pnt_trn_num,0)=0  
  Connect By  prior pnt_trn_num=trn_Num and trn_Srl=pnt_trn_Srl 
  Start With trn_num=2

Above query returned now row.While i need following result.

Trn_Num Trn_Srl,Itm_Rat,Itm_Cod
1         1        1.5     1

How can i get this? Can we not use more than one column in connect by prior.

thanks in advance for your time.

Best Regards,
Asif

[Edit by Lalit : fixed a small typo in topic title]

[Updated on: Mon, 02 February 2015 08:13] by Moderator

Report message to a moderator

Re: More than one column in Connect by perior [message #632519 is a reply to message #632516]

Mon, 02 February 2015 06:19

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

yes one can use multiple columns in connect by prior,
just put PRIOR before each column
for ex:-

connect by prior EMPNO=EMPNO
                 and PRIOR DEPTNO=DEPTNO;

Report message to a moderator

Re: More than one column in Connect by perior [message #632520 is a reply to message #632519]

Mon, 02 February 2015 06:26

mamalik
Messages: 268
Registered: November 2008
Location: Pakistan

Senior Member

Thanks for your reply. I have tried following query but still no row returned.

Select Itm_Rat,itm_cod
  From test
  Where  Nvl(Pnt_trn_num,0)=0  
  Connect By  prior pnt_trn_num=trn_Num and prior trn_Srl=pnt_trn_Srl 
  Start With trn_num=2

Report message to a moderator

Re: More than one column in Connect by perior [message #632522 is a reply to message #632516]

Mon, 02 February 2015 06:51

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

Senior Member
Account Moderator

Quote:

While i need following result.

Which is in words?

Report message to a moderator

Re: More than one column in Connect by perior [message #632524 is a reply to message #632522]

Mon, 02 February 2015 07:49

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Quote:

Trn_Num Trn_Srl,Itm_Rat,Itm_Cod
1         1        1.5     1

Looking at the data and your expected output you provided, it seems you only need the record with max(itm_rat) and min(TRN_NUM).

Nor sure why you are using hierarchical query. As Michel said, explqin the rules in words to arrive at your desired output.

Report message to a moderator

Re: More than one column in Connect by perior [message #632544 is a reply to message #632524]

Mon, 02 February 2015 22:43

mamalik
Messages: 268
Registered: November 2008
Location: Pakistan

Senior Member

Dear All my requirement is that , in following table

TRN_NUM	TRN_SRL	ITM_COD	ITM_RAT	PNT_TRN_NUM	PNT_TRN_SRL

1	  1	  1	 1.5	   0	        0
1	  2	  2	 1	   0	        0
2	  1	  3	 2	   1	        1
3	  1	  1	 1	   0	        0

in this table (Trn_Num,Trn_Srl) is composite primary key.In Trn_Num 2 , pnt_Trn_Num Is 1 And P_Trn_Srl is 1 , its means that Itm_rat Where Trn_Num = 1 And Trn_Srl=1 will be added in rate of Trn_Num 2.

Got the point?

Thanks for your time.
Best Regards,
Asif.

Report message to a moderator

Re: More than one column in Connect by perior [message #632547 is a reply to message #632544]

Mon, 02 February 2015 23:43

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

mamalik wrote on Tue, 03 February 2015 10:13

in this table (Trn_Num,Trn_Srl) is composite primary key.In Trn_Num 2 , pnt_Trn_Num Is 1 And P_Trn_Srl is 1 , its means that Itm_rat Where Trn_Num = 1 And Trn_Srl=1 will be added in rate of Trn_Num 2.

And how does it explain about the expected output? Still not clear.

Report message to a moderator

Re: More than one column in Connect by perior [message #632548 is a reply to message #632547]

Mon, 02 February 2015 23:51

mamalik
Messages: 268
Registered: November 2008
Location: Pakistan

Senior Member

Dear Sir,
In above data, in row against trn_num 2, Pnt_trn_num is 1 and pnt_Trn_Srl=1, its mean that it is associated with data with trn_num=1 and trn_Srl=1. Ok. while i run query using connect by string which start from 2, it must return data in row with trn_num=1 and trn_srl=1 because pnt_trn_num=1 and pnt_Trn_Srl=1 in row with trn_num=2.

Now i write following query.

Select Trn_Num,Trn_Srl,Itm_Rat,itm_cod
  From test
  Where  Nvl(Pnt_trn_num,0)=0  
  Connect By  prior pnt_trn_num=trn_Num and prior trn_Srl=pnt_trn_Srl 
  Start With trn_num=2

Above query returned now row.While it should return following result.

Trn_Num Trn_Srl,Itm_Rat,Itm_Cod
1         1        1.5     1

Thanks for your time and support.

[Updated on: Mon, 02 February 2015 23:51]

Report message to a moderator

Re: More than one column in Connect by perior [message #632552 is a reply to message #632548]

Tue, 03 February 2015 01:42

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

mamalik wrote on Tue, 03 February 2015 11:21

In above data, in row against trn_num 2, Pnt_trn_num is 1 and pnt_Trn_Srl=1, its mean that it is associated with data with trn_num=1 and trn_Srl=1. Ok. while i run query using connect by string which start from 2, it must return data in row with trn_num=1 and trn_srl=1 because pnt_trn_num=1 and pnt_Trn_Srl=1 in row with trn_num=2.

While it should return following result.
Trn_Num Trn_Srl,Itm_Rat,Itm_Cod
1         1        1.5     1 

You just need to join the table with itself to get the required row.

SQL> SELECT t1.Trn_Num,
  2    t1.Trn_Srl,
  3    t1.Itm_Rat,
  4    t1.Itm_Cod
  5  FROM TEST t1,
  6    TEST t2
  7  WHERE t1.trn_num = t2.pnt_trn_num
  8  AND t1.trn_srl   = t2.pnt_trn_srl
  9  /

   TRN_NUM    TRN_SRL    ITM_RAT    ITM_COD
---------- ---------- ---------- ----------
         1          1        1.5          1

SQL>

Regards,
Lalit

Report message to a moderator

Re: More than one column in Connect by perior [message #632557 is a reply to message #632552]

Tue, 03 February 2015 03:07

mamalik
Messages: 268
Registered: November 2008
Location: Pakistan

Senior Member

Dear Sir, This can not fulfill my requirement because there can be more than one pnt_trn_num and pnt_Trn_srl against one record. for example in following data.

TRN_NUM	TRN_SRL	ITM_COD	ITM_RAT	PNT_TRN_NUM	PNT_TRN_SRL

1	  1	  1	 1.5	   0	        0
1	  2	  2	 1	   0	        0
2	  1	  3	 2	   1	        1
2	  2	  3	 2	   1	        2
3	  1	  1	 1	   0	        0

now query must return following

  TRN_NUM    TRN_SRL    ITM_RAT    ITM_COD
---------- ---------- ---------- ----------
         1          1        1.5          1
         1          2         1           2

But your query is only for one pnt_trn_num and pnt_Trn_srl againt each record.

Thanks for your reply.

Report message to a moderator

Re: More than one column in Connect by perior [message #632562 is a reply to message #632557]

Tue, 03 February 2015 03:26

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

mamalik wrote on Tue, 03 February 2015 14:37

This can not fulfill my requirement because there can be more than one pnt_trn_num and pnt_Trn_srl against one record. for example in following data.
TRN_NUM	TRN_SRL	ITM_COD	ITM_RAT	PNT_TRN_NUM	PNT_TRN_SRL

1	  1	  1	 1.5	   0	        0
1	  2	  2	 1	   0	        0
2	  1	  3	 2	   1	        1
2	  2	  3	 2	   1	        2
3	  1	  1	 1	   0	        0
now query must return following
  TRN_NUM    TRN_SRL    ITM_RAT    ITM_COD
---------- ---------- ---------- ----------
         1          1        1.5          1
         1          2         1           2
But your query is only for one pnt_trn_num and pnt_Trn_srl againt each record.

Did you even check before posting? My query gives exactly what you want -

SQL> Insert into TEST
  2     (TRN_NUM, TRN_SRL, ITM_COD, ITM_RAT, PNT_TRN_NUM, PNT_TRN_SRL)
  3   VALUES
  4     (2, 2, 3, 2, 1, 2);

1 row created.

SQL>
SQL> SELECT * FROM TEST
  2  /

   TRN_NUM    TRN_SRL    ITM_COD    ITM_RAT PNT_TRN_NUM PNT_TRN_SRL
---------- ---------- ---------- ---------- ----------- -----------
         1          1          1        1.5           0           0
         1          2          2          1           0           0
         2          1          3          2           1           1
         3          1          1          1           0           0
         2          2          3          2           1           2

SQL>
SQL> SELECT t1.Trn_Num,
  2    t1.Trn_Srl,
  3    t1.Itm_Rat,
  4    t1.Itm_Cod
  5  FROM TEST t1,
  6    TEST t2
  7  WHERE t1.trn_num = t2.pnt_trn_num
  8  AND t1.trn_srl   = t2.pnt_trn_srl
  9  /

   TRN_NUM    TRN_SRL    ITM_RAT    ITM_COD
---------- ---------- ---------- ----------
         1          1        1.5          1
         1          2          1          2

SQL>

Report message to a moderator

Re: More than one column in Connect by perior [message #632564 is a reply to message #632562]

Tue, 03 February 2015 03:51

mamalik
Messages: 268
Registered: November 2008
Location: Pakistan

Senior Member

Thanks a lot dear Lalit Kumar Sb. I got this. How can i get using Connect by. Actully i need row against Trn_Num 2 also. like follwoing

 TRN_NUM    TRN_SRL    ITM_RAT    ITM_COD
---------- ---------- ---------- ----------
         1          1        1.5          1
         1          2         1           2
         2	    1	     2	          3
         2	    2	     2	          3

I need rows againt trn_num=2 and including all rows which are falling against pnt_Trn_num and pnt_trn_srl.

I am really sorry actually i am unable to describe my problem.

Again thanks a lot for your reply.

Regards,
Asif.

Report message to a moderator

Re: More than one column in Connect by perior [message #632611 is a reply to message #632564]

Tue, 03 February 2015 10:12

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

Can you put here what you have tried in your query?

Report message to a moderator

Re: More than one column in Connect by perior [message #632655 is a reply to message #632611]

Tue, 03 February 2015 22:18

mamalik
Messages: 268
Registered: November 2008
Location: Pakistan

Senior Member

I have tried following query.

Select Trn_Num,Trn_Srl,Itm_Rat,itm_cod
  From test
  Where  Nvl(Pnt_trn_num,0)=0  
  Connect By  prior pnt_trn_num=trn_Num and prior trn_Srl=pnt_trn_Srl 
  Start With trn_num=2

i need query which return row against trn_num 2 in following with and also all rows whose (trn_num,trn_Srl) is in(pnt_trn_num,pnt_Trn_Srl) of trn_num 2.

Data Is

TRN_NUM	TRN_SRL	ITM_COD	ITM_RAT	PNT_TRN_NUM	PNT_TRN_SRL

1	  1	  1	 1.5	   0	        0
1	  2	  2	 1	   0	        0
2	  1	  3	 2	   1	        1
2	  2	  3	 2	   1	        2
3	  1	  1	 1	   0	        0

I need query which may return following result.(Required Result).

Trn_Num Trn_Srl Itm_Rat itm_cod
1           1      1.5     1    
1           2      1.5     2
2           1       2      3  
2           2       2      3

Please note that pnt_trn_num is 1 and pnt_trn_Srl is 1 and 2 in row where trn_num=2.
Its means that this record is tied up with trn_num 1 and trn_Srl in(1,2).
I need query which should display record where trn_num=2 with 2 other records which are tied up using pnt_trn_num and pnt_trn_srl.
In pnt_Trn_num value is 1 and in pnt_trn_srl is (1,2) so i am trying to write hierarchical query by using "Connect By" clause which may show my required data.

Thanks in advance for your time and help.

Best Regards,
Muhammad Asif.

Report message to a moderator

Re: More than one column in Connect by perior [message #632807 is a reply to message #632655]

Thu, 05 February 2015 23:27

mamalik
Messages: 268
Registered: November 2008
Location: Pakistan

Senior Member

Did anyone get my point?

Report message to a moderator

Re: More than one column in Connect by perior [message #632809 is a reply to message #632807]

Thu, 05 February 2015 23:52

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

mamalik wrote on Fri, 06 February 2015 10:57

Did anyone get my point?

Did you get my point? I have shown you that you just need to join the tables twice to get the desired rows, I don't see a reason to use hierarchical query.

SQL> SELECT * FROM TEST;

   TRN_NUM    TRN_SRL    ITM_COD    ITM_RAT PNT_TRN_NUM PNT_TRN_SRL
---------- ---------- ---------- ---------- ----------- -----------
         1          1          1        1.5           0           0
         1          2          2          1           0           0
         2          1          3          2           1           1
         3          1          1          1           0           0
         2          2          3          2           1           2

SQL>
SQL> WITH DATA AS
  2    (SELECT t1.trn_num A,
  3      t1.TRN_SRL b,
  4      t1.ITM_RAT c,
  5      t1.ITM_COD d,
  6      t2.trn_num e,
  7      t2.TRN_SRL f,
  8      t2.ITM_RAT g,
  9      t2.ITM_COD h
 10    FROM TEST t1,
 11      TEST t2
 12    WHERE t1.trn_num = t2.pnt_trn_num
 13    AND t1.trn_srl   = t2.pnt_trn_srl
 14    )
 15  SELECT t.A trn_num, t.b trn_srl, t.c itm_rat, t.d itm_cod FROM DATA t
 16  UNION ALL
 17  SELECT t.e, t.f, t.g, t.h FROM DATA t
 18  /

   TRN_NUM    TRN_SRL    ITM_RAT    ITM_COD
---------- ---------- ---------- ----------
         1          1        1.5          1
         1          2          1          2
         2          1          2          3
         2          2          2          3

SQL>

Report message to a moderator

Previous Topic:	Explain code
Next Topic:	query in CASE expression in select

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 16:03:30 CDT 2024