Thank you for the very clear answer!

I am no big fan of MS and I guess I should not be surprised that MS SQL
allows non-standard constructs. I will need to unlearn some things.

I just looked at this description of SQL-2003 and I see how the From
clause is not optional.

http://savage.net.au/SQL/sql-2003-2.bnf.html

thanks again,

Jim s

On 4/4/2014 1:21 PM, Thomas Beckmann wrote:
> Hi James,
>
> your query has to look like
>
> select <something> from <relation>
>
> Your example queries look like
>
> select <something>
>
> Taking your first example, you may write
>
> SELECT
> case RDB$RELATION_NAME
> when 'SCHEMA_VERSION' then 1
> else 0
> end
> FROM RDB$RELATIONS
>
> to achieve the second.
>
> I'd write
>
> SELECT
> ii(RDB$RELATION_NAME = 'SCHEMA_VERSION', 1, 0)
> FROM RDB$RELATIONS
>
> but this is identical in respect of performance.
>
>
>
> Am 04.04.2014 15:30, schrieb James Schumacher:
>>
>>
>> Hello,
>>
>> I am new to Firebird, I have experience using MS SQL. I am trying to
>> convert some code that performs various checks but having problems with
>> the syntax. One thing I need to convert is SQL to detect whether a
>> table exists.
>>
>> I have tried these in Squirrel and isql connected to an embedded db
>> version Firebird 2.5
>>
>> I tried the following
>>
>> SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
>> WHERE RDB$RELATION_NAME = 'SCHEMA_VERSION'
>>
>> works fine. I went on to:
>>
>> select case when exists(SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
>> WHERE RDB$RELATION_NAME = 'SCHEMA_VERSION') then 1 else 0 end;
>>
>> Error: GDS Exception. 335544569. Dynamic SQL Error
>> SQL error code = -104
>> Unexpected end of command - line 2, column 62
>> SQLState: 42000
>> ErrorCode: 335544569
>>
>> I thought maybe firebird wants named result so I tried
>>
>> select (case when exists(SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
>> WHERE RDB$RELATION_NAME = 'SCHEMA_VERSION') then 1 else 0 end) as
>> result;
>>
>> Error: GDS Exception. 335544569. Dynamic SQL Error
>> SQL error code = -104
>> Unexpected end of command - line 2, column 70
>> SQLState: 42000
>> ErrorCode: 335544569
>>
>> I am now down to some very basic queries that work in MS SQL but not in
>> firebird:
>>
>> SELECT CASE
>> WHEN 4 = 5 THEN 1
>> WHEN 5 = 6 THEN 2
>> ELSE 0
>> END;
>>
>> Error: GDS Exception. 335544569. Dynamic SQL Error
>> SQL error code = -104
>> Unexpected end of command - line 5, column 2
>> SQLState: 42000
>> ErrorCode: 335544569
>>
>> or
>>
>> SELECT (CASE
>> WHEN 4 = 5 THEN 1
>> WHEN 5 = 6 THEN 2
>> ELSE 0
>> END) as result;
>>
>> Error: GDS Exception. 335544569. Dynamic SQL Error
>> SQL error code = -104
>> Unexpected end of command - line 5, column 10
>> SQLState: 42000
>> ErrorCode: 335544569
>>
>> I have looked at the following documentation but I don't see what I am
>> doing wrong
>>
>> http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25.html
>>
>>
>> thank you for any help you can give,
>>
>> Jim s
>>
>>