CODEWITHSUNDEEP
sqloracle-database
javanoob
javanoob

Reputation: 6410

Subquery in select statement syntax error

I am trying to run the below query and i keep getting the error:**ORA-01756: quoted string not properly terminated**

SELECT
    InnerTable."Cycle ID",
    (
    SELECT REPLACE(SYS_CONNECT_BY_PATH(CF_ITEM_NAME,'//'),'//','/')
            AS "Path1"
            FROM CYCL_FOLD
            START WITH CF_FATHER_ID = InnerTable."Cycle ID"
            CONNECT BY PRIOR CF_ITEM_ID = CF_FATHER_ID
    ) as "path1",
    InnerTable."CSR/RCQ Name",
    TEST.TS_DESCRIPTION as "Test Case Description",
FROM
(-- few conditions here
) InnerTable INNER JOIN TEST  ON InnerTable."Test Case ID" = TEST.TS_TEST_ID

Can any body tell me what is wrong with the syntax..Thanks so much..

Upvotes: 0

Views: 181

Answers (1)

APC
APC

Reputation: 146199

This error means you have an odd number of single quotes.

The snippet you posted only contains balanced pairs of quotes - in the REPLACE(SYS_CONNECT_BY_PATH(CF_ITEM_NAME() chain. So the problem must lie in the excised part of your query: 

-- few conditions here

Does the redaction include any string literals? If so check them.

This error is easy to spot with an IDE which supports SQL syntax highlighting.

" i don't find any error with the single or double quotes"

Me neither. Hmmm.

There is the possibility that the / is escaping the quote somehow. That will depend on you client settings. In SQL*Plus ESCAPE is off by default and besides the default escape character is \. So your statement would work on my set-up ...

SQL> sho escape
escape OFFSQL> select '//' from dual
  2  /

'/
--
//

SQL> c.//./
  1* select '/' from dual
SQL> r
  1* select '/' from dual

'
-
/

SQL> set escape on
SQL> r
  1* select '/' from dual

'
-
/

SQL>

... but check your client settings anyway.

Upvotes: 1

Related Questions