By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,649 Members | 2,142 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,649 IT Pros & Developers. It's quick & easy.

view puzzle

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Jul 19 '05 #2

P: n/a
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
Jul 19 '05 #3

P: n/a
> 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
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.