Re: Finding out erroneous records

On May 5, 3:00=A0am, Sandy80 <svarshneym...@[EMAIL PROTECTED]
> wrote:
> Hi,
>
> I have a table that has sample records like below:
>
> Emp No. =A0 =A0 =A0 =A0 Unique ID =A0 =A0 =A0 =A0 =A0 =A0 =A0 From Date
=
=A0 =A0 =A0 =A0 =A0 =A0 =A0 To Date
> 11 =A0 =A0 =A0 =A0 =A0 =A0 =A01234 =A0 =A0 =A0 =A0 =A0 =A001-Jan-2005
=A0 =
=A0 30-Sep-2005
> 11 =A0 =A0 =A0 =A0 =A0 =A0 =A02345 =A0 =A0 =A0 =A0 =A0 =A001-Oct-2005
=A0 =
=A0 31-Oct-2005
> 11 =A0 =A0 =A0 =A0 =A0 =A0 =A03456 =A0 =A0 =A0 =A0 =A0 =A001-Nov-2005
=A0 =
=A0 31-Oct-2005
>
> The task that I have is to find out records where the From Date is
> greater than the To Date. This is needed because the 3rd record in the
> example above is actually an erroneous record which needs to be
> deleted. The query that I was able to write was returning the 2nd
> record also because 01-Oct-2005 is greater than 30-Sep-2005. Please
> help me with the query which would return only the 3rd record.
>
> Any help would be appreciated...thanks!
> Sandy

You've written the query already, albeit in text rather than SQL; I
don't understand what query you've written to return the data you
re****t as it's likely far more complex than it needs to be.
'Translate', if you will,  your English text into SQL and you'll have
your answer:

"find out records where the From Date is greater than the To Date."

The SQL, as shown below, should be easy to write from that description
--

SQL> create table sandy(
  2          emp_on number,
  3          unique_id number,
  4          from_date date,
  5          to_date date
  6  );

Table created.

SQL>
SQL> insert all
  2  into sandy
  3  values(11,1234,to_date('01-Jan-2005','DD-Mon-YYYY'),to_date('30-
Sep-2005','DD-Mon-YYYY') )
  4  into sandy
  5  values(11,2345,to_date('01-Oct-2005','DD-Mon-YYYY'),to_date('31-
Oct-2005','DD-Mon-YYYY') )
  6  into sandy
  7  values(11,3456,to_date('01-Nov-2005','DD-Mon-YYYY'),to_date('31-
Oct-2005','DD-Mon-YYYY') )
  8  select * from dual;

3 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select *
  2  from sandy
  3  where to_date < from_date;

    EMP_ON  UNIQUE_ID FROM_DATE TO_DATE
---------- ---------- --------- ---------
        11       3456 01-NOV-05 31-OCT-05

SQL>


David Fitzjarrell