Question about converting old fields to new field in query

[Zerex71]

Greetings once again,

I have a Base table which started out life as an Excel spreadsheet. In the Excel spreadsheet, there were two columns, call them A and B, each with the equivalent of a binary value. What I would like to do is write a query or use some function or macro to convert a new column in the Base table (call it C) which contains an enumeration based on the values of A and B. (C is essentially a truth table but instead of TRUE/FALSE, I want the enumeration "A", "B", "Both", "None/Neither"...that kind of thing. The enumerations are actually listed in another table in the database.) I could not find a way in a query to do this and I figured it must be pretty simple to do. Any help would be appreciated.

Thanks,
Mike

[RPG]

Hello

I think make first clear wihich database you use. See left down the window for information.

When you read spreadsheet: I think convert the data to the HSQLDB database engine.

For the HSQLDB database engine you can maybe use the case function.

http://wiki.services.openoffice.org/wiki/Built-in_functions_and_Stored_Procedures#Date_and_Time_Functions

Romke

[Sliderule]

1. Define your NEW field of "C" in the table, probably as VARCHAR_IGNORECASE(25)
   
   
2. This is an UPDATE ( changing the data, not a Query - whereas a Query just READS the data ), so, the command is issued from the Menu: Tools -> SQL...
   
   
3. Change the "MyTable" below to your table name, being sure it is entered exactly as your table name is defined, including CASE ( UPPER / Mixed / lower ) characters. Copy and paste the SQL commands below into the Command to execute box.
   
   

   Code:

   UPDATE "MyTable" SET "C" = 'Both' WHERE "A" = TRUE and "B" = TRUE; UPDATE "MyTable" SET "C" = 'A' WHERE "A" = TRUE and "C" IS NULL; UPDATE "MyTable" SET "C" = 'B' WHERE "B" = TRUE and "C" IS NULL; UPDATE "MyTable" SET "C" = 'None/Neither' WHERE "C" IS NULL;

   
   
   
4. Press the Execute button
   
   
5. Press the Close button
   
   
6. Smile and say: "Gee Sliderule, that was easy. Now all I have to do is mark the forum post as [Solved] per the instructions at the bottom."

Explanation: You of course will have to change the "MyTable" to your table name, Keep in mind, that a Boolean field, in your explanation, both fields "A" and "B" can have THREE values, TRUE, FALSE, or NULL ( undefined ).

So, what the UPDATE statement is doing:

1. Check where BOTH fields "A" and "B" are TRUE, and, if so, assign "C" to the text string 'Both'
   
2. Check where "A" is TRUE, but, "C" ( the new field ) still NULL ( not assigned above ), assign it the text string 'A'
   
3. Check where "B" is TRUE but, "C" ( the new field ) still NULL ( not assigned above ), assing it the text string 'B'
   
4. If nothing not yet assigned to "C" ( the new field ) . . . so it is NULL, assign it the text string 'None/Neither'

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.

[Zerex71]

Hi,

I will check into the case function and see if I can do it. When I select the dropdown for Function in a query, I don't get the option for such a function, only a few simple statistical and other functions. So I have to learn how to (a) write a custom function, (b) where the function editor is and where the function is stored, (c) where and how to access a built-in library of functions for this kind of thing.

I did convert the Excel to .csv, then read the .csv in using Calc. Then I copied the Calc data into a new table in Base, so I am using HSQL (1.8 I guess is the default). I don't have time at the moment to monkey with setting up the much-recommended HSQL 2.2.8.

Thanks,
Mike

[Zerex71]

Hi Sliderule,

Okay, that sounds good...it sounds like I will have to write a little SQL "code" but no problem.

In fact, my column A and column B actually have the character 'P' in them to represent TRUE, because in Excel, I can use the Wingdings 2 font to make it look like a checkmark to indicate that this entry applies independently to A and B. What I want to do is convert P's into an enumerated value (in effect, collapsing two boolean columns into a single enumerated column).

I am fine with writing the four statements as you have them, however, it would be much better if I could automatically just point to the values in the enumeration table which already contains the four resulting values (A, B, Both, Neither). How would you modify the UPDATE statements to do that?

Mike

[Sliderule]

Once again, not knowing the names of your tables, change it as required from below:

[Zerex71]

Perfect, I'll give it a shot.

(I realize this falls under the realm of learning to code SQL at the moment but since they seem like fairly straightforward and simple tasks, I figured I could ask...I do not at this moment in time have much time to spend on becoming a SQL expert.

Thanks again as always, you are always helpful.

Mike

[Zerex71]

Actually, looking at the SQL again, I'm not sure how that will work. I still want the conditional logic of the four UPDATE statements you first gave, but instead of hard-coding the result strings into the column of my table, I want to use the enumerated values from the enumeration table. There are about 1,000 entries in the main table with the column C, but only four enumerated values (and this will be fixed). So I still want to convert the A & B columns into a single column C using only the values in the enumeration table.

If this works, I will study it and see how and why it works.

[Sliderule]

[Zerex71]

More specifically, without divulging anything sensitive:

Main table: tblRequirements
- Has column Network and column Standalone, each currently containing the letter P or nothing (so is it NULL or is it empty?)
- Also has new column Applicable Mode(s)

Enum table: tblPCSimConfigurations
- Contains four entries under fields ID and Mode:
0 Network
1 Standalone
2 Both (Network and Standalone)
3 None

I want to update tblRequirements.Applicable Mode(s) to hold a string from tblPCSimConfigurations.Mode based on the combination of P's in tblRequirements.Network and tblRequirements.Standalone.

Sorry if I was not too clear in the beginning.

Mike