Solved

Problem with IBX

Posted on 2001-09-18
5
133 Views
Last Modified: 2010-04-06
Hello,
i am using Interbase Express v4.62.
In my table i have some Varchar fields (in some of them there is no text entered)
if i select them with field1 || field2 || field3 and any of these fields has null, the result is null !
But i would like to ignore the fields with null. How ?
0
Comment
Question by:Andi4553
  • 2
  • 2
5 Comments
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6490502
Could you use a query and use select fields from table where field1 not null, field2 not null etc?

The other thing you can do is, when creating the tables, make sure that each field has been created as not null:

create table mytable (
field1 integer not null,
field2 varchar(20) not null,
field3 timestamp not null)

That's how I do mine.  

Cheers,

Stu
0
 

Author Comment

by:Andi4553
ID: 6490637
The Select with the where clausl isn't good because i want to get all records ! Because Select FirstName || ' ' || LastName i would to get all with both First and Last and those who havn't entered first or last.

The second with create table ... i will try if it works how i need it ...
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6491956
As far as I am aware, there is no way of returning all rows and just not showing certain fields depending on the fields condition (like [pseudo] select * from table display all fields unless field is null)

You could do it in code.  Something like:

procdure TForm1.TableTestOnScroll(Dataset: TDataset);
begin
  if not TableTest.FieldByName('Field1').isNull then
     DBEdit1.Datasource := Datasource1
  else
     DBEdit1.Datasource := nil;
end;

But that means you'll never be able to update those null fields.

I think your best bet is putting some constraints in place which help avoid this problem.

Stu.
0
 

Author Comment

by:Andi4553
ID: 6492730
To StuartJohnson:
I dont want this !
My problem is when useing : SELECT Field1 || Field2 || Field3 FROM Table1
if Field1 = "A" Field2 = "B" Field3="C" i get "ABC"
if Field1 = "A" Field2 = "B" Field3 = null i get null but i want "AB"
if Field1 = "A" Field2 = null Field3 = "C" i get null but i want "AC"
0
 
LVL 9

Accepted Solution

by:
ITugay earned 100 total points
ID: 6492889
Hi Andi4553,

the problem is that concatenation's result of  "null" and any string value is always "null". The same situation is for aggregate functions (SUM, AVG ...).
So, you need to convert "null" values to empty string.

update sometable set field1= '' where field1 is null
update sometable set field2= '' where field2 is null
update sometable set field3= '' where field3 is null

Another way is to create calculated field and make concatenation programmatically.

-----
Igor

PS: you can write triggers for your table to change "null" to empty string.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

12 Experts available now in Live!

Get 1:1 Help Now