Question

Syntax error in Continuous query(CQ) to inject NULL value for String field value

  • 16 August 2023
  • 1 reply
  • 44 views

Badge

I am building a CQ to inject NULL values for String data fields that have “Not available” in the source database.

CASE WHEN META(o,"TableName").toString()=="<schema-name>.<table-name>" 
then
CASE WHEN TO_STRING(data[12]) ="Not Available" THEN putUserData(o, 'DENOMINATOR', "NULL")
WHEN TO_STRING(data[13]) = "Not Available" THEN putUserData(o, 'SCORE', NULL)
WHEN TO_STRING(data[14]) = "Not Available" THEN putUserData(o, 'LOWER_ESTIMATE', NULL)
WHEN TO_STRING(data[15]) = "Not Available" THEN putUserData(o, 'HIGHER_ESTIMATE', NULL)
ELSE O

I am receiving the following compile error : 

Error Saving Component

Syntax error at: CASENULL)

Please fix the errors and click "Save" again.

 

I also tried the following syntax

CASE WHEN TO_STRING(data[12]) ="Not Available" THEN NULL
WHEN TO_STRING(data[13]) = "Not Available" THEN NULL
WHEN TO_STRING(data[14]) = "Not Available" THEN NULL
WHEN TO_STRING(data[15]) = "Not Available" THEN NULL

Reference docs : https://www.striim.com/docs/en/handling-nulls-with-cqs.html


1 reply

Userlevel 1
Badge +5

There were a few syntax issues. I corrected the syntax and added comments corresponding to it

SELECT
CASE WHEN META(o,"TableName").toString()=="<schema-name>.<table-name>"
then
CASE WHEN TO_STRING(data[12]) ="Not Available" THEN putUserData(o, 'DENOMINATOR', NULL) -- String Null was used instead Null Keyword
WHEN TO_STRING(data[13]) = "Not Available" THEN putUserData(o, 'SCORE', NULL)
WHEN TO_STRING(data[14]) = "Not Available" THEN putUserData(o, 'LOWER_ESTIMATE', NULL)
WHEN TO_STRING(data[15]) = "Not Available" THEN putUserData(o, 'HIGHER_ESTIMATE', NULL)
ELSE o -- lower case o for Alias
End -- End was missing
Else o -- Else was missing
END -- End was missing
FROM <WAEventOutputStream> o;

In order to handle nulls in a CQ for WAEvent, use Modify functions to transform records.

Reply