Solved

How do I write a CASE statement in BDE Local SQL

Posted on 2008-06-11
6
1,382 Views
Last Modified: 2013-11-23
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
Comment
Question by:BernardGBailey
  • 3
  • 2
6 Comments
 
LVL 13

Accepted Solution

by:
rfwoolf earned 500 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
0
 
LVL 13

Expert Comment

by:rfwoolf
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
0
 

Author Comment

by:BernardGBailey
ID: 21765088
Would a subselect work just as well or is that not supported either?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 13

Expert Comment

by:rfwoolf
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
0
 
LVL 37

Expert Comment

by:Geert Gruwez
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
http://www.microsoft.com/downloads/details.aspx?familyid=413744D1-A0BC-479F-BAFA-E4B278EB9147&displaylang=en
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 21863338
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Firemonkey allowing RTL on android 6 29
Select record with the most recent date 14 54
Oracle - Query link database loop 8 37
Query Syntax 17 32
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

785 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