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

Filtering when multiple attributes in one cell?

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


Joined: 04 Jul 2009
Posts: 13

PostPosted: Sun Jul 05, 2009 2:45 pm    Post subject: Filtering when multiple attributes in one cell? Reply with quote

One of the fields in my database contains varied attributes, and an irregular amount of such. To exemplify; for a 'Flowers data base" the flower 'Rose' can under field 'Characteristics' have e.g; "tall, thorny, sweet smelling, thin leaves" whereas the flower 'Dandelion' under 'Characteristics' may read; "short, sweet smelling" - i.e a different number of characteristics and partially or no overlap in actual attributes so it is not (?) the best idea to split up the contents into separate fields as far as I can tell.

And... you guessed it... now I wish to sort/filter/create queries. Wink

I'm just a beginner so I humbly ask you more experienced guys in here; what would you do? Is there perhaps a way to filter and group based on "substings (strings in stings)"? What else?

Thank you!!


Last edited by amzg on Fri Jul 17, 2009 5:07 am; edited 2 times in total
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: Sun Jul 05, 2009 6:20 pm    Post subject: Reply with quote

amzg:

I hope the following explanation will be helpful.

Since, you have designed your database with the "Characteristics" field to have a possibility of multiple 'characterictics', you will have to SEARCH for a 'text string' anywhere in the field. I mean, it might be at the beginning, the middle, or, the end.

In the database search language:
  1. the word for a 'partial' search is LIKE
  2. Percent is used to indicate a 'wild card' search.

    'MySearch%' means it must BEGIN with 'MySearch'.

    '%MySearch%' means it can be anywhere in the field.

    '%MySearch' means it must END with 'MySearch'

  3. Text literals are surrounded by single quotation marks: ' . . . such as: 'MySearch'
  4. Table and Field (Column) names are enclosed in double quotes " , such as: "Characteristics"
  5. Notation for a Parameter Prompt begins with a semi-colon : and is ONE WORD ( no spaces ), such as: :Enter_Search_String
  6. Two pipe characters, withOUT a space between them || is used to CONCATENATE strings of text together, such as: '%' || 'sweet smelling' || '%'

OK, I created an example of your table as seen below:



To create your Query, please follow these steps:
  1. Open your database in OpenOffice
  2. Under Database - press Queries
  3. Under Tasks - press Create Query in Design View...
  4. An Add Table or Query pop-up will be displayed, select your table name, press Add button
  5. Press Close button
  6. On the lower half of the screen will be a line named Field . . . press the down button and select Flower ( or whatever is the Field in your table that is appropriate )
  7. In the next column, select the column that contains the data you want to search, in this example, it will be Characteristics
  8. In the line Criterion under Characteristic, enter
    Code:
    LIKE '%' || 'sweet smelling' || '%'

  9. Press the Run Query Icon on the ToolBar, OR, F5
  10. You may elect to SAVE this Query from the ToolBar, if you wish, and, assign the saved query a name



