• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

Problem with IBX

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
Andi4553
Asked:
Andi4553
  • 2
  • 2
1 Solution
 
Stuart_JohnsonCommented:
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
 
Andi4553Author Commented:
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
 
Stuart_JohnsonCommented:
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
 
Andi4553Author Commented:
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
 
ITugayCommented:
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now