Ask Your Question
0

How to change fileds order in tables (Libre Base)?

asked 2018-12-13 12:19:26 +0200

skif gravatar image

Hi, I want ti change fields order in the table https://prnt.sc/luevjj (second_field before first_field). Internet says that I can do this only by SQL request such as ALTER TABLE "tb1" ADD COLUMN "second_field" VARCHAR(100) BEFORE "first_field"; but I receive the mistake "Statement does not generate a result set " And I don't want to create new table or somethink else. Thank you)

edit retag flag offensive close merge delete

Comments

why do you want change fields order. You can to fill table uses a form. Field order doesn't matter in any case

kompilainenn gravatar imagekompilainenn ( 2018-12-13 14:59:08 +0200 )edit

I know that it doesn't matter in forms, reports, requests, but I simple want to know if there is some possibility to make flelds to order that I want)

skif gravatar imageskif ( 2018-12-13 17:35:26 +0200 )edit

Please specify which database you are using as this makes a difference in the answer.

Ratslinger gravatar imageRatslinger ( 2018-12-13 17:37:40 +0200 )edit

LibreOffice Base, I have a table "tb1" and want change order of fields - second_field than first_field https://prnt.sc/luevjj

skif gravatar imageskif ( 2018-12-13 17:50:27 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-12-13 19:35:16 +0200

Ratslinger gravatar image

Hello @skif,

I don't believe you understood the question in the comment. In Base you can connect to different databases. The default is HSQLDB embedded & the default with Experimental features turned on is Firebird embedded. You can also connect to many other external databases such as PostgreSQL, MariaDB, MySQL, etc. SQL used in any situation is dependent upon the database being used & not upon Base itself.

Now as with most SQL other than SELECT statements, as yours is an ALTER statement, it must be run from the menu Tools->SQL... and NOT from the Query section of Base.

As for moving the order of fields, if this were Firebird, you could use a statement such as:

Syntax: ALTER TABLE tablename ALTER [COLUMN] colname POSITION <newpos>

Example: ALTER TABLE salary_history2 ALTER old_salary POSITION 2

From Firebird documentation:

The POSITION keyword changes the position of an existing column in the notional “left-to-right” layout of the record. Numbering of column positions starts at 1.

• If a position less than 1 is specified, an error message will be returned

• If a position number is greater than the number of columns in the table, its new position will be adjusted silently to match the number of columns.

Now if using the HSQL embedded (very old version which is currently being replaced with Firebird embedded) moving fields is a tedious process. Yes, you can insert new fields as you have stated in your SQL but there is nothing to move them as you can with some other databases.

Your method would require you to insert the new field, copy the data from the old field & then delete the old field. Time consuming, tedious, error prone and simply unnecessary since placement doesn't matter.

Other databases may again be different.

edit flag offensive delete link more

Comments

Thank you for clear answer! Yes, I use HSQLDB as default. Will be more detailed study the documentation. Thank you)

skif gravatar imageskif ( 2018-12-13 20:37:48 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-12-13 12:19:26 +0200

Seen: 202 times

Last updated: Dec 13 '18