SEO

Hi,

Assume I have a table with only 1 row and all column is 'int'. I just
wonder is it possible to use SQL to check if any one of those column
contain a 'null' without knowing the column name?

For example,

create t1 ( i1 int null, i2 int null ....)
insert into t1 (i1, i2, ...) values (1, 1, 1, ... , null, 1, ... null,
...)

and check if any of those column contain null...


Have fun,
Willie

wilson.sh.tam@gmail.com wrote:



> Assume I have a table with only 1 row and all column is 'int'. I just
> wonder is it possible to use SQL to check if any one of those column
> contain a 'null' without knowing the column name?

> For example,

> create t1 ( i1 int null, i2 int null ....)
> insert into t1 (i1, i2, ...) values (1, 1, 1, ... , null, 1, ... null,
> ..)

> and check if any of those column contain null...


Select from t1 where i1 is null or i2 is null or ....... in is null



Paul...





> Have fun,
> Willie

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

Hi Paul,

Thanks for your reply. Yes, I should have mention this. The databases
are mysql 5.0.22 and postgresql v7.4.13. I am running them on FreeBSD
6.1 release box.

Paul wrote:
> wilson.sh.tam@gmail.com wrote:
>
>
>
> > Assume I have a table with only 1 row and all column is 'int'. I just
> > wonder is it possible to use SQL to check if any one of those column
> > contain a 'null' without knowing the column name?
>
> > For example,
>
> > create t1 ( i1 int null, i2 int null ....)
> > insert into t1 (i1, i2, ...) values (1, 1, 1, ... , null, 1, ... null,
> > ..)
>
> > and check if any of those column contain null...
>
>
> Select from t1 where i1 is null or i2 is null or ....... in is null
>
>
>
> Paul...
>
>
>
>
>
> > Have fun,
> > Willie
>
> --
>
> plinehan __at__ yahoo __dot__ __com__
>
> XP Pro, SP 2,
>
> Oracle, 9.2.0.1.0 (Enterprise Ed.)
> Interbase 6.0.1.0;
>
> When asking database related questions, please give other posters
> some clues, like operating system, version of db being used and DDL.
> The exact text and/or number of error messages is useful (!= "it didn't work!").
> Thanks.
>
> Furthermore, as a courtesy to those who spend
> time analysing and attempting to help, please
> do not top post.

wilson.sh.tam@gmail.com wrote:


> Thanks for your reply. Yes, I should have mention this. The databases
> are mysql 5.0.22 and postgresql v7.4.13. I am running them on FreeBSD
> 6.1 release box.


Please do not top-post.

> > Select from t1 where i1 is null or i2 is null or ....... in is null


That of course should be Select <my_field_list> from t1... &c.


I don't think the platform should matter that much for this query - it
is more important when talking about performance/installation issues.


BTW, just as a matter of interest, how is FreeBSD 6.1?


Paul...


--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

If you know the number of columns of your table t1.
Though it may a little troublesome, it is easy.

But, if you don't know the number of columns or you want to make it
generally.
I think you should check System Catalog or any other way(I think all
DBMS have some way to know column names, column sequences and other
attributes) to know the number of columns. Then dynamically construct
SQL statement.

For example:
Assume t1 have 7 columns.

SELECT SUBSTR('No NULL NULL exist',1+10*SIGN(COUNT(*)-COUNT(t1)),10)
"Check NULL"
FROM (SELECT CASE n
WHEN 1 THEN c1
WHEN 2 THEN c2
WHEN 3 THEN c3
WHEN 4 THEN c4
WHEN 5 THEN c5
WHEN 6 THEN c6
WHEN 7 THEN c7
END AS t1
FROM (SELECT * FROM t1) S (c1,c2,c3,c4,c5,c6,c7)
, (VALUES 1, 2, 3, 4, 5, 6, 7) P (n)
) T (t1)
;

The result will be:
1)
Check NULL
----------
NULL exist

1 record(s) selected.

or
2)
Check NULL
----------
No NULL

1 record(s) selected.

wilson.sh.tam@gmail.com wrote:
>> just wonder is it possible to use SQL to check if any one of those column contain a 'null' without knowing the column name? <<

Paul's answer
"Select from t1 where i1 is null or i2 is null or ....... in is null"
need to know column names(i1, i2, ..., in).

wilson,
You wrote "without knowing the column name". I want ask you the Paul's
answer satisfy your requirements?

>> Assume I have a table with only 1 row and all column is INTEGER. I just wonder is it possible to use SQL to check if any one of those column contain a 'null' without knowing the column name? <<

SELECT 'yes'
FROM Foobar
WHERE (c1 + c2 + c3+ .. + cn) IS NULL;

On 18 Jul 2006 09:33:20 -0700, "--CELKO--" <jcelko212@earthlink.net>
wrote:

>>> Assume I have a table with only 1 row and all column is INTEGER. I just wonder is it possible to use SQL to check if any one of those column contain a 'null' without knowing the column name? <<
>
>SELECT 'yes'
> FROM Foobar
>WHERE (c1 + c2 + c3+ .. + cn) IS NULL;

<OT>
Joe, is that you?

Remember the Watcom Compuserve forums?
</OT>

--
Al Balmer
Sun City, AZ

On 18 Jul 2006 09:33:20 -0700, --CELKO-- wrote:
>>> Assume I have a table with only 1 row and all column is INTEGER. I just wonder is it possible to use SQL to check if any one of those column contain a 'null' without knowing the column name? <<
>
> SELECT 'yes'
> FROM Foobar
> WHERE (c1 + c2 + c3+ .. + cn) IS NULL;

Now THAT is clever.

Gold star for you.

--
"The last refuge of the insomniac is a sense of superiority to the
sleeping world."
--Leonard Cohen, The Favourite Game