CASE And GROUP BY problem
CASE And GROUP BY problem
(OP)
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
Talk To Other Members
RE: CASE And GROUP BY problem
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: CASE And GROUP BY problem
Thank you very much
one last question if you don't mind. I've been searching for an answer about this for a while.
in sql server I can do a subquery in the FROM...
select item from (select * from ....)
I don't think to be able to do this informix.
my workaround was to do the subquery as temp table then do a select on the temp table.
my problems is this.
table1
item staterecord
item3 100
item3 101
item3 204
item5 105
item5 106
item6 200
item6 201
item9 202
item9 203
table2
staterecord StateName
100 NY
101 NY
105 CT
106 FL
200 CA
201 CA
202 MA
203 ME
204 NY
result
=========
item5 CT
item5 FL
item9 MA
item9 ME
basically I wanted to get the items where states are not the same.
can I do something like without using a temp table?
TIA
RE: CASE And GROUP BY problem
CODE
FROM table1 A,table2 B
WHERE A.staterecord=B.staterecord
AND 1<(SELECT COUNT(DISTINCT StateName) FROM table1 X,table2 Y WHERE X.staterecord=Y.staterecord AND X.item=A.item)
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: CASE And GROUP BY problem
SELECT DISTINCT A.item,B.StateName
FROM table1 A,table2 B
WHERE A.staterecord=B.staterecord
and a.salesgroup = 1
AND (SELECT COUNT(DISTINCT StateName) FROM table1 X,table2 Y WHERE X.staterecord=Y.staterecord and x.salesgroup = a.salesgroup AND X.item=A.item) >1
The above works
but when I change the operator of sales group the query seems to take very very long time. for example
SELECT DISTINCT A.item,B.StateName
FROM table1 A,table2 B
WHERE A.staterecord=B.staterecord
and a.salesgroup > 1
AND (SELECT COUNT(DISTINCT StateName) FROM table1 X,table2 Y WHERE X.staterecord=Y.staterecord and x.salesgroup = a.salesgroup AND X.item=A.item) >1
I even tried do a small range like a.salesgroup > 1 and a.salesgroup < 4.
I really appreciate your help... and thanks for everything.