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!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CASE And GROUP BY problem

Status
Not open for further replies.

cesaru

Technical User
Jan 31, 2008
19
US
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

 
SELECT ... GROUP BY 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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






 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top