use of exists
A user wanted to check whether a table exists before querying it, and tried "select * from tableforsearch where exists (select * from tableforsearch)", which failed with error -206 (table not found). A reply suggested instead testing the catalog: IF EXISTS (SELECT 1 FROM systables WHERE tabname = 'tableforsearch') THEN SELECT ... END IF. The poster then hit syntax error -201; the explanation was that this IF/THEN block must be run inside a stored procedure, not directly in an SQL editor. That resolved the confusion.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I am trying to develop one query over databases to look for the existence of
one table. I am using the exists clause but I am receiving error 206.
My query is:
select * from tableforsearch where exists (select * from tableforsearch)
Is this the correct way to do it?
How can I do it?
Thanks for any help,
SP
This is one way,
if exists(select 1 from systables where tabname ='tableforsearch')
then
select * from tableforsearch;end if
Thanks for your reply, I already have tried something like that and always get syntax error 201. I am using 12.10.FC10 version... SP
well that's a syntax error, can you share the exact code that gives you that
error? Have you run this code in a procedure?
if exists(select 1 from systables where tabname ='tableforsearch')
then
select * from tableforsearch;end if
This code needs to be run inside of a procedure, if you are trying to run it
in a sql editor window, from a tool like server studio, you will get a -201
syntax error.
Sorry for my mistake, I am SQL newbie :)
Sorry for my mistake, I am SQL newbie :)
No worries, we all start at th ebegining