Error with query in Oracle
I am using Oracle
and using in my toad to check the result. But I am getting error as
ORA-01740: missing double quote in identifier
Here is my request
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 am I going wrong
source to share
This is the actual request (with data) you are 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 software version can be (just gotten from your basic 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 avoidance
SQL*Injection
, you should try using bind variables (I'm not that careful with the syntax.NET
)
source to share
You seem to have an additional one "
at the end of the request:
to_date fnd_conc_date.string_to_date('"txtOutDate.Text"')" -- here
should look like this:
to_date fnd_conc_date.string_to_date('"txtOutDate.Text"')
An initial double quote (") was found without a closing quote. The identifier contains an empty or special character other than $, #, or _ and must be enclosed in double quotes.
source to share
Including comment in response ...
First, you can check if data is actually flowing from the database to your interface. To do this, check out a simplified query for returning data, for example in TOAD. Then just copy the query string into your application code and send it there to the database. Try something line by line:
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 how setup / further information is required.
source to share