Solved

Problem with IBX

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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
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…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

688 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