Informix SE Check all fields for NULL

[Ralf Hackmann]

Hello,

I have to check all fields on NULL for several tables in an Informix SE database.

I am doing the following:

Determine the table columns:
select_column.sql:
SELECT colname FROM syscolumns
WHERE tabid = (SELECT tabid FROM) WHERE tabname = "fyar1sta")

dbaccess myDB select_column.sql >> ar1_spalte.txt

select_ar1_NULL.awk:
#! / Bin / awk -f
{printf "SELECT article,% s FROM fyar1sta WHERE% s is null; \ n", $ 1, $ 1}

./select_ar1_NULL.awk ar1_spalte.txt> select_ar1_NULL.sql

The select_ar1_NULL.sql file now contains for each Field from fyar1sta a row with a select query that checks for NULL.


My approach works basically, but I would be interested in how to do this with a stored procedure in Informix SE, where I have no experience with SPL.

Greeting

Ralf

[Ralf Hackmann]

Post by Ralf Hackmann
Hello,
I have to check all fields on NULL for several tables in an Informix SE database.
SELECT colname FROM syscolumns
WHERE tabid = (SELECT tabid FROM) WHERE tabname = "fyar1sta")
dbaccess myDB select_column.sql >> ar1_spalte.txt
#! / Bin / awk -f
{printf "SELECT article,% s FROM fyar1sta WHERE% s is null; \ n", $ 1, $ 1}
./select_ar1_NULL.awk ar1_spalte.txt> select_ar1_NULL.sql
The select_ar1_NULL.sql file now contains for each Field from fyar1sta a row with a select query that checks for NULL.
My approach works basically, but I would be interested in how to do this with a stored procedure in Informix SE, where I have no experience with SPL.
Greeting
Ralf

Art Kagel:
"Ralf:

Please describe the required output for us, that will be more helpful than your script.

Art"

The table fyar1sta hast more than 100 fields (f1, fn, fm)

select_column.sql:
SELECT colname FROM syscolumns
WHERE tabid = (SELECT tabid FROM) WHERE tabname = "fyar1sta")

dbaccess myDB select_column.sql >> ar1_spalte.txt

ar1_spalte:
f1
fn
fm

select_ar1_NULL.awk:
#! / Bin / awk -f
{printf "SELECT artikel,% s FROM fyar1sta WHERE% s is null; \ n", $1, $1}

./select_ar1_NULL.awk ar1_spalte.txt > select_ar1_NULL.sql

Then:
select_ar1_NULL.sql:

select artikel, f1 from fyar1sta where f1 is null
select artikel, fn from fyar1sta where fn is null;
select artikel, fm from fyar1sta where fm is null;

The script select_ar1_NULL.sql shows filds from fyar1sta with NULL data.

My Construction works, but I think it is not professional. I whould like to Know How to make it with a Stored Procedure, so that I can give the Procedure any table as a parameter.

Greeting

Ralf