How to use nested alias to make the query simpler?

Question

Let's say I have this query:`

SELECT
    IF(uPer="Yes", "Y", "N") AS Manage,
    IF(date_check="Yes", "Y", "N") As dCheck,
    IF(dCheck="Yes", "Great", "Not Great") AS firstNested,
    IF(firstNested="Great",1,0) AS secondNested
FROM table

So in this example I have more that 1 alias and I want to use some of them inside other aliases , Like a nested aliases.

How to achieve that without copying/pasting alias inside another alias like:

IF(
   IF(dCheck="Yes", "Great", "Not Great") AS firstNested) ="Great",1,0
) AS secondNested

Note sure if the above is a right syntax.

Answer 1

Try this:

SELECT
    Manage,
    dCheck,
    firstNested,
    IF(firstNested="Great",1,0) AS secondNested
FROM
(
    SELECT
        Manage,
        dCheck,
        IF(dCheck="Yes", "Great", "Not Great") AS firstNested
    FROM
    (
        SELECT
            IF(uPer="Yes", "Y", "N") AS Manage,
            IF(date_check="Yes", "Y", "N") As dCheck
            FROM table
    ) as t1
) as t2

Bug: dCheck result will be Y or N and you are checking Yes in the next test which will result false and firstNested will be Not Great for all the rows.

Your query should be:

SELECT
    Manage,
    dCheck,
    firstNested,
    IF(firstNested="Great",1,0) AS secondNested
FROM
(
    SELECT
        Manage,
        dCheck,
        IF(dCheck="Y", "Great", "Not Great") AS firstNested
    FROM
    (
        SELECT
            IF(uPer="Yes", "Y", "N") AS Manage,
            IF(date_check="Yes", "Y", "N") As dCheck
            FROM table
    ) as t1
) as t2