Solved

Null value --> 'Unknown'

Posted on 1997-10-10
4
224 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 50 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delphi cmd execution 6 67
Can Viruses spread while transferring Binary data with Winsock 2 86
control image tags in a string ? 12 142
How to build JSON File in Delphi 6 3 45
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…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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