Home » SQL & PL/SQL » SQL & PL/SQL » Outer join with cut-off condition
Outer join with cut-off condition [message #446874] Wed, 10 March 2010 20:07 Go to next message
summoner
Messages: 44
Registered: March 2009
Member
Suppose I have two tables

Transaction
TXN_ID:integer
TXN_DATE:date

Return_Transaction
RET_TXN_ID:integer
TXN_ID:integer
RET_TXN_DATE:date

Example:
Transaction
100, 2010/03/10
101, 2010/03/11
102, 2010/03/11

Return_Transaction
500, 100, 2010/03/11
501, 102, 2010/03/12

Transaction may have returned transactions. We use outer join to join the tables using TXN_ID. We have a report that shows the following data

The report use the following basic query to check all transactions with returned transactions information
Quote:

SELECT t.txn_id,
       t.txn_date,
       rt.ret_txn_id
FROM   TRANSACTION t,
       return_transaction rt
WHERE  t.txn_id = rt.txn_id (+) 


Result
TXN_ID, TXN_DATE, RET_TXN_ID
100, 2010/03/10, 500
102, 2010/03/11, 501



If user want to check all transactions with no returned transactions, a where clause is appended to the query
Quote:

SELECT t.txn_id,
       t.txn_date
FROM   TRANSACTION t,
       return_transaction rt
WHERE  t.txn_id = rt.txn_id (+)
       AND Nvl((SELECT 1
                FROM   return_transaction rt
                WHERE  t.txn_id = rt.txn_id),0) = 0 


Result
TXN_ID, TXN_DATE
101, 2010/03/11


Now we have enhancement request for cut-off issue. There is a report to check all transactions. On 2010/03/11, the report shows there is only 1 record that do not have returned tranactions
Quote:

TXN_ID, TXN_DATE, RET_TXN_ID
100, 2010/03/10, 500
101, 2010/03/11, NULL
102, 2010/03/11, NULL


However, they print the report again on 2010/03/12 and there are 2 records with no returned transaction.
Quote:

TXN_ID, TXN_DATE, RET_TXN_ID
100, 2010/03/10, 500
101, 2010/03/11, NULL
102, 2010/03/11, 501



User want to have a filter condition based on TXN_DATE and RET_TXN_DATE so that the report will be freezed at some point.
i.e. The report will be same no matter the datetime user prints the report.

Quote:

Cut-off time as 2010/03/10

TXN_ID, TXN_DATE, RET_TXN_ID
100, 2010/03/10, NULL

Cut-off time as 2010/03/11

TXN_ID, TXN_DATE, RET_TXN_ID
100, 2010/03/10, 500
101, 2010/03/11, NULL
102, 2010/03/11, NULL


Could anyone suggest whether I could handle it? Thanks

[Updated on: Wed, 10 March 2010 20:27]

Report message to a moderator

Re: Outer join with cut-off condition [message #446878 is a reply to message #446874] Wed, 10 March 2010 20:49 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>User want to have a filter condition based on TXN_DATE and RET_TXN_DATE so that the report will be freezed at some point.

Who determines when/what this "freezed at some point" is
& how does this become part of the query?

Asked a different way, how does SQL today "know" or remember that the report was run yesterday & results need to reflect yesterday's data & not today's data?

It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Outer join with cut-off condition [message #446880 is a reply to message #446878] Wed, 10 March 2010 21:14 Go to previous messageGo to next message
summoner
Messages: 44
Registered: March 2009
Member
Thanks for reply

Users define it when they generate the report. That is, users will first select a date in UI panel, then they press "generate" and then a report is pop up
Re: Outer join with cut-off condition [message #446881 is a reply to message #446880] Wed, 10 March 2010 21:24 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>Users define it when they generate the report.
It this request to see yesterday's data or today's data.

You said they run "same report" on different days, but wanted "same results".

You may need to establish a complete report request form to differentiate between new/current report & historical/reprint report
Re: Outer join with cut-off condition [message #447079 is a reply to message #446880] Thu, 11 March 2010 19:47 Go to previous message
summoner
Messages: 44
Registered: March 2009
Member
Is it possible to work on the same report file?
What we want is to have an option field to indicate whether we want to show historical data.

If users click the option in GUI panel and choose the date they defined, the system appends a filter condition in selection query. The report will then show historical data based on query result
Previous Topic: need help in sql query (merged by CM)
Next Topic: Procedure
Goto Forum:
  


Current Time: Sun Sep 25 06:08:55 CDT 2016

Total time taken to generate the page: 0.08693 seconds