- - - - - - - - - - - - - - - - - - - - - - - - - - - -
Below, I will attempt to show you how to create a PARAMETER QUERY, where, the Query will PROMPT you for input ( in this case, the string of text you want to search for in the "Characteristic" field ( column ).
  1. Open your database in OpenOffice
  2. Under Database - press Queries
  3. Under Tasks - press Create Query in Design View...
  4. An Add Table or Query pop-up will be displayed, select your table name, press Add button
  5. Press Close button
  6. On the lower half of the screen will be a line named Field . . . press the down button and select Flower ( remember the name will be your table name -- )
  7. In the next column, select the column that contains the data you want to search, in this example, it will be Characteristics
  8. In the line Criterion under Characteristic, enter
    Code:
    LIKE '%' || :Enter_Search_String || '%'

  9. Press the Run Query Icon under the menu line, OR, F5
  10. A popup will appear prompting you to input a Value . . . in this case, put the text you want to search
  11. Press the OK button
  12. You may elect to SAVE this Query from the ToolBar, if you wish, and, assign the saved query a name.

    Next time you run this Saved Parameter Query with the Prompt, it will ask you to enter the search string



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

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
MSPhobe
Super User
Super User


Joined: 29 Sep 2005
Posts: 529
Location: England

PostPosted: Mon Jul 06, 2009 12:44 am    Post subject: Reply with quote

The alternative is to re-do your database.

I know this will not be very palatable advice... but what you have done is a Bad Thing that MANY people do..... and it paints you and them into corners that are a pain to deal with.

At first, doing things like your flowers characteristics the Right Way, seems odd and unsatisfactory, but once you get the hang of it, so many things work so much better.

Table 1: Flowers

ID: integer, primary key
Name: text

Table 2: Characteristics
ID: integer, primary key
Description: text

Table 3: WhoHasWhat
ID: integer, primary key
Flower: integer <- this filled with integers from Flowers.ID
Characteristic: integer <- this filled with integers from Characteristics.ID

Sample data....

Flowers...

1: Rose
2: Bramble

Characteristics

1: Thorns
2: Tasty fruit
3: Smelly flower

WhoHasWhat

Code:

ID  Flower Characteristic
1     1           1
2     1           3
3     2           1
4     2           2
Back to top
View user's profile Send private message
amzg
General User
General User


Joined: 04 Jul 2009
Posts: 13

PostPosted: Tue Jul 07, 2009 4:28 pm    Post subject: [SOLVED] Reply with quote

My GOSH! You guys are the greatest! What generosity and kindness! My sincerest appreciation for your - to say the least - thorough answers!

And, actually, thanks to getting your advice I've realized I should actually use both(!) alternatives!
In case you're wondering why/how;

Because of the 'free' nature of the information, there will likely be synonyms included, such as "smelling" and "scent". Rather than keeping e.g "thorny" and "smelling" in the same cell and, say, "scent" in another, I will group synonyms into the same cells but otherwise split it up into different rows.

Again, my sincerest thanks MSPhobe and sliderule!
Back to top
View user's profile Send private message
amzg
General User
General User


Joined: 04 Jul 2009
Posts: 13

PostPosted: Fri Jul 17, 2009 5:07 am    Post subject: Reply with quote

MSPhobe wrote:


Code:

ID  Flower Characteristic
1     1           1
2     1           3
3     2           1
4     2           2


Hm, does this mean I must manually create each new record in the WhoHasWhat table (i.e 4 records in your example) and that I must do this by entering their ID digits? This will mean an incredible amount of manual work for my actual data base.

(Or, BTW, in the quote above, what does "code" actually mean? Is it something that is rendered or is it man made?)


I found this thread: http://www.oooforum.org/forum/viewtopic.phtml?t=82498&highlight=flat+relational where user 'keme' suggests combining ID's for the linking table, i.e in my case a combo of Flower:ID and Characteristics:ID
- is there any benefit/drawback to this?

And also, in table edit view, if I select two rows, right click and select to set Primary key... I get an error message when trying to save. Am I doing something wrong?

Thank you!
Back to top
View user's profile Send private message
jmunoz
Newbie
Newbie


Joined: 09 Jul 2010
Posts: 1

PostPosted: Fri Jul 09, 2010 3:15 am    Post subject: MySQL (JDBC) concat parameters Reply with quote

Hi,

Posting this, just in case is useful for others.

As far as MySQL don't accept the standard "||" concatenation operator you need to use CONCAT function, but looks like there is a bug with the OO Design view.

This is the SQL query what worked for me:

Code:

SELECT * FROM table
WHERE field LIKE CONCAT ('%' , :Enter_Search_String , '%')


But (IMPORTANT) it need to be done in the SQL view (not the OO GUI).
As soon as you enable the Design view, the code is wrongly parsed and the query stop working.

Thanks a lot for this useful post,

Juan.
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