OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Question about converting old fields to new field in query

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Zerex71
General User
General User


Joined: 23 Feb 2012
Posts: 46

PostPosted: Mon Feb 27, 2012 9:37 am    Post subject: Question about converting old fields to new field in query Reply with quote

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
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Mon Feb 27, 2012 10:24 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Feb 27, 2012 10:27 am    Post subject: Reply with quote

  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. Smile 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." Smile


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.
Back to top
View user's profile Send private message
Zerex71
General User
General User


Joined: 23 Feb 2012
Posts: 46

PostPosted: Mon Feb 27, 2012 10:29 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Zerex71
General User
General User


Joined: 23 Feb 2012
Posts: 46

PostPosted: Mon Feb 27, 2012 10:34 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Feb 27, 2012 10:45 am    Post subject: Reply with quote

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

Code:
UPDATE "MyTable" SET "C" = (Select "FieldFromEnumerationTable" From "MyEnumerationTable" Where "MyTable"."PrimaryKey" = "MyEnumerationTable"."PrimaryKey")


Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Zerex71
General User
General User


Joined: 23 Feb 2012
Posts: 46

PostPosted: Mon Feb 27, 2012 11:06 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Zerex71
General User
General User


Joined: 23 Feb 2012
Posts: 46

PostPosted: Mon Feb 27, 2012 11:13 am    Post subject: Reply with quote

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. Smile
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Feb 27, 2012 11:17 am    Post subject: Reply with quote

Quote:
I do not at this moment in time have much time to spend on becoming a SQL expert.


Like you, I do not have much time to spend . . . besides giving clear explanations as above. Since you do not want to learn, that is fine, I need not answer further here.

There seems to be a pattern here, I have no idea what you are talking about.

Since, to the best of my knowledge, I did answer your original question, exactly as you wanted, perhaps someone else can help you.

Sliderule


Last edited by Sliderule on Mon Feb 27, 2012 11:21 am; edited 1 time in total
Back to top
View user's profile Send private message
Zerex71
General User
General User


Joined: 23 Feb 2012
Posts: 46

PostPosted: Mon Feb 27, 2012 11:18 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group