Eugénio Varejão wrote:
Hello!
I have a problem in a database: the database have two fields CP (postal
code) and Localidade (town) and I want to create a new field appending
the information of these two fields as shows:
CP LOCALIDADE CPC
5030 Cumieira - SMP 5030 CUMIEIRA - SMP
How can I do this in Base? Also how can I have a field always in CAPS?
Hello Eugénio,
Well, to start I'll assume that you are working with an embedded
database. (as opposed to MySQL or PostgreSQL)
I'll also start with the second part of your question and then try to
show how you could look the question in a different way.
"how can I have a field always in CAPS?"
In the database table proper you can not alter input to all caps
(without writing a Trigger in java) but you can create a rule that any
data written to the field be all caps via the Base GUI. To do this you
use the command ALTER TABLE in the SQL window (Tools>SQL)
Let's say your table is named Contato then I would
Open the SQL window and enter the command:
ALTER TABLE "Contato" ADD CHECK( "CPC" = UPPER("CPC") );
You should receive the message "1: Command successfully executed." in
the SQL Window status box. Now any new data will be checked (note new
there, existing data if any will not be checked).
You can find documentation for the ALTER TABLE command at
http://hsqldb.org/web/hsqlDocsFrame.html
Now for the first part of the question.
"I want to create a new field appending the information of these two fields"
OK let me say right off - It is considered quite poor database design to
create a field in a table to store a value derived from existing data in
the table. Rather this derived value would be generated in a view to
your data, either in a Query statement or an actual View object in the
database.
First the query (here I am assuming that you have OpenOffice.org 3.0)
SELECT "CP", "LOCALIDADE", UPPER("CP") || " " || UPPER("LOCALIDADE") AS
"CPC" FROM "Contato"
How to construct that query using the GUI Query Designer.
The easiest way to see this is to:
Go to the Query section in the Base window
Click on "Create Query in SQL view"
Copy and paste the query from above, changing the name of the table to
yours of course.
Now click on the tool bar button "Switch design view on/off"
You can see what that looks like in the GUI designer, it should be
fairly easy to see how you would construct it from scratch.
Anyway I hope that helps - if you really want to create a stored field
for that derived data post back here...I can give you the ugly answer to
that in Base also.. :>)
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
