Missing right parenthesis-CodePudding

I have 4 tables A, B, C and D and inner join is performed on same column headings. r is a date type column present in table D and fromVar and toVar are two different dates passed as a parameter

SELECT A.m , B.n , B.o , B.p, B.q, C.q
FROM A a 
INNER JOIN B b on B.m = a.m 
INNER JOIN D d ON b.m = d.m
INNER JOIN C c1 on c1.s = a.s 
INNER JOIN C c2 on c2.s = D.s
WHERE C.r BETWEEN TO_DATE(:fromvar,"yyyy-MM-dd" "HH:mm:ss.SSS") AND TO_DATE(:tovar,"yyyy-MM-dd" "HH:mm:ss.SSS")

Getting missing right parenthesis error for this query in oracle

CodePudding user response：

The date format masks should each be a single string enclosed in single quotes, not two strings in double quotes; and you need the Oracle format model elements not Java/C/etc. ones; and if your strings have fractional seconds then you have to treat them as timestamps not dates. So:

to_timestamp(:fromvar, 'YYYY-MM-DD HH24:MI:SS.FF')

If you are comparing against a date column then the fractional seconds won’t be relevant but still need to be handled by the conversion.

CodePudding user response：

If :fromVar and :toVar are being passed in from an external source (i.e. Java, C, etc) then pass them in directly as timestamp values (and not strings):

SELECT A.m , B.n , B.o , B.p, B.q, C.q
FROM   A a
       INNER JOIN B b on B.m = a.m 
       INNER JOIN D d ON b.m = d.m
       INNER JOIN C c1 on c1.s = a.s 
       INNER JOIN C c2 on c2.s = D.s
WHERE  C.r BETWEEN :fromvar AND :tovar

If you must pass them as strings then, as Alex said, use one, single-quoted string with the correct format model:

SELECT A.m , B.n , B.o , B.p, B.q, C.q
FROM   A a 
       INNER JOIN B b on B.m = a.m 
       INNER JOIN D d ON b.m = d.m
       INNER JOIN C c1 on c1.s = a.s 
       INNER JOIN C c2 on c2.s = D.s
WHERE  C.r BETWEEN TO_TIMESTAMP(:fromvar, 'YYYY-MM-DD HH24:MI:SS.FF')
               AND TO_TIMESTAMP(:tovar,   'YYYY-MM-DD HH24:MI:SS.FF')