[Solved] Query Base for character in the column

Creating tables and queries

[Solved] Query Base for character in the column

Postby cub001 » Sun Dec 02, 2018 6:59 am

Is it possible to Query a column of single words for a specific character?
For example, I have a database with a column of single words. Each word has one specific number (ex: word3, word4, etc) and only one number per word.
What I would like to do is to query that column for a specific number. I also want to be able to update that query with other
specific numbers without building more queries.
Any help would be appreciated. :)
Thanks
cub001
Last edited by Hagar Delest on Mon Dec 03, 2018 9:17 am, edited 1 time in total.
Reason: tagged solved
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
User avatar
cub001
 
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: Query Base for char. in the column

Postby FJCC » Sun Dec 02, 2018 7:33 am

You can write a query like
Code: Select all   Expand viewCollapse view
SELECT * FROM "TableName" WHERE "WORD" LIKE '%' || :NUMBER || '%'

Substitute your table name and column name for "TableName" an "WORD". The % serve as wildcards and the || concatenate text. You will be prompted for a number each time you run the query. If the numbers are always at the end of the word, you can omit the trailing || '%'.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7230
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Query Base for char. in the column

Postby cub001 » Sun Dec 02, 2018 9:41 pm

Talk about getting excited. Unfortunately, I can't get it to work.
Let me tell you what I have done so far, I probably did it wrong.
I have a CSV file that I created to build a database from. It has these rows:
"FREQ";"CALL";"MODE";"NAME";"QSO_DATE";"QSO_DATE_OFF";"TIME_OFF";"TIME_ON";"RST_RCVD";"RST_SENT";"STATE";"BAND"
The base built fine but I discovered that it does not convert it to an editable base from the spreadsheet made, that is able to add records. For example: if there are 100 records then you have a base that has 100 records that one can work with, but cannot add records.
So I went back to the SS and added the original records plus blank records to 1000 records. This way I can add more records.
Now I wanted to query the CALL field. Couldn't make it work and came here. I thought sure yours would work but I get:" the query too complex"
FYI: My table is already built "Sheet1" and cannot rename. This is "Sheet1". The base is called MyLog.odb
I built the Quert as you described.

SELECT * FROM "Sheet1" WHERE "CALL" LIKE '%' || :NUMBER
Am I doing something wrong?
Thanks so much for your time and help
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
User avatar
cub001
 
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: Query Base for char. in the column

Postby FJCC » Sun Dec 02, 2018 11:44 pm

Try this form
Code: Select all   Expand viewCollapse view
SELECT * FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%',:NUMBER)
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7230
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Query Base for char. in the column

Postby cub001 » Mon Dec 03, 2018 3:39 am

Well sorry to report Still have issues.
I don't get the error message, however, all I do get is the Header field names:
"FREQ";"CALL";"MODE";"NAME";"QSO_DATE";"QSO_DATE_OFF";"TIME_OFF";"TIME_ON";"RST_RCVD";"RST_SENT";"STATE";"BAND", across the top. That part looks great. But that's all I get.
No records what so ever. I am still using 415 but don't think that's the problem. Also tried other similar SQL statements all I could make it do is display the top field names.
For some reason, it's not recognizing the individual fields, much less parsing the word That I really don't understand because the Spreadsheet and Dbase are so closely related.
Thanks for your continued support
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
User avatar
cub001
 
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: Query Base for char. in the column

Postby robleyd » Mon Dec 03, 2018 4:13 am

For some reason, it's not recognizing the individual fields

Or more likely, the query is not returning any results. What happens with a simple query like SELECT * FROM "YourTableName" ?
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2885
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Query Base for char. in the column

Postby cub001 » Mon Dec 03, 2018 4:19 am

well, that's encouraging it works great. Didn't think to try that.
Wonder what the heck is going on with the other 2 queries...

SELECT * FROM "Sheet1" WHERE "CALL" LIKE '%' || :NUMBER || '%'

SELECT * FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%',:NUMBER)

Thanks
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
User avatar
cub001
 
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: Query Base for char. in the column

Postby FJCC » Mon Dec 03, 2018 5:05 am

It is probably easiest if you upload your spreadsheet. To do that, click Post Reply and look for the upload attachment tab below the box where you type a response.
You can also try
Code: Select all   Expand viewCollapse view
SELECT * FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%',:NUMBER, '%')

If that works, it means you have characters after the numbers, probably unintended spaces.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7230
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Query Base for char. in the column

Postby cub001 » Mon Dec 03, 2018 7:16 am

Ok.
I am uploading both a test CSV file that the spreadsheet was created with and the Spreadsheet file.
Please note that (Separator Symbols) were changed so that OO would convert it properly.
Also, Please note that I extended the ID field to 25 so you can sorta play around with that because I could not get it to
Convert to a base that one could add records too. I had to basically add blank records...

Hope I did the upload right??
Thanks
Attachments
log1.csv
Original CSV file before conversion
(1.23 KiB) Downloaded 30 times
MylogTest.ods
Spreadsheet file converted file CSV file
(14.09 KiB) Downloaded 27 times
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
User avatar
cub001
 
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: Query Base for char. in the column

Postby FJCC » Mon Dec 03, 2018 7:35 am

This works for me with your spreadsheet.
Code: Select all   Expand viewCollapse view
SELECT * FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%',:NUMBER, '%')

The numbers are in the middle of the CALL text, so you need a % before and after :NUMBER.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7230
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Query Base for char. in the column

Postby cub001 » Mon Dec 03, 2018 8:15 am

