Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Null value --> 'Unknown'

Posted on 1997-10-10
4
Medium Priority
?
229 Views
Last Modified: 2010-08-05
This question applies to Local SQL using the Borland Database Engine.

Having used Oracle's SQL*Plus for so long, I feel spoiled when I find that I can't do things such as calculations other than substring, sum, count, min, max, etc.

For instance, surely there is a way to translate any field with a null value into some other value such as 'Undefined'.

Oracle's SQL*Plus has the nvl function:

  select nvl(Field1, 'Undefined') as NewValue
  from Table1

This would assign the value of Field1 to NewValue unless Field1 has a null value. In that case, it would assign 'Undefined' to NewValue.

Obviously, this can be accomplished without a function using a union statement:

  select Field1 as NewValue
  from Table1
  where Field1 is not null

  union

  select 'Undefined' as NewValue
  from Table1
  where Field1 is null

The above query also has a problem in that if 'Undefined' is not the same length as Field1, then the BDE gives an error!
0
Comment
Question by:EBuffey
  • 2
  • 2
4 Comments
 
LVL 5

Expert Comment

by:julio011597
ID: 1347266
What about creating a calculated 'NewValue' field and handling the assignment in the OnCalcField event?

-julio
0
 

Author Comment

by:EBuffey
ID: 1347267
That's a good point! Thanks! I'm still wondering, however, if there is any calculations that can be done in Local SQL! There are all of these Local SQL reserved words such as "if" and "conditional"! Where does one find documentation on these? Do they work in a SELECT statement? Or are they only applicable in another statement?
0
 
LVL 5

Accepted Solution

by:
julio011597 earned 200 total points
ID: 1347268
In fact, there are lots of reserved words, which should be borrowed from the standard SQL. (maybe you could find some documentation on-line, but your Oracle docs should fit as well)

The fact is that you can just use a few of them, as far as Local SQL is involved, and can do nothing with the rest of them - neither in SELECT statements, nor in any others.

Cheers, julio

P.S. since you seem satisfied with my first comment, i'll submit this as an answer :)
0
 

Author Comment

by:EBuffey
ID: 1347269
Thanks for the answer. If you have an idea where I can get documentation for all of the Local SQL reserved words listed in Borland's TI2752 "Local SQL Reserved Words" document, I'd appreciate it! There doesn't seem to be any useful information in the documentation that comes with Delphi 2.0! Maybe you could recommend a good book?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Screencast - Getting to Know the Pipeline

972 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