Differences Between MS SQL and Sybase

[EPNICO]

Hello:

I have a query that I wrote in MS SQl 7.0 when I transfered to Sybase it has a big problem with the case statement for some reason it does not like the "AND" is this a restriction in Sybase? Here is the sample Query:

select m. policy_id, ExRtCd=
case when count(distinct m.state_id) > 1 and
when count(distinct m.action_code_adj_value_input) > 1 then '3'

into #ExRtCd_res1
from wcps.period_action_code as m
group by m.policy_id;

This query works fine in MS Sql 7.0 on Sybase it has a problem with the AND. I can comment out the second count and it will run?? Any ideas are welcomed?

 

[qyllr]

hello,

try this...

SELECT m.policy_id,
ExRtCd = CASE
WHEN count(distinct m.state_id) > 1
WHEN count(distinct m.action_code_adj_value_input) > 1
ELSE "3"
END
INTO #ExRtCd_res1
FROM wcps.period_action_code as m
GROUP BY m.policy_id

hth,
q.

 

[EPNICO]

I ran your query:

Got the following error:

Error line 4
Syntax error near "When"

 

[mikewoodhouse]

Check that your Sybase version supports the CASE construct! I believe that CASE is only supported from 11.9.2 onwards.

Sybase SQL hasn't changed much for years and years and years...

 

[EPNICO]

You are right I logged in to sysbase.com the forums section and found out that sybase sql does not support two distincts
at the same time. MS SQL does. Back to the drawing board. Thanks for your help.

 

[foxdev]

The problem is that you have two "WHEN" clauses. Try this:

[tt]select m. policy_id, ExRtCd=
case when count(distinct m.state_id) > 1 and
count(distinct m.action_code_adj_value_input) > 1
then '3'
ELSE "3"
END

[/tt]