Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2008
    Posts
    6

    Unanswered: Help with isNULL

    hello,
    Does someone know why i'm getting this error:


    **** the select for t1****
    )t1
    LEFT JOIN (

    SELECT ROUND(ISNULL(sum(p_sum),0),3) AS Total, fk_id AS id
    FROM abc
    GROUP BY id
    )t2 ON t1.id = t2.id


    and i'm getting this error:

    1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0),3) as Total, fk_id as id
    from abc
    group b' at line 17

    thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the ISNULL function takes only one parameter

    use this instead --

    ... ROUND(COALESCE(SUM(p_sum),0),3)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2008
    Posts
    6
    first of all, thank you for the quick response..

    I changed it, and Now I don't get an error but I still get NULL instead of zero in the Total column

    thanks again..
    suami.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Run the subquery on it's own - any NULLs?

    I'm assuming the LEFT JOIN is what is introducing the NULL values.
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    because it's a LEFT OUTER JOIN

    do the COALESCE in the outer query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    arg, sniped by george again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2008
    Posts
    6
    Sorry but I didn't get you.. if you could please explain what you meant i'll be very thankful..

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you could please post the actual query, i could help you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2008
    Posts
    6
    select * from (SELECT id,
    id_contact,
    id_address,
    name,
    total_price,
    full_com
    FROM sale as s
    INNER JOIN click as c ON s.click=c.click
    INNER JOIN partner as p ON p._id = b.fk_id
    WHERE sale_stat in(1,5)
    Group by p.id) t1
    LEFT JOIN (
    select round(COALESCE(sum(advp_sum),0),3) as Total, fk_id as id
    from abc
    group by id
    ) t2 ON t1.id = t2.id


    thanks.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by suami
    select * from (SELECT id,
    id_contact,
    id_address,
    name,
    Coalesce(total_price, 0),
    full_com
    FROM sale as s
    ...
    Meh .
    George
    Home | Blog

  11. #11
    Join Date
    Oct 2008
    Posts
    6
    But total price is not in t2, and I would like to make the column of adv_sum which if it's null the i want zero instead, sorry for keep asking i guess don't explain my self right,

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Apologies, mis-read your query.
    Change your SELECT * to pick out specific columns. Then apply the Coalesce at that point
    George
    Home | Blog

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by suami
    select * from (SELECT id,
    id_contact,
    id_address,
    name,
    total_price,
    full_com
    FROM sale as s
    INNER JOIN click as c ON s.click=c.click
    INNER JOIN partner as p ON p._id = b.fk_id
    WHERE sale_stat in(1,5)
    Group by p.id) t1
    LEFT JOIN (
    select round(COALESCE(sum(advp_sum),0),3) as Total, fk_id as id
    from abc
    group by id
    ) t2 ON t1.id = t2.id

    this cannot be your real query, because it references a table (b) which doesn't exist


    like i said before, do the COALESCE in your outer query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Oct 2008
    Posts
    6
    Thank you so much for the help!
    got you. worked.

Posting Permissions

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