result of ifnull() in mysql

Question

Hello freinds, i want to use the result of ifnull as a third parameter for concate(). my query is like

select x.a,
       x.aa,
       x,bb,
       y.b,
       z.c
      concate(a,aa)
      ifnull((select P.name from p where p.id=x.name ), N.A.) as state
      concate(a,aa,state) // here it says state is ambiguous than i used full table name but than results are different.

Please give your valuable suggestions.

Thanks in advance !!

Answer 1

You cannot use the state alias. You have to repeat your IFNULL() expression:

... CONCAT(a, aa, IFNULL((SELECT p.name FROM p WHERE p.id = x.name), 'N.A.'))

---

In addition, I believe you mean CONCAT() instead of CONCATE(). Also note that N.A. should be wrapped in single quotes: 'N.A.'.

---

Test case:

SELECT CONCAT('1-', '2-', IFNULL((SELECT NULL), 'N.A.'));
+---------------------------------------------------+
| CONCAT('1-', '2-', IFNULL((SELECT NULL), 'N.A.')) |
+---------------------------------------------------+
| 1-2-N.A.                                          |
+---------------------------------------------------+
1 row in set (0.00 sec)

SELECT CONCAT('1-', '2-', IFNULL((SELECT 3), 'N.A.'));
+------------------------------------------------+
| CONCAT('1-', '2-', IFNULL((SELECT 3), 'N.A.')) |
+------------------------------------------------+
| 1-2-3                                          |
+------------------------------------------------+
1 row in set (0.00 sec)