| 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 |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
