• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1495
  • Last Modified:

How do I write a CASE statement in BDE Local SQL

Hi Team,

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

Case GROUPCODE
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?

 Cheers
bernard

 
0
BernardGBailey
Asked:
BernardGBailey
  • 3
  • 2
1 Solution
 
rfwoolfCommented:
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
0
 
rfwoolfCommented:
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
0
 
BernardGBaileyAuthor Commented:
Would a subselect work just as well or is that not supported either?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rfwoolfCommented:
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
0
 
Geert GruwezOracle dbaCommented:
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
http://www.microsoft.com/downloads/details.aspx?familyid=413744D1-A0BC-479F-BAFA-E4B278EB9147&displaylang=en
0
 
Geert GruwezOracle dbaCommented:
anyway you have an incorrect syntax in your case too ...

Case
  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"
ELSE
  NULL
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)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now