SQL syntax

I’m obviously missing something … but have not yet figured out what it is

  "SELECT a.field1,a.field2,ifNull(CASE b.field3 WHEN '' THEN '*' ELSE b.field3,'?') as field3 " + _
  "  FROM table1 a" + _
  " LEFT JOIN table2 b" + _
  "   ON a.field1=b.field1'" 

Simple left join of two tables…

Table 2 does NOT always have a matching value to Table 1… if it DOESN’t, I need Field 3 to be ‘?’
If there is a match (and there can be more than one, and I want them all), it is possible that Field 3 is ‘’ (NOT a null)
but I need THAT to be in the output a “*”

So Field 3 will have 3 types of values

  • The actual value of Field3 in table 2 if it exists and is NOT ‘’ (again… not null, and there are no nulls)
  • a value of “*”, which indicates there is a corresponding record in table 2, just no specifed value for field3
  • a value of “?” which indicates there is NOT a corresponding match in table 2

SQLite indicates "Syntax error near ‘,’
which I assue is the one right before the IFNULL

Note, for those of you thinking, “Hey he said there were no nulls, yet he uses IFNULL? WTF?”
Remember on a left join, it keeps everything from the “right” table (table1 in this case), and attempts to join things from the “left” table (table2), returning a NULL if there is no match. So my DATA has no nulls, but the join process will create some

arent you missing end of the case ?

yup… figured it out about 20 seconds after you posted.

Correct syntax is

 "SELECT a.field1,a.field2,ifNull(CASE b.field3 WHEN '' THEN '*' ELSE b.field3 END ,'?') as field3 " + _