OraFAQ Forum: SQL & PL/SQL » Outer join with cut-off condition

Home » SQL & PL/SQL » SQL & PL/SQL » Outer join with cut-off condition

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

Outer join with cut-off condition [message #446874]

Wed, 10 March 2010 20:07

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

Re: Outer join with cut-off condition [message #446880 is a reply to message #446878]

Wed, 10 March 2010 21:14

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

Report message to a moderator

Re: Outer join with cut-off condition [message #446881 is a reply to message #446880]

Wed, 10 March 2010 21:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

Report message to a moderator

Re: Outer join with cut-off condition [message #447079 is a reply to message #446880]

Thu, 11 March 2010 19:47

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

Report message to a moderator

Previous Topic:	need help in sql query (merged by CM)
Next Topic:	Procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 13 21:55:40 CDT 2025