Home » SQL & PL/SQL » SQL & PL/SQL » Chained transaction retrieval query
Chained transaction retrieval query [message #421675] Wed, 09 September 2009 13:40 Go to next message
namitanamburi
Messages: 35
Registered: March 2009
Member
These are few transactions and corresponding adjustments made in a bank.
Tran #	Type	Location Code	Date	Previous Adj Code
153	TRANSACTION	4	01-SEP-09 12.00.00 AM	
155	Adjustment	6	04-SEP-09 04.14.06 PM	153
157	TRANSACTION	4	04-SEP-09 04.31.00 PM	
162	Adjustment	6	04-SEP-09 04.50.29 PM	157
169	TRANSACTION	4	30-AUG-09 12.00.00 AM	
171	Adjustment	6	30-AUG-09 12.00.00 AM	155
172	TRANSACTION	4	30-AUG-09 12.00.00 AM

What does this mean ?
ACCcode	Type	Location Code	Date	Previous Adj Code
               	      
153    TRANSACTION	  4  01-SEP-09 12.00.00 AM	155	  Adjustment      6  04-SEP-09 04.14.06 PM	153
171	Adjustment	  6  30-AUG-09 12.00.00 AM	155


The transaction with Acc code 153 has been adjusted twice, the first adjustments acc code is 155 and its previous adj code (153) is equal to acc code of the original transaction.(153).

So the most recent adjustment is 
171	Adjustment	6	30-AUG-09 12.00.00 AM	155

I want the out put to be like this.

It means for each transaction, I want to see most recent adjustment, they are linked with previous ADJ Code.
Tran #	Type	Location Code	Date	Previous Adj Code	

Grouped by date of transaction 

sep - 01 - 2008 

153	TRANSACTION	4	01-SEP-09 12.00.00 AM		
171	Adjustment	6	30-AUG-09 12.00.00 AM	155

sep - 04 - 2008 

157	TRANSACTION	4	04-SEP-09 04.31.00 PM	
162	Adjustment	6	04-SEP-09 04.50.29 PM	157


Thanks in advance
NAMmu

[added code tags]

[Updated on: Thu, 10 September 2009 03:50] by Moderator

Report message to a moderator

Re: Chained transaction retrieval query [message #421677 is a reply to message #421675] Wed, 09 September 2009 14:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68771
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Fri, 17 July 2009 18:53
...

Also Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

Stiil true.

Report message to a moderator

Re: Chained transaction retrieval query [message #421681 is a reply to message #421677] Wed, 09 September 2009 15:05 Go to previous messageGo to next message
namitanamburi
Messages: 35
Registered: March 2009
Member
hI,


I did try my best to format but every time I copy contents from excel, the formatting is lost.


iam uploading a excel, please look into it .


Thanks

NAMMU.
  • Attachment: NAMMU.csv
    (Size: 0.89KB, Downloaded 781 times)

Report message to a moderator

Re: Chained transaction retrieval query [message #421704 is a reply to message #421681] Wed, 09 September 2009 23:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68771
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
2 points:

  • use code tags
  • Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

Report message to a moderator

Re: Chained transaction retrieval query [message #421752 is a reply to message #421681] Thu, 10 September 2009 04:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's a solution:
create table test_059 (Tran_code number,	tran_Type varchar2(30),	Location_Code number(1),	tran_Date date,	Previous_Adj_Code number);

insert into test_059 values (153	,'TRANSACTION'	,4	,to_date('01-SEP-09 12.00.00 AM','dd-MON-yyyy hh.mi.ss AM'), null);	
insert into test_059 values (155	,'Adjustment'	  ,6	,to_date('04-SEP-09 04.14.06 PM','dd-MON-yyyy hh.mi.ss AM'),	153);
insert into test_059 values (157	,'TRANSACTION'	,4	,to_date('04-SEP-09 04.31.00 PM','dd-MON-yyyy hh.mi.ss AM'),	null);
insert into test_059 values (162	,'Adjustment'	  ,6	,to_date('04-SEP-09 04.50.29 PM','dd-MON-yyyy hh.mi.ss AM'),	157);
insert into test_059 values (169	,'TRANSACTION'	,4	,to_date('30-AUG-09 12.00.00 AM','dd-MON-yyyy hh.mi.ss AM'),	null);
insert into test_059 values (171	,'Adjustment'	  ,6	,to_date('30-AUG-09 12.00.00 AM','dd-MON-yyyy hh.mi.ss AM'),	155);
insert into test_059 values (172	,'TRANSACTION'	,4	,to_date('30-AUG-09 12.00.00 AM','dd-MON-yyyy hh.mi.ss AM'),  null);

with src as (select * from (
             select tran_code,tran_type,tran_date,previous_adj_code
                   ,level row_level
                   ,connect_by_root tran_code  cbr_code
                   ,connect_by_root tran_type  cbr_type
             from   test_059
             connect by prior tran_code = previous_adj_code
             ) where cbr_type = 'TRANSACTION')
select tran_code,tran_type,tran_date
from  src
where tran_code in (select max(tran_code) keep (dense_rank last order by row_level)
                    from src
                    group by tran_type,cbr_code)
order by cbr_code,row_level;

Report message to a moderator

Re: Chained transaction retrieval query [message #422030 is a reply to message #421752] Sun, 13 September 2009 19:38 Go to previous messageGo to next message
namitanamburi
Messages: 35
Registered: March 2009
Member
Hi,

Thanks for your reply.Please ignore all the content in the previous posts.Here is the flow

Transaction --> Adjustment----> OUS -----> OUP

But there can be instances like

Transaction --> Adjustment----> OUP or Transaction ----> OUS or Transaction ----> OUP or Transaction --> Adjustment----> OUS ----> Adjustment ----> OUP

Things to remember an adjustment will never preceed a transaction, but Adjustment, OUS, OUP can always
preceed eachother in a flow.

The output should display transaction and OUS, Transaction and OUP.

The user keys in a date which always is the transaction date and retrieve all linked OUS and OUP with that particular transaction.

      
Tran Code	Type	Date	  Previous Tran Code
			
153	Transaction	01-sep-09	
155	Adjustment	4-Sep-09	153
157	OUS	        4-Sep-09	155
159	Transaction	30-AUG-09	
162	OUS	        30-Aug-09	159
169	Transaction	10-Jan-09	
174	OUS	        9-Feb-09	173
175	OUP	        12-Feb-09	174
173	Transaction	16-Jan-09	
176	OUP	        19-Jan-09	169

			
Req output ----- No adj displayed
			
Tran Code Type	     Date	Previous Tran Code
			
153	Transaction  01-sep-09	
157	OUS	     4-Sep-09	155
			
159	Transaction  30-AUG-09	
162	OUS	     30-Aug-09	159
			
173	Transaction  16-Jan-09	
174	OUS	     9-Feb-09	173
175	OUP	     12-Feb-09	174
			
169	Transaction  10-Jan-09	
176	OUP	     19-Jan-09	169



Thanks

I hope I have posted in proper format Smile

Report message to a moderator

Re: Chained transaction retrieval query [message #422059 is a reply to message #422030] Mon, 14 September 2009 02:57 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can see the test case I posted, with a CREATE TABLE and INSERT statements. I created the first one for you, as you're a relatively new poster - I'm not creating a second test case for you just because you've changed the problem.

When you post a test case, I'll try to look at it for you.

Report message to a moderator

Previous Topic: Accept User Inputs
Next Topic: Oracle PL/SQL transferring value from a cursor to a variable
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Nov 18 14:00:19 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.