CASE And GROUP BY problem

cesaru · Mar 4, 2009

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 · Mar 4, 2009

SELECT ... GROUP BY 1

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

cesaru · Mar 4, 2009

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 · Mar 4, 2009

Typed, untested:

Code:

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 · Mar 5, 2009

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.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

CASE And GROUP BY problem

cesaru

Technical User

PHV

MIS

cesaru

Technical User

PHV

MIS

cesaru

Technical User

Similar threads

Part and Inventory Search

Sponsor