Data was submitted by users to update 2 tables in our database. The data was submitted in excel and a short program was written to update the data in the tables with the data from the excel form. However, the programmer who wrote the code didn't take into account the case (and he's since left the company) and now we have some fields in 2 tables with lower case data when they should be upper case.
Is there a Progress tool, function, keyword that allows you to change the data in the table to upper case? I don't see anything on the help and I'm new to Progress 4GL.
Thanks!
You may check the UPPER function.
If you just need to display the values in uppercased, us a character format like "!(8)".
Thanks for responding so quickly!! Unfortunately, we need the data to be stored in the table in upper-case so we need to convert it. It's not a display issue for us. I appreciate the info!
ASSIGN Table.Field = UPPER (Table.Field).
Thanks! I thought you meant to user UPPER to display the values in upper case. Sorry I misunderstood your first answer.
You are aware that Progress is case-insensitve by default so the case in which it is stored does not affect indexing, comparison tests, etc? The only reason to car is for display ... and except for things like part numbers, where people seem to be stuck with upper case, all upper case is rather old fashioned and dates from when there weren't enough bits in a byte to show lower case letters.
I knew that our reports and processes were working correctly so yes I guess I knew that technically it didn't matter, but our users are complaining about it when they use certain tools to query themselves so our management wanted it fixed.
Thanks for the info!
Point being that it is adequate to fix the display. That may mean more changes now, but it means that you will be protected from bad data in the future.
And, as I say, except for part numbers and the like, mixed case is actually easier to read.
>Point being that it is adequate to fix the display.
I'd rather make sure that the input fixes the data casing. In a properly designed system (do I start to sound like the Dr.?), there should only be one class that writes a single field. Some outputs are hard to control, like SQL. And reporting users won't be pleased by a mix of upper case and lower case in SQL result sets. Some clients might not be able to join that properly.
Yes, I meant to mention that one should also fix the import, unless, of course, this was a one-time event.
But, it is unfriendly to actually require people to press shift if entering the data.
As for upper case versus mixed case, I think it is a question of whether it is a key or not. If it is not being used as a key, i.e., not being used in a join, then mixed case reads more easily. Part names and customer names etc. should be mixed case. Standardizing on part numbers and customer ids being all upper is fine.
Thanks to both of you. Maybe a further explanation is in order. We have a product purchased from a vendor so they control most of the design and code. However, we purchased rights to their code so that we could make modifications for our environment when necessary. Part of that allows us to create new, user defined tables within the same database that our vended product works. It was within these user defined tables that the programmer didn't consider case. So there was an initial import of data from another system into these tables and then we also made program changes to some of the existing screens provided by our vendor so that the users could maintain this data from here on out and the old system went away. If they add new data, it is stored correctly in upper case. The imported data however is in lower case (and there is quite a lot of it) and they want it fixed so that all of the data is in the same format. Some users are using Toad and some are exporting data into excel and doing their own manipulations for reports etc and they just want it to be consistent in case.
So I agree that design etc comes into play, this was just a mistake that wasn't caught as being an issue during testing and we just wanted a quick way to fix it.
Thanks again for the quick replies!
Illustrates the importance of thorough testing!
And, of course, it also brings up the question of good strategies for working with vendor code and yet providing local modifications. So often this leads to people coding themselves into an island where they can no longer accept a new version of the vendor's application because it would be too expensive to re do all the modifications. This is a very difficult problem which few APs and few end users manage well ... but also way out of scope of this thread!
Lucky for you this is a one time deal since you then just need to fix the data. A few FOR EACHes and you should be done.
Well I didn't realize a simple question would involve so many branches or topics!
I can't address the testing since I wasn't involved on the project at that time. We had a new developer who worked on it and has since left. As for coding ourselves into an island, don't worry, we aren't that stupid. We have a team who has worked with this vendor's code for over 10 years now, although I myself am somewhat new. I am the only one in the office today so I couldn't ask co-workers about a solution and thought I would post a quick question so I could continue with my work.
As for vendor releases, they have developed a very clear process for merging our changes in with the vendor releases and it doesn't take too long, therefore the expense is minimal. They also work with our vendor in cases where our changes could benefit other customers, to get the changes implemented as part of a future release.
Sounds like you are luckier than most in your vendor and smarter than most in the way your work!