I have the following table(table_name)

Plant Dc Flag Start_week End_week
---------------------------------------------------------------
6745 2834 X 6-AUG-06 10-SEP-06
6745 2834 M 11-SEp-06 NULL

There are many others records in this table..



I need to select the flag for the plant '6745' based on week falling
between the start_week column and end_week column.
For eg: 1) If the week is august 13 then 'X' flag should be selected
2) If the week is September 17 then 'M' flag should be
selected

I issued the following query..

SELECT t1.FLAG
FROM table_name t1,
(SELECT MIN(start_date) AS startdate
FROM table_name
WHERE TO_DATE('aug 13,06','mm/dd/yyyy') >= start_date
AND (TO_DATE('aug 13,06','mm/dd/yyyy') <= end_date OR end_date
IS NULL)) t2
WHERE t1.start_date = t2.startdate
AND t1.plant = '6745';


The above query using inline view is not working.It returns "no rows
selected". Please help me in correcting my query.



Thanks,
Abishek




The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.

www.wipro.com

Oracle LazyDBA home page