Subqueries in SELECT

I've read somewhere that subqueries in the SELECT-part of the SQL Statement should work in Firebird 1.5. But the following example doesn't (and that's really all the sub-querying I've tested):

SELECT templateId, parentTemplate, name, (templateImage IS NULL) As hasImage, (templateData IS NULL) As hasData FROM template

As you can see, all I want to know is if two blob-fields has any data or not.
He gives me the error:
Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 66
IS

Any idea of how to solve this? Since the blob-data can be quite large, loading the data at this stage is not an option.

I think you syntax is simply wrong, you should use a CASE statement to check if the blob field is null and then set a flag accordingly.

Something like this?:
SELECT template.templateId, parentTemplate, name,
CASE templateImage
WHEN NULL THEN 0
ELSE 1
END,
CASE templateData
WHEN NULL THEN 0
ELSE 1
END
FROM template


Cause that doesn't work. Seems he really doesn't appreciate NULL, NOT or IS in the select-part of a query (also tried with IS NULL after WHEN):
Dynamic SQL Error
SQL error code = -104
Token unknown - line 3, char 10
NULL

I even tried inserting this statement:
(templateId IN (SELECT templateId FROM template WHERE templateImage IS NULL)) As hasImage

But of course to no avail. It didn't like IN...

See this thread

I'm having trouble understanding how to use COALESCE in a way such that it returns 1 if the BLOB-field isn't null, and 0 if it is null. FIrst of all, the datatypes are incompatible, but even if it weren't, COALESCE returns the object when it is not null - which would become a much to large dataset to handle.

COALESCE (templateImage, 0) AS hasImage
// Incompatible datatypes
// Returns the data when not null


Or did you mean the function that was desribed in the thread? Don't see how that would help me.

I'm currently considering making a main query with the basic data, and then two more querys - each listing the templateId:s where the specific blob-field is null, and then looping through both lists to see if the current templateId matches any of the templateIds in the two other recordsets. But that's really ugly if you ask me...

The CASE "part" only accepts a column or no expression at all
The WHEN part accepts only values when a column is used in CASE or it can compare an expression, when nothing is specified in the CASE part.
This works:

Worked perfectly. Thanks!