Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    14

    Red face Unanswered: NULL values to '' widthin GROUP BY processing

    Hi,

    I have some left outer joins. The data within the fields is always of varchar or integer type. Querying the data will never send back NULL-values, because the default value of strings is set to an empty string. But in my outer joins there appear NULL-values.

    The queries have to support a Pervasive DB and my MySQL DB. The Pervasive DB groups NULL values and empty strings to one group, but MySQL doesn't. The result looks like this:

    MySQL DB:
    +----------+--------+-------+------+---------+--------+
    | count(*) | midkey | datum | zeit | midnnam | reskey |
    +----------+--------+-------+------+---------+--------+
    | 411 | [NULL] | | | [NULL] | [NULL] |
    | 2 | [NULL] | | | [NULL] | |
    ......


    Pervasive DB:
    +----------+--------+-------+------+---------+--------+
    | count(*) | midkey | datum | zeit | midnnam | reskey |
    +----------+--------+-------+------+---------+--------+
    | 413 | | | | | |
    ........


    So the reskey is grouped by Pervasive to an empty string but not by MySQL. There is a NULL-group and an empty string group.

    Does anyone know a possiblity to set NULL values to empty strings in MySQL GROUP BY queries?

    Thanks for your help,
    Markus

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use COALESCE(fieldname,'')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    14

    Talking

    Hi,

    I'm wondering that COALESCE is accepted in the GROUP BY clause - but it seems to work.

    Thank you for your help,
    Markus

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •