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 >> >> ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
