Solved

Null value --> 'Unknown'

Posted on 1997-10-10
4
221 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
Comment Utility
What about creating a calculated 'NewValue' field and handling the assignment in the OnCalcField event?

-julio
0
 

Author Comment

by:EBuffey
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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

11 Experts available now in Live!

Get 1:1 Help Now