Using DATE_SUB() based off dynamic field

How does one go abouts using a dynamically created field to be used within a DATE_SUB calculation?

I have this SQL below:

SELECT *, 
        CASE 
            WHEN `currentDate` IS NULL 
            THEN 
                `lastDate`
            ELSE 
                `currentDate`
        END AS `useDate`, 
        CASE 
            WHEN `type` = 'weekly' 
                THEN DATE_SUB(`useDate`, INTERVAL 1 WEEK)
            END AS `nextDate`
    FROM `aTable`

And this does not work, due to error '#1054 - Unknown column 'useDate' in 'field list''

The query works fine if I am using an actual field from the Select *, however won't accept the dynamically created field.

What would the proper way to work with this without having to do a 'case in case' query?

Answers


Did you try to use the CASE clause inside WHEN?

SELECT *, 
        CASE 
            WHEN `currentDate` IS NULL 
            THEN `lastDate`
            ELSE `currentDate`
        END AS `useDate`, 
        CASE 
            WHEN `type` = 'weekly' 
                THEN DATE_SUB(
                        CASE 
                            WHEN `currentDate` IS NULL 
                            THEN `lastDate`
                            ELSE `currentDate` 
                        END AS `useDate`
                    , INTERVAL 1 WEEK)
            END AS `nextDate`
    FROM `aTable`

Need Your Help

Creating “product” tables with div's

html css table

I'm currently working on some web coursework and as you'll notice I lack experience in web development. Basically I'm trying to create tables that hold products for a shop, however I want to use div

Blackberry console output

blackberry logging java-me console

i am new in black berry development, just like console output in J2me development and log cat in android to see the event log of the simulator, is there anything for blackberry so that i can keep a...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.