Invalid Number ORA-01722

I have problem in my query that I always get this kind of error

ORA-01722: invalid number

Whatever I try to doesn’t work. The query is here

  SELECT * FROM 
            (
            SELECT p.ProjectID, p.CustomName, p.Name
                FROM projects p
                INNER JOIN 
                    users u
                ON
                    u.UserID =  1
                WHERE 
                    u.User_roleID = 1
            UNION
            SELECT p.ProjectID, p.CustomName, p.Name 
                FROM projects  p
                WHERE 
                    (p.Responsible_person_id = 1 OR p.Delivery_contact = 1) 
                AND 
                    (SYSTIMESTAMP BETWEEN TO_DATE(p.StartDate,'YYYY-MON-DD') AND TO_DATE(p.EndDate,'YYYY-MON-DD'))
                AND 
                    p.status = 2
            UNION
            SELECT rs.ProjectID, pr.CustomName, pr.Name 
                FROM 
                    responsible_persons rs
                LEFT JOIN 
                    projects pr 
                ON 
                    pr.ProjectID = rs.ProjectID
                WHERE 
                    rs.UserID = 1
                AND 
                    (SYSTIMESTAMP BETWEEN TO_DATE(pr.StartDate,'YYYY-MON-DD') AND TO_DATE(pr.EndDate,'YYYY-MON-DD'))
                AND 
                    pr.status = 2
            UNION
            SELECT p.ProjectID, p.CustomName, p.Name 
                FROM project_users_schedule_dates pusd
                LEFT JOIN projects p 
                ON
                    p.ProjectID = pusd.ProjectID
                WHERE pusd.UserID = 1
                AND
                 (SYSTIMESTAMP BETWEEN TO_DATE(pusd.StartDate,'YYYY-MON-DD')
                AND 
                 TO_DATE(pusd.EndDate, 'YYYY-MON-DD'))
                AND
                    p.status = 2) a
       -- GROUP BY a.ProjectID
        ORDER BY a.CustomName, a.ProjectID

Since here StartDate and EndDate are VARCHAR2 I need to convert to_date but I have no idea what is wrong that I always get error. The format which I store date is 'YYYY-MM-DD' Where I made mistake ? What is wrong here ?

Answer

Responsible_person_id VARCHAR and Delivery_contact also VARCHAR

Then compare them with strings, not with numbers.

(p.Responsible_person_id = '1' OR p.Delivery_contact = '1')

If you compare them to numbers, Oracle tries to convert the column values to a number and the error indicates that not all values in at least one of those columns are proper numbers.

If they are supposed to contain numbers, they should be defined with the NUMBER type.

Advertisement

Answer