编号ORA-01722无效

Question

我在查询中遇到问题，我总是得到这种错误

ORA-01722: invalid number

不管我怎么做都无济于事。查询在这里

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

由于这里的StartDate和EndDate是VARCHAR2，我需要转换为_date，但我不知道哪里出了问题，我总是出错。我存储日期的格式是'YYYY-MM-DD'我哪里搞错了？这里怎么了？

Answer 1

Responsible_person_id VARCHAR和Delivery_contact也是VARCHAR

然后将它们与字符串进行比较，而不是与数字进行比较。

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

如果将它们与数字进行比较，Oracle会尝试将列值转换为数字，但错误表明并非其中至少一列中的所有值都是正确的数字。

如果它们应该包含数字，那么应该使用NUMBER类型来定义它们。