Hello Friends,
I am not sure if this is the right forum to discuss OTL code related problems.
If not, then please advise in which forum can I post OTL code related queries.
I have a code which does not work if I pass an OTL date to the SQL and if the passed value is compared with another date but by using minus (-) sign.
It works fine if I use = sign.
In the database :-
CREATE TABLE TEST1(A NUMBER(9),B VARCHAR2(20), DATE1 DATE);
In the OTL code :-
otl_connect *db1::db1OtlConnect = NULL;
otl_datetime dt1;
dt1.year = 2019;
dt1.month = 11;
dt1.day = 10;
static std::string SQL1 =
" SELECT A,B,DATE1 "
" FROM TEST1 "
" WHERE TRUNC(:dDatetime<timestamp>) - TRUNC(DATE1) > 10 "; /*****THIRD LINE IN SQL*****/
TRYBLOCK
{
if (db1OtlConnect == NULL)
{
db1OtlConnect = JBLdbCxnManager::getConnection(); /* JBLdbCxnManager is an internal library of my company and works fine without any issues */
}
static otl_stream otlSelectModeStreamA(1, SQL1.c_str(), *db1OtlConnect);
otlSelectModeStreamA << dt1;
while (!otlSelectModeStreamA.eof())
{/* Process columns A,B, and DATE1*/}
}
catch (otl_exception& p)
{
/* print p */
}
This gives me the error :-
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
If 3rd line in SQL is replaced with :-
" TO_CHAR((:dDatetime<timestamp>) - TRUNC(DATE1)) > 10 ";
then I get the error :-
ORA-00932: inconsistent datatypes: expected CHAR got DATE
If 3rd line in SQL is replaced with :-
" WHERE TRUNC(:dDatetime<timestamp>) >= TRUNC(DATE1) ";
then it works fine.
Please help/advise.
Thanks.
[Updated on: Tue, 05 November 2019 07:13]
Report message to a moderator