Re: invalid number error

Sweth Chandramouli wrote:
>
> it's been a while since i've done any sql, but i'm having some
> trouble with what really shouldn't be a complicated select statement at
> all. the error i get (ORA-01722: invalid number) seems to be based on
> this part of the where clause:
>
> where mac.contract_number=cct.contract_code
> and to_number(mac.create_date) > (select (to_char(sysdate,'J') -
> to_char(to_date('1/2/70','MM/DD/YY'),'J'))*86400 from dual)
>
> create_date is a number field in the table aliased to mac in the
> from statement (not shown), which contains a value corresponding to the
> number of seconds since jan. 1, 1970 (as created by a different app; i
> don't like it, myself, but can't do anything to change it). i'm trying to
> have the select match all items in the two joined tables (mac and cct)
> that are newer than one day old. the nested select after the greater-than
> sign works fine if run on its own, returning the number of seconds from
> jan. 1, 1970 until midnight of the day before the statement is run, and a
> select of to_number(mac.create_date) works fine as well (the to_number is
> redundant, i realize, since create_date is already a number field, but i
> put it in after i got the error, just to be safe).
> i'm sure it's something simple that i'm forgetting here; could
> some kind soul point it out to me?
>
> relearning sql,
> sweth.
> --
> .

Hi,

think it should be:

 where mac.contract_number=cct.contract_code

    and to_number(mac.create_date) > (select (to_number(to_char(sysdate,'J')) -
 to_number(to_char(to_date('1/2/70','MM/DD/YY'),'J')))*86400 from dual)

-- 
Regards

Matthias Gresz    :-)