TransWikia.com

ERROR at line 3: ORA-00911: invalid character" when joining tables

Stack Overflow Asked by sinafarheidar12 on February 19, 2021

I have a working PAINTER and PAINTING table which both work correctly. I am trying to Join them together where the PAINTING.PAINTER_Name (Artist who painted the painting) = PAINTER.AName (Artist Name). The only constraint is that the artist has to be born in the 19th century (Check WHERE Statement).

I have ran this code in I get an error reading "ERROR at line 3: ORA-00911: invalid character WHERE ABDate BETWEEN date’1800-01-01’ AND date’1899-12-29’".

I have attached the table schema and the query i am trying to tun. Any tips?

CREATE TABLE PAINTER    
(AName varchar2 (15),    
ABDate date,    
ADdate date,    
ACountry varchar2 (15),    
constraint pkPAINTER PRIMARY KEY(AName));
   

CREATE TABLE PAINTING    
(Painting_Name varchar2(15),    
Year_Painted varchar2(15),    
Est_Value varchar2(15),    
Museum_Name varchar2(15),    
PAINTER_Name varchar2(15),
constraint pkPainting_Name PRIMARY KEY (Painting_Name),    
constraint fkPAINTING1 foreign key (PAINTER_Name) references PAINTER,    
constraint fkPAINTING2 foreign key (Museum_Name) references MUSEUM);

SELECT PAINTING.Year_Painted, PAINTING.Painting_Name, PAINTER.ABDate
FROM PAINTING JOIN PAINTER ON PAINTING.PAINTER_Name = PAINTER.AName
WHERE ABDate BETWEEN '1800/01/01' AND '1899/12/31'
ORDER BY Painting_Name;

2 Answers

Try using a date literal:

SELECT p.Year_Painted, p.Painting_Name, pr.ABDate
FROM PAINTING p JOIN
     PAINTER pr
     ON p.PAINTER_Name = p.AName
WHERE pr.ABDate BETWEEN DATE '1800-01-01' AND DATE '1899-12-31'
ORDER BY Painting_Name;

EDIT:

Based on your error message:

ERROR at line 3: ORA-00911: invalid character WHERE ABDate BETWEEN date’1800-01-01’ AND date’1899-12-29’".

You are using "fancy" or "smart" single quotes for the dates. The proper delimiter for the date string is a single quote, and it should look like ', not .

Correct answer by Gordon Linoff on February 19, 2021

You should use to_date or date literal to create date from string as follows:

WHERE ABDate BETWEEN DATE '1800-01-01' AND DATE '1899-12-31'

OR

WHERE ABDate BETWEEN TO_DATE('1800/01/01','YYYY/MM/DD') 
                 AND TO_DATE('1899/12/31','YYYY/MM/DD')

-- Update

You have used wrong quotes (tilted quotes - ). You need to use normal single quotes (')

date’1800-01-01’ -- opening and closing quotes are not normal single quotes

Use

date'1800-01-01'

Answered by Popeye on February 19, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP