Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

select query modification Expand / Collapse
Author
Message
Posted Monday, April 13, 2009 7:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 17, 2009 1:27 AM
Points: 34, Visits: 99

Hi
i am a newbie to mysql i am generating birt reports in table. state is one of the fieldname of my table. state contains null values along with that some strings. But i dont want this null values in the table to display in the place of null values i want to give one common string value so i changed the select statement as follows SELECT advid ,country,IS NULL('all',STATE ) FROM T1
but i noticed that whole state values has been changed to string 'all' but i want only null values to change.try to solve this.
Thanx.
Post #695726
Posted Monday, April 13, 2009 7:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Hi

I'm not sure if your "mysql" was a typo... If you mean mssql try this:
DECLARE @t TABLE (txt VARCHAR(30))

INSERT INTO @t
SELECT 'abc'
UNION ALL SELECT NULL

SELECT ISNULL(txt, ' ')
FROM @t

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #695743
Posted Monday, April 13, 2009 7:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 17, 2009 1:27 AM
Points: 34, Visits: 99
sorry i cant get this i dont want to insert or update the table i just want to use select statement.
Thanx.
Post #695758
Posted Monday, April 13, 2009 7:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
The INSERT was just for some sample data. I'm not sure if you want to eliminate the NULL values within your SELECT or on client side. If you want to remove the NULLs in your select you can use:

SELECT ISNULL(<AnyColumn>, 'OtherValue')

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #695767
Posted Monday, April 13, 2009 7:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 17, 2009 1:27 AM
Points: 34, Visits: 99
I'm sure i want to eliminate the NULL values within my SELECT i have used this code given by u ISNULL('all',STATE ) it is replacing whole null values to 'all' but in this state field not null values are also there.By ur statement not null values r also changed to given string 'all'. My problem is to replace only null values i want to keep those not null values to same.
Post #695786
Posted Monday, April 13, 2009 7:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
You use the correct function, just swap your parameters and it works

ISNULL(STATE, 'all')

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #695795
Posted Monday, April 13, 2009 7:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 17, 2009 1:27 AM
Points: 34, Visits: 99
Hi
i have tried this ISNULL(state,'all') but it is giving null values as it is wen i tried this ISNULL('all',state ) not only null values whole field values r changing so wat i have to do
Post #695805
Posted Monday, April 13, 2009 7:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Could you please post your SELECT statement?

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #695809
Posted Monday, April 13, 2009 7:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
BTW: Is your database SQL Server or MySQL?


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #695812
Posted Monday, April 13, 2009 10:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 7:25 AM
Points: 347, Visits: 1,344
Instead of using ISNULL I would recommend using COALESCE instead. This is an ANSI standard. You might need to verify that the values are actually NULL and not just empty or ''. Try using the COALESCE instead and I would recommend using this going forward.

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Post #695925
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse