AcuXDBC Version 8.0.0 Gives Syntax Error on CASE Query

0 Likes

This article describes what to do if you get a syntax error when executing a query containing a CASE expression.

Problem:

Executing a query containing a CASE expression with AcuXDBC Version 8.0.0 results in a syntax error. Does AcuXDBC support CASE?

Resolution:

AcuXDBC Version 8.1 added support for CASE. For versions prior to 8.1, you may use DECODE to obtain the same results. For example, the following two queries are equivalent:


select
patient_name,
decode (animal_type,
'Cat', 'MEOW',
'Dog', 'WOOF',
'Bird', 'TWEET',
'whatever')
as SOUND
from pets;

select
patient_name,
case animal_type
when 'Cat' then 'MEOW'
when 'Dog' then 'WOOF'
when 'Bird' then 'TWEET'
else 'whatever'
end
as SOUND
from pets;

With Version 8.1.0, executing either query against the sample database (created by demo.bat) will return:

PATIENT_NAME         SOUND
-------------------- --------
Cinnamon MEOW
Nutmeg MEOW
Shotzi WOOF
Cinder WOOF
Buster MEOW
Missy TWEET
Kit WOOF
Milo WOOF
Puzzle whatever
Copper WOOF
Scooter MEOW
Scrapper MEOW
Abbie MEOW
Princess whatever
Polly TWEET
Diego TWEET
Alexi TWEET
Hammy whatever
Rodney whatever

The DECODE query will give this result in any version.

Old KB# 14125
Comment List
Related
Recommended