Need help with this Oracle Query

10 pts.
Tags:
Oracle queries
Oracle Query
Query optimization
BPC_APPLICATIONS_TRANSACTIONS (Table) Application_no,Transaction_no,Transaction_date Step_id Dept_code Decision 20090045 1 01/14/2009 10:07:17 1 001 0 20090045 2 01/14/2009 10:07:17 70 007 0 20090045 3 01/14/2009 10:08:57 1 001 0 20090045 4 01/14/2009 10:08:57 60 006 0 20090045 5 01/14/2009 10:14:12 71 007 1 20090045 6 01/14/2009 10:14:41 61 006 1 20090045 7 01/18/2009 11:45:13 73 007 1 20090045 8 01/18/2009 11:46:26 74 007 1 20090045 9 01/18/2009 11:47:13 7 001 1 20090045 11 01/18/2009 11:48:43 70 007 0 20090045 10 01/18/2009 11:48:43 1 001 0 20090045 12 01/27/2009 11:22:16 1 001 0 20090045 13 01/28/2009 10:30:56 71 007 1 20090045 14 01/28/2009 10:31:37 73 007 1 20090045 15 01/28/2009 10:32:14 74 007 1 20090045 16 01/28/2009 10:35:44 7 001 1 20090045 17 01/28/2009 10:35:53 1 001 0 20090045 18 01/28/2009 10:35:53 70 007 0 20090045 19 02/01/2009 10:35:53 71 007 0 ----------------------------------------------------------------------------------------------------- select a.application_no,a.transaction_date dateIn,b.transaction_date dateout, a.dept_code,b.decision from bpc_applications_transactions a, bpc_applications_transactions b where a.dept_code=007 and a.step_id in (71,73) and b.step_id in (71,73) and a.transaction_date in (select transaction_date from bpc_applications_transactions where step_id=71 and a.application_no=b.application_no ) and b.transaction_date in (select transaction_date from bpc_applications_transactions where step_id=73 and a.application_no=b.application_no ) and b.decision in (select nvl(decision,0) from bpc_applications_transactions where step_id=73 and a.application_no=b.application_no ) and a.application_no=b.application_no; --------------------------------------------------------------------------------------------------------- When I am running the above query , i am getting the output below: APPLICATION_NO DATEIN DATEOUT DECISION 20090045 1/28/2009 10:30:56 ص 1/18/2009 11:45:13 ص 1 20090045 1/28/2009 10:30:56 ص 1/28/2009 10:31:37 ص 1 20090045 1/14/2009 10:14:12 ص 1/28/2009 10:31:37 ص 1 20090045 1/14/2009 10:14:12 ص 1/18/2009 11:45:13 ص 1 20090045 02/01/2009 10:35:53 1/28/2009 10:31:37 ص 1 20090045 02/01/2009 10:35:53 1/18/2009 11:45:13 ص 1 ---------------------------------------------------------------------------------------------------- But I should get the output as: APPLICATION_NO DATEIN DATEOUT DECISION 20090045 1/28/2009 10:30:56 ص 1/28/2009 10:31:37 ص 1 20090045 1/14/2009 10:14:12 ص 1/18/2009 11:45:13 ص 1 20090045 02/01/2009 10:35 0 Can anyone help me to tune my query above to get the desired result? Thanks, SK...
ASKED: April 5, 2009  9:08 AM
UPDATED: April 6, 2009  1:51 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

I’m not sure if I understood correctly the logic/meaning of the desired output. Could you please explain it, with words ?

How exactly do you identify what is the OUT record for a given IN record ? what is the criteria ?

I’m guessing that a step_id=71 means IN, and a step_id=73 means OUT, and that for a given IN record, the corresponding OUT record is the one that has step_id=73 with the first transaction_no that is greater than its transaction_no.
I’m also guessing that a decision = 0 means that this IN record has no corresponding OUT record.

If I’m correct, a query like this could do the trick:

SELECT a.application_no,a.transaction_date DATE_IN, b.transaction_date DATE_OUT
FROM bpc_applications_transactions a, bpc_applications_transactions b
WHERE a.application_no = b.application_no
AND a.dept_code = 7
AND a.step_id = 71
AND b.step_id = 73
AND b.transaction_no = (SELECT MIN(transaction_no) FROM bpc_applications_transactions WHERE application_no = a.application_no AND step_id = 73 AND transacTion_no > a.transaction_no)
UNION
SELECT application_no,transaction_date,NULL
FROM bpc_applications_transactions
WHERE dept_code = 7
AND step_id = 71
AND decision = 0;

I currently don’t have a database to test it, so please make the necessary corrections.

If this is not what you need, please provide more details.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following