×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

CASE And GROUP BY problem

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

 

RE: CASE And GROUP BY problem

(OP)
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






 

RE: CASE And GROUP BY problem

(OP)
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.
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close