OraFAQ Forum: SQL & PL/SQL » Select Query based on closed / reopened flag

Home » SQL & PL/SQL » SQL & PL/SQL » Select Query based on closed / reopened flag

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

Select Query based on closed / reopened flag [message #639630]

Mon, 13 July 2015 03:36

simramkumar
Messages: 2
Registered: July 2015

Junior Member

0
down vote
favorite

I have a dimension and a transaction_fact tables. transaction_fact table has multiple records of a single record of the dimension table. transaction_fact table has columns closed_today_flag, reopened_today_flag, effective_from, effective_till along with other columns.

When a record is created in dimension table an entry will be added in transaction_fact table. If the same record is updated/closed/reopened entries will be added in the transaction_fact table.

Dimension table will not have any status. Currently the record in a dimension table may be in open/closed/reopened state.

Query:

I have to select only closed records from transaction_fact table based on the current status from start date(effective_from) to end date(effective_till).

Table Data:

closed_today_flag reopened_today_flag effective_from effective_till
true false 2014-05-29 09:30:00.000 2014-10-31 09:30:00.000
false false 2014-05-28 09:30:00.000 2014-05-29 09:30:00.000
false false 2015-01-22 10:30:00.000 2015-02-25 10:30:00.000
false false 2014-05-27 09:30:00.000 2014-05-28 09:30:00.000
false false 2015-02-25 10:30:00.000 2015-03-09 09:30:00.000
false false 2015-03-09 09:30:00.000 2015-03-09 09:30:00.000
false false 2015-03-09 09:30:00.000 2015-03-09 09:30:00.000
false true 2014-10-31 09:30:00.000 2015-01-22 10:30:00.000
false false 2015-03-09 09:30:00.000 2015-06-25 09:30:00.000
false false 2015-06-25 09:30:00.000 2200-01-01 10:29:59.999

Report message to a moderator

Re: Select Query based on closed / reopened flag [message #639631 is a reply to message #639630]

Mon, 13 July 2015 03:37

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

Senior Member

Hi,

Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags.

And please provide a test case. Posted table data is of no use unless someone reverse engineers it to create the table and insert data.

[Updated on: Mon, 13 July 2015 03:39]

Report message to a moderator

Re: Select Query based on closed / reopened flag [message #639632 is a reply to message #639630]

Mon, 13 July 2015 04:31

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

Senior Member
Account Moderator

Quote:

0
down vote
favorite

Report message to a moderator

Re: Select Query based on closed / reopened flag [message #639633 is a reply to message #639632]

Mon, 13 July 2015 04:32

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

Senior Member

Seems to be copy paste from Stack Overflow Laughing

Report message to a moderator

Re: Select Query based on closed / reopened flag [message #639634 is a reply to message #639633]

Mon, 13 July 2015 04:34

simramkumar
Messages: 2
Registered: July 2015

Junior Member

Yes. I posted there also.

Report message to a moderator

Re: Select Query based on closed / reopened flag [message #639635 is a reply to message #639634]

Mon, 13 July 2015 04:50

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

Senior Member
Account Moderator

So post what is requested here: a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

Report message to a moderator

Previous Topic:	To tune a PL/SQL block
Next Topic:	Rowcount

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 23:51:20 CDT 2024