passing DATE to be used in query written in tverticarow component

One Star

passing DATE to be used in query written in tverticarow component

hi
the job is as follows
tfileinputdelimited----->main---->tflowtoiterate---->iterate----->tverticarow
the file has date value 2013-04-04 yyyy-MM-dd
the query in tverticarow is as follows
"delete from XYZ where date_time="+((Date)globalMap.get("row1.date_val"))
i am getting following error
Exception in component tVerticaRow_2
java.sql.SQLException: (4856) ERROR: Syntax error at or near "Apr"
at com.vertica.util.ServerErrorData.buildException(Unknown Source)
at com.vertica.dataengine.VDataEngine.prepareImpl(Unknown Source)
at com.vertica.dataengine.VDataEngine.prepare(Unknown Source)
at com.vertica.dataengine.VDataEngine.prepare(Unknown Source)
at com.vertica.jdbc.SStatement.executeNoParams(Unknown Source)
at com.vertica.jdbc.SStatement.execute(Unknown Source)
at master_project.prepaidkpiconsolidationwithsinglequery_0_1.prepaidkpiconsolidationwithsinglequery.tFileInputDelimited_1Process(prepaidkpiconsolidationwithsinglequery.java:685)
at master_project.prepaidkpiconsolidationwithsinglequery_0_1.prepaidkpiconsolidationwithsinglequery.runJobInTOS(prepaidkpiconsolidationwithsinglequery.java:3914)
at master_project.prepaidkpiconsolidationwithsinglequery_0_1.prepaidkpiconsolidationwithsinglequery.main(prepaidkpiconsolidationwithsinglequery.java:3782)
Caused by: com.vertica.util.ServerException: (4856) ERROR: Syntax error at or near "Apr"

how to use the value in file ??
One Star

Re: passing DATE to be used in query written in tverticarow component

hi
i have similar error the query in tverticarow is as follows
"delete from XYZ where orig_date_time ="+(TalendDate.formatDate("yyyy-MM-dd",(Date)globalMap.get("row1.date")))

but im getting the following error:
Exception in component tVerticaRow_2
java.sql.SQLException: (4286) ERROR: Operator does not exist: date = int
at com.vertica.util.ServerErrorData.buildException(Unknown Source)
at com.vertica.dataengine.VQueryExecutor.executeSimpleProtocol(Unknown Source)
at com.vertica.dataengine.VQueryExecutor.execute(Unknown Source)
at com.vertica.jdbc.SStatement.executeNoParams(Unknown Source)
at com.vertica.jdbc.SStatement.execute(Unknown Source)
at master_project.prepaidkpiconsolidationwithsinglequery_0_1.prepaidkpiconsolidationwithsinglequery.tFileInputDelimited_1Process(prepaidkpiconsolidationwithsinglequery.java:687)
at master_project.prepaidkpiconsolidationwithsinglequery_0_1.prepaidkpiconsolidationwithsinglequery.runJobInTOS(prepaidkpiconsolidationwithsinglequery.java:3916)
at master_project.prepaidkpiconsolidationwithsinglequery_0_1.prepaidkpiconsolidationwithsinglequery.main(prepaidkpiconsolidationwithsinglequery.java:3784)
Caused by: com.vertica.util.ServerException: (4286) ERROR: Operator does not exist: date = int
One Star

Re: passing DATE to be used in query written in tverticarow component

Dear naranibhanu,
Can you try your query as below and try?
"delete from XYZ where date_time='"+((Date)globalMap.get("row1.date_val")) + "'"
Probably you need single quotes at the start and the end of your row.date_value.
Also, your error says "Syntax error at or near "Apr""
That means, your input component probably might be reading the date from the input file in the "dd MON yyyy" format, check your input component's date format as well.
G
One Star

Re: passing DATE to be used in query written in tverticarow component

hi
i tried as u said , its working thank u Smiley Happy
Seventeen Stars

Re: passing DATE to be used in query written in tverticarow component

There are a much better way to do that:
delete from XYZ where date_time=?

and now switch to the Advanced settings and set the prepared statement parameter with index 1 and use as value row1.date_val.
This way you do not need the error prone to string conversion of the date.
One Star

Re: passing DATE to be used in query written in tverticarow component

how to write minus operation result set into a file using tverticarow ???
Below query is executed using tVerticarow component
select * from WTDA_RTG_CHARGE_AREA_CODE_TEMP minus select * from WTDA_RTG_CHARGE_AREA_CODE;

Below is the result of the above query :
CHARGE_AREA_CODE | CHARGE_AREA_NAME | CHARGE_AREA_TYPE | START_TIME_STAMP | STOP_TIME_STAMP | TIME_STAMP | JCCLML_IDENTITY
------------------+------------------+------------------+---------------------+---------------------+---------------------+-----------------
1000 | banu | d | 1858-11-17 00:00:00 | 1858-11-17 00:00:00 | 1858-11-17 00:00:00 | 4545
I want to capture the result into a file, and do further processing .