Re: Is this the sanctioned way to ascertain a table's existence?
Date: Sat, 17 Oct 2009 08:07:53 -0700 (PDT)
Message-ID: <8265e42a-e9fc-4234-a3b1-65c29661645b_at_a7g2000yqo.googlegroups.com>
On Oct 17, 7:24 am, Serge Rielau <srie..._at_ca.ibm.com> wrote:
> Out of principle I would never use COUNT() to check for existence.
> Imagine a bowl of rice. does it require counting the rice grains inside
> the bowl to know whether it is empty or not?
> One glance should be enough.
> In SQL Terms that would be an EXISTS predicate.
> Or if you want to keep it simple in your case
>
> SELECT 1 FROM USER.TABLES WHERE table_name = :table_name AND ROWNUM < 2
>
> In teh case of USER_TABLE by design you may only get one row, but if you
> use COUNT() here chances are someone will copy your code and use it
> somewhere else where there are a million buggers to count.
>
> Cheers
> Serge
>
> --
> Serge Rielau
> SQL Architect DB2 for LUW
> IBM Toronto Lab
There is an American expression that comes to mind in regards to not using a select count(*) to verify existance just because some other developer might copy the code at some future date: you can't fix stupid.
I do not think I would let what someone might do stop me from doing what I need to do. What I might do if Serge's concern came to mind is code a database stored function that perrforms the table exists check and returns 0 or 1 to indicate existance.
Still, I wonder if there is not a better way to determine what SQL to compile. If the application has several distinct features then perhaps an installed feature table by customer could be used to guide the compile.
HTH -- Mark D Powell -- Received on Sat Oct 17 2009 - 10:07:53 CDT