How to set NULL to another value when exporting SQL databases with many columns?

Question

I have a SQL database with approx. 200 columns that I want to export to json which is similar to this:

id  ab1  cd2  ef3  ...
 1    1    0 NULL   
 2    0    1    1
 3 NULL    0 NULL
 4   -9 NULL NULL
....

Normally, I'm happy with MySQL Workbench's export wizard which does this to select:

SELECT ab1,cd2,ef3,... FROM myDatabase

However, when I export to json the normal way, the difference between 0 and NULL is lost, because both the NULL values and the 0 values appear as 0 in the json. So I bit the sour apple and followed this solution to face my problem like so:

SELECT
  COALESCE(id,-9) AS id,
  COALESCE(ab1,-9) AS ab1,  
  COALESCE(cd2,-9) AS cd2,                        
  COALESCE(ef3,-9) AS ef3,
  ...
FROM myDatabase

But the export wizard throws me this error:

Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\workbench\
        wizard_progress_page_widget.py", line 192, in thread_work
self.func()
File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\
      sqlide_power_export_wizard.py", line 264, in start_export
retval = self.module.start(self.stop)
File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\
      sqlide_power_import_export_be.py", line 273, in start
ret = self.start_export()
File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\
      sqlide_power_import_export_be.py", line 601, in start_export
rset = self._editor.executeManagementQuery(query, 1)
DBError: ("You have an error in your SQL syntax; check the manual that corresponds 
           to your MariaDB server version for the right syntax to use near 
          'id,-9) AS id, COALESCE(ab1,-9) AS ab1, COALESCE(cd2,-9) AS cd2, COALESCE(ef3,-9)' 
          at line 1", 1064)
ERROR: Export data to file: ("You have an error in your SQL syntax; check the 
                             manual that corresponds to your MariaDB server version 
                             for the right syntax to use near 'id,-9) AS id, 
                             COALESCE(ab1,-9) AS ab1, COALESCE(cd2,-9) AS cd2, COALESCE(ef3,-9)' 
                             at line 1", 1064)
Failed

I don't know if it's just a syntax error or something else. I already tried just id instead of COALESCE(id,-9) AS ab1, since it's the unique identifier but it didn't work. Affected are integer columns.

It seems reasonable to me that NULL is the default value, but I'm depending on the differentiation mentioned. (I hope the issue has nothing to do with the fact that I am shamefully using MariaDB with MySQLWorkbench.)

How could I change NULL to another value as -9 or NA when exporting my SQL database with 200 columns to json?

Edit:

There is another question that reads similar, but it is actually different, because the answer is regarding a very special case with a WHERE clause. However I have no WHERE clause at all in my code, I want a simple general solution for all columns without a WHERE clause.

Could it be as simple as needing to enclose the `-9` in quotes? ie: `'-9'` — Dave, Mar 11 '19 at 12:10
@RiggsFolly Thanks for your suggestion. Since it didn't help me I've edited my question to differentiate it from the dupe. — jay.sf, Mar 11 '19 at 12:48
It will work for the SELECT columns just as well as on a WHERE clause — RiggsFolly, Mar 11 '19 at 12:49
A table with 200 columns sounds like a bit of a design error, but who knows — RiggsFolly, Mar 11 '19 at 12:50
I'm sorry @RiggsFolly, in the question I show how I tried to adapt your suggestion for the `WHERE` clause to my simpler case, but it won't work. If you don't mind, show me how it works. — jay.sf, Mar 11 '19 at 12:58
You do realise you are using that same alias for the first 2 coalesce commands `COALESCE(id,-9) AS ab1, COALESCE(ab1,-9) AS ab1,` Is that your problem — RiggsFolly, Mar 11 '19 at 13:02
Oh, thanks, fixed! That was just carelessness, it' correct in my real code though. — jay.sf, Mar 11 '19 at 13:05

How to set NULL to another value when exporting SQL databases with many columns?

Edit:

0 Answers0