Solved

Problem with IBX

Posted on 2001-09-18
5
136 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

808 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