Hi All,
I'm trying to impletement a Logstash pipeline using the JDBC plugin to fetch events from an Oracle database, but I'm receiving following errors.
Java::JavaSql::SQLSyntaxErrorException: ORA-00936: missing expression:
Pipeline input configuration:
input {
jdbc {
jdbc_driver_library => "/usr/share/logstash/ojdbc6.jar"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
jdbc_connection_string => "jdbc:oracle:thin:@...."
jdbc_user => "...."
jdbc_password => "...."
statement => "SELECT
'230' AS "plantCode",
TRIM(CycleCountingLine.t$item)AS "itemCode",
TRIM(GeneralItemData.t$dsca) AS "itemDesc",
CycleCounting.t$cwar AS "Warehouse",
'' AS "tmlArea",
to_char(new_time(CycleCountingLine.t$trdt,'GMT','CST'),'YYYY-MM-DD HH24:MI:SS') AS "Timestamp",
to_char(new_time(CycleCountingLine.t$cdat,'GMT','CST'),'YYYY-MM-DD HH24:MI:SS') AS "creationDate",
DECODE(CycleCounting.t$otyp,1,'Cycle Counting',2,'Adjustment') AS "orderType",
'Inventory Adjustment' AS "transactionType",
CycleCounting.t$orno AS "Order",
CASE
WHEN CycleCountingLine.t$vstk > 0 THEN 'POSITIVE'
ELSE 'NEGATIVE'
END AS "Signal",
ABS(CycleCountingLine.t$vstk) AS "absoluteQty",
CycleCountingLine.t$vstk AS "Qty",
CycleCountingLine.t$cstk AS "qtyAfterTransaction",
'' AS "Type",
ItemCostingData.t$ecpr$1 AS "unValueUSD",
CycleCountingLine.t$vstk * ItemCostingData.t$ecpr$1 AS "totalValueUSD",
ItemCostingData.t$ccur AS "currencyType",
ItemWarehousingData.t$abcc AS "abcCode",
UserData.t$user AS "userName",
CycleCounting.t$recd AS "reasonCode",
Reasons.t$dsca AS "reasonCodeDesc",
'' AS "Comments",
'mycity' AS "destCityName",
'mycode' AS "destCityCode",
'MX' AS "destCountry",
'23.346482 -131.31342' AS "destLocation"
FROM
bn.twh550391 CycleCounting,
bn.twh551391 CycleCountingLine,
bn.ttcd001391 GeneralItemData,
bn.ttcd007391 ItemCostingData,
bn.twmd400391 ItemWarehousingData,
bn.ttcm001391 Employees,
bn.tttd200000 UserData,
bn.ttcs005391 Reasons
WHERE
CycleCounting.t$orno = CycleCountingLine.t$orno
AND CycleCountingLine.t$item = GeneralItemData.t$item
AND CycleCountingLine.t$item = ItemCostingData.t$item
AND CycleCountingLine.t$item = ItemWarehousingData.t$item
AND CycleCounting.t$emno = Employees.t$emno
AND TRIM(Employees.t$nama) = TRIM(UserData.t$name)
AND CycleCounting.t$recd = Reasons.t$cdis"
}
}
Can someone please help me on this.
thank you