Error - Contains predicates on expression IFNULL

Ramco Ramco

In below colde i am gettimg error - Contains predicates on expression IFNULL

select * from "Result"
	where "InvType" = 13 
and not contains("GSTN", '%NOTAPPLICABLE%')

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Daniel Wright1y ago
Accepted answer
It seems the error in your SQL query is due to the incorrect usage of the "contains" function. The "contains" function is typically used in databases like Oracle, but in your case, it appears to be causing a problem due to the presence of the IFNULL expression in the query. The IFNULL function is generally used to handle NULL values and provide an alternative value if a column contains NULL.

To resolve the error and achieve the desired result, you can adjust your query as follows:
```
SELECT * FROM "Result"
WHERE "InvType" = 13 
AND "GSTN" NOT LIKE '%NOTAPPLICABLE%';
```
In this modified query:

- We replaced the "contains" function with the standard SQL syntax using "NOT LIKE" to check if the "GSTN" column does not contain '%NOTAPPLICABLE%'.

- This adjustment should help you avoid the error related to the "contains" function and filter the results based on your criteria.

Feel free to test this modified query in your database environment, and let me know if you encounter any further issues or if you need additional assistance.
Jayraj Chhaya1y ago
Hi @Ramco Ramco,

The error you are experiencing is likely due to the incorrect usage of the CONTAINS function in conjunction with the IFNULL expression. In SQL, the CONTAINS function is typically used for full-text search and may not support wildcard patterns like '%NOTAPPLICABLE%' directly. Instead, you can use the NOT LIKE operator for pattern matching.
```
SELECT * 
FROM "Result"
WHERE "InvType" = 13
AND "GSTN" NOT LIKE '%NOTAPPLICABLE%'
```