Professional Web Applications Themes

Using COALESCE for subqueries - MySQL

Hi! I'm trying to set an '1' to all records that return NULL in below query, using COALESCE, but I get errors which way I try...probably a simple one, but I can't find it: SELECT (SELECT '0' FROM customers t1 WHERE t1.id = 107 AND NOT (reg_date < '2006-06-15 18:28' AND reg_date > '2006-06-14 18:28') LIMIT 1 ) AS date_correct, (SELECT '0' FROM orders t1 RIGHT JOIN order_items t2 ON t1.id = t2.order_id WHERE t1.user_id = 107 AND t2.artnr IN (9350, 9560, 9594) LIMIT 1 ) AS artid_correct, (SELECT '0' FROM customers WHERE id = 107 AND country <> 1 ...

  1. #1

    Default Using COALESCE for subqueries

    Hi!

    I'm trying to set an '1' to all records that return NULL in below
    query, using COALESCE, but I get errors which way I try...probably a
    simple one, but I can't find it:

    SELECT
    (SELECT '0'
    FROM customers t1
    WHERE t1.id = 107 AND NOT (reg_date < '2006-06-15 18:28' AND reg_date
    > '2006-06-14 18:28') LIMIT 1
    ) AS date_correct,
    (SELECT '0'
    FROM orders t1 RIGHT JOIN order_items t2 ON t1.id = t2.order_id
    WHERE t1.user_id = 107 AND t2.artnr IN (9350, 9560, 9594) LIMIT 1
    ) AS artid_correct,
    (SELECT '0'
    FROM customers
    WHERE id = 107 AND country <> 1 LIMIT 1
    ) AS country_correct

    Any ideas? In the above query, date_correct and country_correct returns
    NULL, artid_correct returns '0'


    Thanks alot!!

    marty3d Guest

  2. #2

    Default Re: Using COALESCE for subqueries

    marty3d wrote:
    > I'm trying to set an '1' to all records that return NULL in below
    > query, using COALESCE, but I get errors which way I try...
    Does this example help show the right syntax:

    SELECT COALESCE( (...your subquery goes in here...), 1) AS date_correct,
    ....etc.

    Regards,
    Bill K.
    Bill Karwin Guest

Similar Threads

  1. #39553 [NEW]: Add coalesce() and coalesce_strict() functions
    By greg at mtechsolutions dot ca in forum PHP Bugs
    Replies: 0
    Last Post: November 19th, 09:36 AM
  2. Joins or subqueries
    By alexford in forum Coldfusion Database Access
    Replies: 1
    Last Post: April 26th, 02:31 AM
  3. DBI and subqueries in mysql 4.1
    By Marc Lambrichs in forum PERL Modules
    Replies: 2
    Last Post: December 13th, 08:03 PM
  4. Coalesce() in Informix SE
    By preetinder dhaliwal in forum Informix
    Replies: 2
    Last Post: July 29th, 07:46 PM
  5. COALESCE with NULL
    By N.V.Dev in forum IBM DB2
    Replies: 1
    Last Post: July 10th, 08:07 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139