CASE And GROUP BY problem

[cesaru]

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

 

[PHV]

SELECT ... GROUP BY 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cesaru]

oh you are good!

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

 

[PHV]

Typed, untested:

SELECT DISTINCT A.item,B.StateName
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
FAQ181-2886

 

[cesaru]

This is great! Hoewever, if i change the criteria in the first group, the query seems to take very long.

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.