How do I write a CASE statement in BDE Local SQL

Posted on 2008-06-11
Medium Priority
Last Modified: 2013-11-23
Hi Team,

I'm trying to get Database Tour to run a CASE statement like

WHEN sum(invdtl.linetot) = 0 then '"G0"
WHEN sum(invdtl.linetot) between 1 and 1000 then '"G999"
WHEN sum(invdtl.linetot) between 1000 and 2000 then '"G1999"

End Case as 'GROUPCODE'

I get an error saying

"Invalid use of Keyword
Token: CASE"

I gather "CASE" is not supported in the BDE implementation of local SQL

How should I write this now?


Question by:BernardGBailey
  • 3
  • 2
LVL 13

Accepted Solution

rfwoolf earned 1500 total points
ID: 21765054
You are correct - CASE is not supported by BDE Local SQL.
If you go into your BDE directory (usually at C:\Program Files\Common Files\Borland Shared\BDE you will see a file called LOCALSQL.HLP. If you open it and go to Index -> Unsupported Language it lists "CASE" as unsupported
LVL 13

Expert Comment

ID: 21765063
My advice is to just execute multiple queries according to what you're trying to do.
So do one query, get the result, then in delphi say
if between 0 and 1000 then G999. etc
then build another query using a parameter, and insert G999 into the parameter

Author Comment

ID: 21765088
Would a subselect work just as well or is that not supported either?
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

LVL 13

Expert Comment

ID: 21765133
Hmmm. . i think it is supported in some ways...
Using the help file I told you about, looking up "SubQueries" you can use
the IN predicate
the EXISTS predicate
and it also mentions the SOME/ANY/ALL predicates
LVL 38

Expert Comment

by:Geert G
ID: 21766186
There is still the possibilty of installing a database on your local computer.
Like the MSSQL Desktop Engine, Oracle10G, Interbase, ...

Most of the databases can be run locally, you would then have the full power of a rdbms system at your disposal.

if it's an older computer the 2000 MSDE for example
LVL 38

Expert Comment

by:Geert G
ID: 21863338
anyway you have an incorrect syntax in your case too ...

  WHEN sum(invdtl.linetot) = 0 then '"G0"
  WHEN sum(invdtl.linetot) between 1 and 1000 then '"G999"
  WHEN sum(invdtl.linetot) between 1000 and 2000 then '"G1999"
End Case as 'GROUPCODE'

Removed GROUPCODE from the top
you don't have an ELSE section (not necessary but then this is what you get)

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
From store locators to asset tracking and route optimization, learn how leading companies are using Google Maps APIs throughout the customer journey to increase checkout conversions, boost user engagement, and optimize order fulfillment. Powered …

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question