Question

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

  • 16 August 2023
  • 1 reply
  • 45 views
S
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

S
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


Terms & ConditionsCookie settings