We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Null value --> 'Unknown'

EBuffey
EBuffey asked
on
Medium Priority
255 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!
Comment
Watch Question

What about creating a calculated 'NewValue' field and handling the assignment in the OnCalcField event?

-julio

Author

Commented:
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?
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 :)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.