Post reply

select query modification

  • April 13, 2009 at 7:00 am

    #220114

    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.

  • April 13, 2009 at 7:11 am

    #976552

    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

  • April 13, 2009 at 7:19 am

    #976565

    sorry i cant get this i dont want to insert or update the table i just want to use select statement.

    Thanx.

  • April 13, 2009 at 7:23 am

    #976574

    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

  • April 13, 2009 at 7:35 am

    #976592

    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.

  • April 13, 2009 at 7:40 am

    #976600

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

    ISNULL(STATE, 'all')

    Greets

    Flo

  • April 13, 2009 at 7:49 am

    #976608

    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

  • April 13, 2009 at 7:55 am

    #976611

    Could you please post your SELECT statement?

    Greets

    Flo

  • April 13, 2009 at 7:57 am

    #976614

    BTW: Is your database SQL Server or MySQL?

  • April 13, 2009 at 10:12 am

    #976714

    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

  • April 15, 2009 at 3:52 am

    #977935

    Hi

    I just confirm with Dan. In MySQL COALESCE is the way to go:

    COALESCE(STATE, 'all')

    Here a link to the MySQL reference where I just found this:

    http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html

    Greets

    Flo

  • April 15, 2009 at 5:11 am

    #977960

    Hi

    sorry i am using mssql not mysql now can u please tell me how to replace a null value with string.

    Thanx

  • April 15, 2009 at 5:19 am

    #977966

    bvinay57 (4/15/2009)

    Hi

    sorry i am using mssql not mysql now can u please tell me how to replace a null value with string.

    Thanx

    Use ISNULL() or COALESCE() as suggested.

    Either will work just fine. If you are still having trouble then post the query which is causing the problem - it could be that the function is not operating on the entire set of output rows.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • April 15, 2009 at 6:57 am

    #978025

    Hi

    select NULLIF( STATE, 'null') AS state FROM T1 i have used this it is giving empty string in null values but i want to replace the string with 'all'

    can u suggest me

  • April 15, 2009 at 7:09 am

    #978036

    bvinay57 (4/15/2009)

    Hi

    select NULLIF( STATE, 'null') AS state FROM T1 i have used this it is giving empty string in null values but i want to replace the string with 'all'

    can u suggest me

    From BOL: "NULLIF returns the first expression if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a null value of the type of the first expression."

    In your usage of NULLIF above, you're comparing the STATE column to a string containing the word 'null'. It's not the same as NULL. If you have a STATE value of 'null' then the function will return NULL, otherwise it will return any value of STATE, including NULLs and empty strings. NULL values will display as NULL, not as empty strings (unless you're using something like Crystal as a client!)

    NULLIF(), on it's own, is making the problem more confusing for you.

    What's wrong with ISNULL(STATE, 'all') or COALESCE(STATE, 'all')?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply