[Solved] Can't alter (reduce) text length in column

[AfTech54]

Not sure why I can't alter text length from 50 to 15 in the ShortName column. I can extend the length, but never reduce it. Each time I try to reduce it I get a warning message. I extended to 100, then I got the message when trying to reduce it to 99.

Couldn't alter the column "ShortName". Should it rather be deleted and the new format attached.

If I copy the table the text length will be adjustable up and down in the new table.

Anyone has any idea what's wrong here?

[UnklDonald418]

In my tests I get that error message when I try changing the Entry Required property for a VARCHAR() column to Yes when there are rows in my table that have NULL in that column.
Either change Entry Required to No or put something in the rows with NULL in that column.

[AfTech54]

Thanks!
Sorry but I have no columns with null and I get the same message with "No". Two strange things; I can extend the length and I can alter the length in a table copy.

[UnklDonald418]

When you attempt to change the length and get the Base error dialog, answer Yes to deleting the the old column and inserting a new one. Base should generate a new error dialog, but this one should display the ALTER TABLE statement that Base used when it attempted to change the table.
Copy that statement and execute it at Tools>SQL. Let HSQL tell you what the problem it has with the statement.

It's quite rare but I recall a few of instances where a table exhibited unusual behavior and the only solution I found was to copy the data into a new table and delete the old one.
In one case I had to create a new database and copy the tables into that.

[AfTech54]

OK and Thanks!

I'll copy the DB and try to solve it as per your recommendation, thanks again.

I don't know why, but if I delete the view "vShortNames_In_Columns", I'm able to alter from 50 to 15 signs. I get the impression that the view has inherited the text lenght from the "Person" table. But I'm a real amateur.

-- Place "Person.ShortName" in order of "PhotoPerson.ID_PhotoPerson" into columns
Select "ID_Photo",

Max(case when POS = 0 then PE."ShortName" end) C0,
Max(case when POS = 1 then PE."ShortName" end) C1,
Max(case when POS = 2 then PE."ShortName" end) C2,
Max(case when POS = 3 then PE."ShortName" end) C3,
Max(case when POS = 4 then PE."ShortName" end) C4,
Max(case when POS = 5 then PE."ShortName" end) C5,
Max(case when POS = 6 then PE."ShortName" end) C6,
Max(case when POS = 7 then PE."ShortName" end) C7,
Max(case when POS = 8 then PE."ShortName" end) C8,
Max(case when POS = 9 then PE."ShortName" end) C9,
Max(case when POS = 10 then PE."ShortName" end) C10,
Max(case when POS = 11 then PE."ShortName" end) C11,
Max(case when POS = 12 then PE."ShortName" end) C12,
Max(case when POS = 13 then PE."ShortName" end) C13,
Max(case when POS = 14 then PE."ShortName" end) C14,
Max(case when POS = 15 then PE."ShortName" end) C15,
Max(case when POS = 16 then PE."ShortName" end) C16,
Max(case when POS = 17 then PE."ShortName" end) C17,
Max(case when POS = 18 then PE."ShortName" end) C18,
Max(case when POS = 19 then PE."ShortName" end) C19
-- Get from sorted table
From
(
Select PO."ID_Photo", PO."ID_Person", PO."Position" POS
From "vPhotoPersonPositionOrder" PO
) A
-- Join to get ShortName
Join "Person" PE
On PE."ID_Person" = A."ID_Person"
-- To get one row for each Photo
Group By "ID_Photo"

[chrisb]

hello AfTech54,

even though the table in question is referenced by a view you should still be able to resize your varchar() column using the GUI.

i recognise the code that you pasted & remember that in the past i was able to help you with your db. i see that you are using the original queries/views.

shortly after our communications i improved the code by integrating the queries & eliminating all views.
once a thread has been marked as solved i am reluctant to offer further support because although rarely perfect the issue has been resolved.

i suggest you download the attachment.

[UnklDonald418]

When you followed my earlier instructions and executed the ALTER TABLE statement at TOOL>SQL you should have received an error message something like:

1: Column is referenced in constraint or view: vShortName_In_Columns in statement [ALTER TABLE "Persons" DROP "ShortName"]

Views are stored in the back end so HSQLDB is fully aware of them and apparently considers them equivalent to a constraint.

[AfTech54]

Thanks again!

Well the help I got from you in the past has been a great one! I'm not sure that the AfTechRevisited is enough. I'm tagging each person with position so I can determine how names will appear (specific order) in the album reports. Therefore you created the two views for me.
I've documented more than 2000 photos (excl. names, places) so far so I'm afraid of mess the 2.5 Mb db up. The 50 sign text length is no big issue for me more than it scares me that the data might be messed up or the db stops to work in the future. I'm not an expert on Base/DBs.
So I would be grateful could you give me step by step instructions to solve it.

[chrisb]

when i read your first post i suspected that the table 'Person' was corrupt, your second post confirmed it.
your table 'Person' is referenced by another table via a foreign key & at least one view.
to delete such tables the foreign key constraint must be dropped along with any views.
i use OpenOffice 4.1.7 on windows 10 & am able to resize varchar() fields in tables like yours.
your signature says Ooo v4.1.2. i wonder if this could be the cause of the problem.

AfTech54 said
I don't know why, but if I delete the view "vShortNames_In_Columns", I'm able to alter from 50 to 15.

this is good news & suggests that the issue is constraint related.
i think you have found your own solution. all you need to do now is recreate the view.

i am sorry that i rushed my initial post without thoroughly reading the thread, your db has more tables than the stripped version that i have.
if you drag & drop the queries from my upload into your db they may still execute, it's a professional solution to the initial issue.

make the effort to update OO & if you have not already done so split your db.
if you have a specific issue & provide adequate info then i am certain that i or others will be willing & able help.

[AfTech54]

Thanks chrisb!

Didn't help to update ooo.

• I deleted the view.
  Altered text length (works)
  Recreated the view
  DB seems to work

I can upload the db to Goggle drive if you want to look into it and then give you access, but in that case I need your email address.

[chrisb]

AfTech54,
i am sorry but i can not collaborate with you on a personal basis any info supplied must be accessible to all users.
this forum is for the benefit of the community. if the community is excluded then there is no benefit.

if your issue is not related to this topic then please start a new thread.
be clear & specific about your aims & issues, show us what you have.
if possible upload an embedded stripped down version of your db. do not upload personal info.
everyone is willing to help within the ethics which govern this forum.

[AfTech54]

I'm really sorry, I didn't understand that all info has to pass the forum.
As I worked around the problem I can wait with that until I run in to another issue. I'll try to add "Solved" in the header.