select query modification

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » select query modification

18 posts, Page 1 of 2

1 2 »»

select query modification

Rate Topic

Display Mode

Topic Options

Author

Message

bvinay57

Posted Monday, April 13, 2009 7:00 AM

SSC 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

Florian Reischl

Posted Monday, April 13, 2009 7:11 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, 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

bvinay57

Posted Monday, April 13, 2009 7:19 AM

SSC 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

Florian Reischl

Posted Monday, April 13, 2009 7:23 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, 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

Post #695767

bvinay57

Posted Monday, April 13, 2009 7:35 AM

SSC 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

Florian Reischl

Posted Monday, April 13, 2009 7:40 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

You use the correct function, just swap your parameters and it works Wink

ISNULL(STATE, 'all')

Greets
Flo

Post #695795

bvinay57

Posted Monday, April 13, 2009 7:49 AM

SSC 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 w00t

so wat i have to do

Post #695805

Florian Reischl

Posted Monday, April 13, 2009 7:55 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

Could you please post your SELECT statement?

Greets
Flo

Post #695809

Florian Reischl

Posted Monday, April 13, 2009 7:57 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

BTW: Is your database SQL Server or MySQL?

Post #695812

denglishbi

Posted Monday, April 13, 2009 10:12 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:06 AM
Points: 347, Visits: 1,340

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 »

18 posts, Page 1 of 2

1 2 »»

Permissions