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 (AT) gmail (DOT) com wrote:




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



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.

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 (AT) gmail (DOT) com wrote:



Please do not top-post.


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 (AT) gmail (DOT) com wrote:
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?

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

On 18 Jul 2006 09:33:20 -0700, "--CELKO--" <jcelko212 (AT) earthlink (DOT) net>
wrote:

<OT>
Joe, is that you?

Remember the Watcom Compuserve forums?
</OT>

--
Al Balmer
Sun City, AZ