I'm trying to use CASE in the group by clause and I'm having a bit of a problem.
for example
SELECT
CASE
WHEN Order_Date IS NULL THEN 'Unknown'
WHEN DATEDIFF(mm,Order_Date, GETDATE()) BETWEEN 0 AND 6 THEN 'Recent'
WHEN DATEDIFF(mm,Order_Date, GETDATE()) BETWEEN 6 AND 18 THEN 'Old'
ELSE 'Ancient'
END AS Recency, count(*)
FROM Orders
GROUP BY
CASE
WHEN Order_Date IS NULL THEN 'Unknown'
WHEN DATEDIFF(mm,Order_Date, GETDATE()) BETWEEN 0 AND 6 THEN 'Recent'
WHEN DATEDIFF(mm,Order_Date, GETDATE()) BETWEEN 6 AND 18 THEN 'Old'
ELSE 'Ancient'
END
does not seem to work with informix. It does with SQL server.
Is there a workaround for this?
TIA
for example
SELECT
CASE
WHEN Order_Date IS NULL THEN 'Unknown'
WHEN DATEDIFF(mm,Order_Date, GETDATE()) BETWEEN 0 AND 6 THEN 'Recent'
WHEN DATEDIFF(mm,Order_Date, GETDATE()) BETWEEN 6 AND 18 THEN 'Old'
ELSE 'Ancient'
END AS Recency, count(*)
FROM Orders
GROUP BY
CASE
WHEN Order_Date IS NULL THEN 'Unknown'
WHEN DATEDIFF(mm,Order_Date, GETDATE()) BETWEEN 0 AND 6 THEN 'Recent'
WHEN DATEDIFF(mm,Order_Date, GETDATE()) BETWEEN 6 AND 18 THEN 'Old'
ELSE 'Ancient'
END
does not seem to work with informix. It does with SQL server.
Is there a workaround for this?
TIA