- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: how to check for null values in 24 columns in ...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-17-2006 02:56 AM
тАО05-17-2006 02:56 AM
The problem is if there is a NULL value in the column it will break the app.
I have put in a service request with Vendor, but in the mean time I would like to keep on top of this. The thing is searching thru the 24 columns for NULL values, and if NULL replace with '0'.
How would I write the sql for this?
I got for one column but would I add the other 24?
Need to set values of 0 for anything today and onward... Is sysdate right to use?
update table
set column1 = '0'
where column1 IS NULL
AND appt_date => sysdate
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-17-2006 03:25 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-17-2006 04:01 AM
тАО05-17-2006 04:01 AM
Re: how to check for null values in 24 columns in a table
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-17-2006 04:41 AM
тАО05-17-2006 04:41 AM
Re: how to check for null values in 24 columns in a table
to avoid further problems, you should add NOT NULL constraint to the column after updating all values.
update TAB set column1 = '0' where column1 IS NULL;
commit;
alter table TAB modify (column1 not null);
... for all columns to change
Volker
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-17-2006 12:52 PM
тАО05-17-2006 12:52 PM
Re: how to check for null values in 24 columns in a table
If you need to select to list all the rows with NULL column values, then
select * from table
where col1 is null or
col2 is null or
col3 is null or
like that for all the columns.
or if you need to know how many rows to see that you update the same number of rows, then select count(*) from table where
col1 is null or
col2 is null or
like that for all the columns.
You could exclude the primary key columns as they will not be null.
To update the columns with '0' that are null use the nvl as peter suggested. You can verify the update count with the above select count.
Indira A
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-18-2006 02:18 AM
тАО05-18-2006 02:18 AM
Re: how to check for null values in 24 columns in a table
If you need to find them, then selecting them specificly is the only way to do it. If you want to just get rid of them:
alter table tableA modify (column1 varchar2(..) default ' ' not null);
That will update the column with a ' ', as well as setting it to NOT NULL.
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-18-2006 03:28 AM
тАО05-18-2006 03:28 AM