View previous topic :: View next topic |
Author |
Message |
priyam Beginner
Joined: 05 Jul 2006 Posts: 16 Topics: 12
|
Posted: Wed Aug 30, 2006 3:21 am Post subject: Query to fetch all records with no NULL values in it |
|
|
I need a query to fetch all the records that fetch valid records with completely no Null values in it.
Ex:
Select * from table -> Selects all rows from table.
Select * from table where column1 <> null -> selects all rows except for those null values in column1.
Similarly, if there are 100 columns |
|
Back to top |
|
|
shekar123 Advanced
Joined: 22 Jul 2005 Posts: 528 Topics: 90 Location: Bangalore India
|
Posted: Wed Aug 30, 2006 3:39 am Post subject: |
|
|
priyam,
You need to check for individual columns for NOT NULL values:
Code: |
SELECT * FROM TABLE WHERE COL1 <> NULL OR COL2 <> NULL OR ....
|
_________________ Shekar
Grow Technically |
|
Back to top |
|
|
firoze Beginner
Joined: 25 Aug 2006 Posts: 2 Topics: 0
|
Posted: Wed Aug 30, 2006 4:31 am Post subject: |
|
|
Not "OR" Its "AND" |
|
Back to top |
|
|
shekar123 Advanced
Joined: 22 Jul 2005 Posts: 528 Topics: 90 Location: Bangalore India
|
Posted: Wed Aug 30, 2006 6:29 am Post subject: |
|
|
firoze,
Can you let me know why OR will not fit in the query as anyway we will retrieve NOT NULL values only ? Please let me know your thoughts. _________________ Shekar
Grow Technically |
|
Back to top |
|
|
priyam Beginner
Joined: 05 Jul 2006 Posts: 16 Topics: 12
|
Posted: Wed Aug 30, 2006 6:53 am Post subject: |
|
|
If there are 100 columns it will be very difficult to give as
WHERE C0L1<> NULL,COL2<>NUL... COL100<>NULL.
Can you tell me any other way to do this? |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
|
Posted: Wed Aug 30, 2006 8:39 am Post subject: |
|
|
priyam,
Here is an alternative way to get the results. Even though the table has more than 1000 columns there might be only few columns which allow null. All the column attributes are stored in sysibm.syscolumns DB2 catalog table. So we pull off the information from this table, so that you don't have to code all the column names.
Code: |
//STEP0100 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIAUL) -
PARMS('SQL') -
LIB('xxxx.RUNLIB.LOAD')
/*
//SYSREC00 DD DSN=your syscol unload dsn,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(1,1),RLSE)
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT (CASE WHEN (COLNO = 1 AND (NULLS = 'N' OR NULLS = 'Y'))
THEN CHAR(' WHERE ') ||
CHAR(NAME) ||
CHAR(' IS NULL')
WHEN COLNO > 1 AND NULLS = 'Y'
THEN CHAR(' OR ') ||
CHAR(NAME) ||
CHAR(' IS NULL')
ELSE CHAR(' ')
END)
,CHAR(' ',46)
FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME LIKE 'your tablename%'
AND TBCREATOR = 'xx'
AND ((COLNO = 1 AND NULLS IN ('N', 'Y'))
OR (COLNO > 1 AND NULLS = 'Y'))
ORDER BY 1 DESC
;
/*
|
The output of this job will be as follows
Code: |
WHERE col_1 IS NULL
OR col_5 IS NULL
OR col_67 IS NULL
|
Now use this as input to extract data using the following JCL
Code: |
//STEP0100 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIAUL) -
PARMS('SQL') -
LIB('xxxx.RUNLIB.LOAD')
/*
//SYSREC00 DD DSN=your table unload dsn,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(x,y),RLSE)
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT * FROM tablename
// DD DSN=your syscol unload dsn from step above,disp=shr
// DD *
;
|
Hope this helps...
Cheers
Kolusu _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
|
|