How to use row default if string is empty or undefined?

[Rupur]

Hi guys, lets say I have this query:

INSERT INTO customers (id, name)
VALUES ({{ listbox1.selectedItem.id }}, {{ textInput1.value }});

However my issue is, that the value of listbox1.selectedItem.id can sometimes be an empty string. In that case I want to use the Default row statement from the table instead.

I have tried a simple if/else like this:

{{ listbox1.selectedItem.id == '' ? DEFAULT : listbox1.selectedItem.id }}

But that produces an error: message:"invalid input value for enum art_enum_9c0110bb: """

What am I doing wrong? How can I get the SQL statement to insert the Default row value if the given string is empty?

[ScottR]

Where are you using:
{{ listbox1.selectedItem.id == '' ? DEFAULT : listbox1.selectedItem.id }} ?
In the query or a component?
Also, have you tried using CASE in the query?

[Rupur]

Hi Scott, I am using it in the query. I haven't tried CASE yet as I thought it would be easier through javascript.

[Rupur]

Hi Scott, I have tried using CASE but then I get this:

SELECT
  CASE
    WHEN {{ listbox1.selectedItem }} = null THEN DEFAULT
    ELSE {{ listbox1.selectedItem.id }}
  END AS testvariable
FROM customers LIMIT 1;

Error: "DEFAULT is not allowed in this context"

[ScottR]

First:
WHEN {{ listbox1.selectedItem }} = null
should be
WHEN {{ listbox1.selectedItem }} IS NULL

What is DEFAULT? Is it a number?

[joeBumbaca]

Hey @Rupur! The default value set on the column in the DB will be used if there is no value for that column passed in the insert query. I think the root issue here is that you are always passing some value for the id column in the query which is then never using the default, and occasionally causing syntax errors due to data type mismatches. I'd use a JS query to determine which case the data currently satisfies and trigger an appropriate SQL query. My setup is different than yours, but the underlying structure should be the same.

1. DB column setting
   

   

2. JS query, checks the value of a component and triggers the appropriate SQL query. For your data, you'll want to check whether there is an id or not.
   

   

3. SQL queries, note that when I want to use the default value, I do not pass age into the insert query at all.

4. Component setup with switch toggled on and off
   
   

5. DB results
   
   

Let me know if you have any other issues!