Error with the query in Oracle

Question

I am using Oracle and using in my toad to check what the result is. But I am getting error as

ORA-01740: missing double quote in identifier

Here is my query

SELECT T1.Project_Id, 
       PROPERTY_NAME Project_name,
       T1.Vehicle_No,
       T1.Creation_date,
       T1.Time_In,
       T1.Time_Out
FROM 
       XXCUS.XX_SUPINV T1
   INNER JOIN XXACL_PN_PROJBUILD_V T2 
       ON T1.Project_Id = T2.Project_id
WHERE  t1.Project_Id = '" + ddlProjectName.SelectedValue + "' 
   AND Creation_date BETWEEN to_date fnd_conc_date.string_to_date('"TxtIndate.Text"') AND 
         to_date fnd_conc_date.string_to_date('"txtOutDate.Text"')"

Please suggest where I am mistaking

is this your final constructed query? `to_date()` has to be like `to_date('20-Jan-2015','DD-Mon-YYYY')` .. your date conversion here is bit confusing — Maheswaran Ravisankar, Jul 04 '15 at 07:57
@MaheswaranRavisankar: Yes, I tried in my Toad but giving error — Nad, Jul 04 '15 at 07:57
There seems to be an unmatched `"` at the very end of your statement... (to start with...) — Abecee, Jul 04 '15 at 08:05
Could you drop the very last `"` from your query, re-run, and share the database's response? I'd expect `to_date` to cause an error as well. What does `fnd_conc_date.string_to_date('"TxtIndate.Text"')` return (value and datatype)? Perhaps you can drop the two `to_date` altogether. And depending on your table structure you might need to qualify `Creation_date` in `AND Creation_date BETWEEN`. — Abecee, Jul 04 '15 at 08:25
@Abecee: I will test it and let you know in some time as the power connection is not working and servers are down — Nad, Jul 04 '15 at 09:12
@Abecee: I tried with this query: `SELECT T1.Project_Id,PROPERTY_NAME Project_name, T1.Vehicle_No,T1.Creation_date,T1.Time_In,T1.Time_Out FROM XXCUS.XX_SUPINV T1 INNER JOIN XXACL_PN_PROJBUILD_V T2 ON T1.Project_Id = T2.Project_id WHERE t1.Project_Id = '" + 409 + "' AND Creation_date BETWEEN to_date fnd_conc_date.string_to_date('"01-jan-2015') AND to_date fnd_conc_date.string_to_date('"01-jan-2012"')` and I am getting error as `Missing keyword` — Nad, Jul 04 '15 at 09:49
@MaheswaranRavisankar: Yes, please help. Still getting the error — Nad, Jul 04 '15 at 09:52
You could try `SELECT T1.Project_Id, PROPERTY_NAME Project_name, T1.Vehicle_No, T1.Creation_date, T1.Time_In, T1.Time_Out FROM XXCUS.XX_SUPINV T1 INNER JOIN XXACL_PN_PROJBUILD_V T2 ON T1.Project_Id = T2.Project_id WHERE t1.Project_Id = 409 AND Creation_date BETWEEN to_date('01-jan-2015', 'DD-MON-YYYY') AND to_date('01-jan-2012', 'DD-MON-YYYY')`. — Abecee, Jul 04 '15 at 10:48

Stanislovas Kalašnikovas · Answer 1 · 2015-07-04T16:22:31.853

1

Seems that you have additional " in the end of query:

to_date fnd_conc_date.string_to_date('"txtOutDate.Text"')" -- here

should be like:

to_date fnd_conc_date.string_to_date('"txtOutDate.Text"')

