Solved

Problem with IBX

Posted on 2001-09-18
5
137 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delphi Spellcheck in Webbrowser 1 46
Base1 Encode/Decode 3 86
Tvertscrollbox like a whatsapp layout 5 50
DBCtrlGrid, Delphi, Scroll 8 57
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

763 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