Solved

Null value --> 'Unknown'

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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…
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 …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

815 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now