An initial double quote (") was found without a closing quote. If an identifier contains a blank or special characters other than $, #, or _, it must be enclosed in double quotes.

Documentation

edited Jul 04 '15 at 16:22

answered Jul 04 '15 at 08:27

Stanislovas Kalašnikovas

9,203
4
32
59

can you help me with full query, as still i am getting the same error – Nad Jul 04 '15 at 09:44

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

This is the actual Query (with data) you're trying to execute.

SELECT T1.Project_Id, 
       PROPERTY_NAME Project_name,
       T1.Vehicle_No,
       T1.Creation_date,
       T1.Time_In,
       T1.Time_Out
FROM 
       XXCUS.XX_SUPINV T1
   INNER JOIN XXACL_PN_PROJBUILD_V T2 
       ON T1.Project_Id = T2.Project_id
WHERE  t1.Project_Id = '409' 
   AND Creation_date BETWEEN to_date('01-jan-2015','DD-mon-yyyy') AND 
         to_date('01-jan-2012','DD-mon-yyyy')

You're programmatic version could be ( Just derived from your base version)

 sl =  "SELECT T1.Project_Id, 
           PROPERTY_NAME Project_name,
           T1.Vehicle_No,
           T1.Creation_date,
           T1.Time_In,
           T1.Time_Out
    FROM 
           XXCUS.XX_SUPINV T1
       INNER JOIN XXACL_PN_PROJBUILD_V T2 
           ON T1.Project_Id = T2.Project_id
    WHERE  t1.Project_Id = '" + ddlProjectName.SelectedValue + "' 
       AND Creation_date BETWEEN fnd_conc_date.string_to_date('" + TxtIndate.Text+ "') AND 
             fnd_conc_date.string_to_date('"+ txtOutDate.Text +"')"

To improve readability and avoid SQL*Injection,you should try using bind variables( I am not so thorough with the .NET syntax)

edited May 23 '17 at 12:21

Community

1
1

answered Jul 04 '15 at 09:57

Maheswaran Ravisankar

17,652
6
47
69

The first one is working fine in the oracle, but the second one is giving error as `;` as expected `(fnd_conc_date.string_to_date('"TxtIndate.Text"')` – Nad Jul 04 '15 at 10:02
`string_to_date()` is your function in `asp.net`? – Maheswaran Ravisankar Jul 04 '15 at 10:03
No, i guess it is used in oracle to call dynamically – Nad Jul 04 '15 at 10:04
@nad in that case, you don't need `to_date()` , assuming with the name.. it returns date itself.. – Maheswaran Ravisankar Jul 04 '15 at 10:08
@nad I did my editing with my iPad.. extremely difficult!! this should work now. – Maheswaran Ravisankar Jul 04 '15 at 10:12
Sorry for the trouble, let me check now – Nad Jul 04 '15 at 10:14
For the second one, now I am getting error as `missing keyword`. – Nad Jul 04 '15 at 10:17
Do you have the printed SQL Query .. formed? – Maheswaran Ravisankar Jul 04 '15 at 10:18
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/82365/discussion-between-maheswaran-ravisankar-and-nad). – Maheswaran Ravisankar Jul 04 '15 at 10:18

score 1 · Answer 3 · answered Jul 04 '15 at 12:11

^{Turning a comment into an answer...}

You might want to verify first, the data is actually flowing from the database to your front end. For this, verify a simplified query to return data in, e.g., TOAD. Then just copy the query string to your application code, and send it from there to the database. Try something along the lines of:

SELECT
  T1.Project_Id,
  PROPERTY_NAME Project_name,
  T1.Vehicle_No,
  T1.Creation_date,
  T1.Time_In,
  T1.Time_Out
FROM XXCUS.XX_SUPINV T1
INNER JOIN XXACL_PN_PROJBUILD_V T2
  ON T1.Project_Id = T2.Project_id
WHERE t1.Project_Id = 409
  AND Creation_date BETWEEN TO_DATE('01-jan-2015', 'DD-MON-YYYY')
                    AND TO_DATE('01-jan-2012', 'DD-MON-YYYY')

Please comment, if and as adjustment / further detail is required.

Error with the query in Oracle

3 Answers3