view puzzle

[puppet_sock]

Here's what I want to do.

I want a view that restricts to rows that are "in date" when a
working date exists, but shows the whole table when no date exists.

I have a temporary global work-items table that has a single row
including the working date as one column. This is scrubbed when
the connetion to the database is closed. But some automated
processes will connect and not put an entry into the work items
table. There will be zero rows in the table.

Is there a not-too-complicated way to construct a view that
restricts by a date when there is one, but does not restrict
when there is no date?

Restricting to the date is pretty simple when it's there. It's
just a select statement that has start_date <= working_date
and end_date >= working_date. But how do I procede when there
is no working date?
Socks

[Ed prochak]

pu*********@hotmail.com wrote in message news:<c7**************************@posting.google. com>...

Here's what I want to do.

I want a view that restricts to rows that are "in date" when a
working date exists, but shows the whole table when no date exists.

I have a temporary global work-items table that has a single row
including the working date as one column. This is scrubbed when
the connetion to the database is closed. But some automated
processes will connect and not put an entry into the work items
table. There will be zero rows in the table.

Is there a not-too-complicated way to construct a view that
restricts by a date when there is one, but does not restrict
when there is no date?

Restricting to the date is pretty simple when it's there. It's
just a select statement that has start_date <= working_date
and end_date >= working_date. But how do I procede when there
is no working date?
Socks

offhand, I cannot think of how the final version will work, but AFAIK
if "there is no working date", then your WHERE clause will test for
NULL, IOW,
WHERE working_date IS NULL

Hmm, will the final complete SQL view contain a NOT IN clause??

HTH
ed

[puppet_sock]

ed********@magicinterface.com (Ed prochak) wrote in message news:<4b**************************@posting.google. com>...
[snip]

Hmm, will the final complete SQL view contain a NOT IN clause??

That will work. Yes, select all the ones that are not in the
set excluded by the date.

Thanks.
Socks

[Vladimir Andreev]

> Restricting to the date is pretty simple when it's there. It's

just a select statement that has start_date <= working_date
and end_date >= working_date. But how do I procede when there
is no working date?

What's wrong with:
where working_date is null or working_date between start_date and end_date
Or with this:
where nvl(working_date,start_date) between start_date and end_date

Of course, I assume start_date and end_date cannot be NULL...

HTH
Flado