CODEWITHSUNDEEP
sqlchartio
Steven
Steven

Reputation: 3813

Checking Chartio filter for empty string SQL CASE statement

In my Chartio report, I have a variable filter named TRACKING_GROUP with Control Type of Text Input, with a Default Value of the empty string.

The following SQL works fine..

SELECT
    {TRACKING_GROUP} AS tg,
    (CASE WHEN {TRACKING_GROUP} = '' THEN 0 ELSE 1 END) AS tg_present

..returning..

+----+------------+
| tg | tg_present |
+----+------------+
|    | 0          |
+----+------------+

When I try and cast the text value to an integer in the ELSE condition (which ideally should not be getting reached due to the default value of TRACKING_GROUP being the empty string), using the following SQL..

SELECT
    {TRACKING_GROUP} AS tg,
    (CASE WHEN {TRACKING_GROUP} = '' THEN 0 ELSE 1 END) AS tg_present,
    (CASE WHEN {TRACKING_GROUP} = '' THEN 0 ELSE CAST({TRACKING_GROUP} AS INTEGER) END) AS tg_int

..I get the following error messages:

  • There was an error extracting data for one or more layers
  • Your database returned: Amazon Invalid operation: invalid input syntax for integer: "";

When this filter has a string value of "10557" for example, no errors occur when running the second query listed above, returning...

+-------+------------+--------+
| tg    | tg_present | tg_int |
+-------+------------+--------+
| 10557 | 1          | 10,557 |
+-------+------------+--------+

How can I toggle the casting of this filter to an integer when its value is the empty string since Chartio does not like when that happens?

Upvotes: 1

Views: 326

Answers (0)

Related Questions