BINGO..
Would you please explain to me "LIKE CONAT( '%', : NUMBER, '%' )" in detail... I thought I put that exact statement in..Sorry I guess I didn't
Thank you so much. I have been trying everything..
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
User avatar
cub001
 
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: [Solved] Query Base for character in the column

Postby FJCC » Tue Dec 04, 2018 2:08 am

The LIKE token tells the database that the comparison between the thing on the left and on the right will include some wildcard matching. That is, the query is not looking for an exact match but for text that contains certain features.

CONCAT() just glues together text.
CONCAT('A', 'piece of', 'text')
would return Apiece oftext. Of course, it is usually used with column names instead of literal text and then the content of the column in the selected row is glued to whatever else is passed to CONCAT(). CONCAT( '%', : NUMBER, '%' ) would seem to return %:NUMBER% but Base passes the query through an interpreter and that takes a text that starts with a colon to mean "ask the user for input". So, :NUMBER results in Base asking for a user input called NUMBER. After you input that, the CONCAT() continues using your input. If you response to the request for NUMBER with a 5, CONCAT('%', :NUMBER, '%') results in '%5%'.

So now we know that
"CALL" LIKE CONCAT( '%', : NUMBER, '%' )
will be processed as
"CALL" LIKE '%5%'
The % signs are wild cards that mean "match any text". %5% means "match any text, followed by 5, followed by any text" which is to say "match a text as long as it contains a 5".
WHERE "CALL" LIKE '%5%'
will select any row where the CALL column contains a 5.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7230
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Query Base for character in the column

Postby cub001 » Tue Dec 04, 2018 5:08 am

well, I figured out why I was not querying properly..
I changed the script from SELECT * FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%',:NUMBER, '%') To SELECT * FROM "Sheet1" WHERE "CALL" LIKE '%' || :NUMBER || '%'

Why won't both work???? :crazy:

Thank you so much for your time and explanations
cub001
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
User avatar
cub001
 
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: [Solved] Query Base for character in the column

Postby FJCC » Tue Dec 04, 2018 5:24 am

To add to your confusion, I ran
Code: Select all   Expand viewCollapse view
SELECT "CALL" FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%', :NUMBER, '%')

using your spreadsheet as the table and it worked fine.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7230
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Query Base for character in the column

Postby cub001 » Wed Dec 05, 2018 2:35 am

Well, this one...SELECT "CALL" FROM "Sheet1" WHERE "CALL" LIKE CONCAT( '%', :NUMBER, '%' ) does not work in mine for some reason..
I would really like to understand why...

SELECT * FROM "Sheet1" WHERE "CALL" LIKE '%' || :NUMBER || '%' does work fine ????

Maybe compiler, program version?????

Thanks
Cub001
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
User avatar
cub001
 
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: [Solved] Query Base for character in the column

Postby FJCC » Wed Dec 05, 2018 2:44 am

Strangely,
Code: Select all   Expand viewCollapse view
SELECT * FROM "Sheet1" WHERE "CALL" LIKE '%' || :NUMBER || '%'

does not work for me and
Code: Select all   Expand viewCollapse view
SELECT * FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%', :NUMBER, '%')

does work. I am on Windows 10 AOO version 4.1.6.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7230
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Query Base for character in the column

Postby robleyd » Wed Dec 05, 2018 2:48 am

Does Base provide any debug functionality that would allow seeing the actual query that is passed to the database engine?
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2885
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] Query Base for character in the column

Postby cub001 » Wed Dec 05, 2018 3:33 am

FYI:
These two work for me

SELECT * FROM "Sheet1" WHERE "CALL" LIKE '%' || :NUMBER || '%'

SELECT * FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%' || :NUMBER || '%')

Apparently, the || operators work best with CONAT...
However, that's all that I can get to work when trying to get the same results.
Merry Christmas and Thanks Again
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
User avatar
cub001
 
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: [Solved] Query Base for character in the column

Postby eremmel » Wed Dec 05, 2018 10:34 am

Did some testing:
# Data based on Calc document
AOO 4.1.4 only concat('%', :letter, '%') with || query too complex
LO 5.2.5.1 only concat('%', :letter, '%') with || query too complex
LO 6.0.5.2 only concat('%', :letter, '%') with || query too complex

# Data based on HSQLDB 1.8 (Embedded database)
AOO 4.1.4 supports concat( '%', concat(:letter, '%'))
and '%' || :letter || '%'
but not concat('%', :letter, '%') Java error.

So it looks like the access to Calc has its own program path or database driver...
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: [Solved] Query Base for character in the column

Postby Villeroy » Wed Dec 05, 2018 7:30 pm

When it comes to pseudo-databases (sheet, text, dBase), http://www.openoffice.org/dba/specifica ... tions.html lists all availlable functions.
In early versions of Base, the || operator was not availlable at all and CONCAT was limited to 2 arguments which led to absurdities as CONCAT(arg1, CONCAT(arg2, CONCAT(arg3, '%')))
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26980
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Query Base for character in the column

Postby cub001 » Thu Dec 06, 2018 12:17 am

Well, it has been enlightening for sure for me.
Still, In my opinion, one can't beat OO suite for an opensource bundle.
I have both Libre and OO for years, mainly because of the ease at which one can save and export in Libre, that OO writer still doesn't have.
But the little bit we accomplished in this effort has answered my issues about querying a column for characters and data.
I really appreciate the kindly help...I'm reading through MySQL, PostgreSQL, etc trying to find some correlations.
I use to program a lot and totally understand how compatibility and hard-headedness affect a programmer's code.
Sorta like one can't be everything to everyone..
Thanks Much!!
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
User avatar
cub001
 
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest