Source language: Translate to:

Query in a multi fields

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Query in a multi fields

Postby Leos » Sat Feb 01, 2014 11:24 am

Hi,
Is there any simple way to make a query simultaneously in all the fields in one table?
I tried to use 'CONTAINS' but it returns error.

Example:

database: TESTE
field1
field2
field3


*SELECT *
FROM TESTE
WHERE CONTAINS((field1,field2,field3,) ' "Neobook" ');

Thanks
User avatar
Leos
 
Posts: 178
Joined: Mon Apr 04, 2005 11:13 am
Location: Coimbra, Portugal

Re: Query in a multi fields

Postby Gaev » Sat Feb 01, 2014 1:27 pm

Leos:

If you want to select records where 'NeoBook' is in ANY ONE of the fields ...
Code: Select all
SELECT FROM TESTE
WHERE field1 LIKE '%NeoBook%' OR field2 LIKE '%NeoBook%' OR field3 LIKE '%NeoBook%';


If you want to select records where 'NeoBook' is in ALL the fields ...
Code: Select all
SELECT FROM TESTE
WHERE field1 LIKE '%NeoBook%' AND field2 LIKE '%NeoBook%' AND field3 LIKE '%NeoBook%';
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Query in a multi fields

Postby Leos » Sat Feb 01, 2014 7:46 pm

Hi Gaev,
One more time thank you for your help, but in this case, is not exactly what I need.
Using this method I get this, and is not yet complete!

Code: Select all
SELECT * from EuroMilhoes35 where ((((N1 LIKE '3' OR N2 LIKE '3' OR N3 LIKE '3' OR N4 LIKE '3' OR N5 LIKE '3') AND (N2 LIKE '7' OR N3 LIKE '7' OR N4 LIKE '7' OR N5 LIKE '7') OR ((N1 LIKE '3' OR N2 LIKE '3' OR N3 LIKE '3' OR N4 LIKE '3' OR N5 LIKE '3') AND (N2 LIKE '10' OR N3 LIKE '10' OR N4 LIKE '10' OR N5 LIKE '10')) OR ((N1 LIKE '3' OR N2 LIKE '3' OR N3 LIKE '3' OR N4 LIKE '3' OR N5 LIKE '3') AND (N2 LIKE '13' OR N3 LIKE '13' OR N4 LIKE '13' OR N5 LIKE '13')) OR ((N1 LIKE '3' OR N2 LIKE '3' OR N3 LIKE '3' OR N4 LIKE '3' OR N5 LIKE '3') AND (N2 LIKE '21' OR N3 LIKE '21' OR N4 LIKE '21' OR N5 LIKE '21'))) OR ((N1 LIKE '7' OR N2 LIKE '7' OR N3 LIKE '7' OR N4 LIKE '7' OR N5 LIKE '7') AND (N2 LIKE '10' OR N3 LIKE '10' OR N4 LIKE '10' OR N5 LIKE '10'))) OR ((N1 LIKE '10' OR N2 LIKE '10' OR N3 LIKE '10' OR N4 LIKE '10' OR N5 LIKE '10') AND (N2 LIKE '13' OR N3 LIKE '13' OR N4 LIKE '13' OR N5 LIKE '13'))) OR ((N1 LIKE '13' OR N2 LIKE '13' OR N3 LIKE '13' OR N4 LIKE '13' OR N5 LIKE '13') AND (N2 LIKE '21' OR N3 LIKE '21' OR N4 LIKE '21' OR N5 LIKE '21'));


SQL it haves WHERE / CONTAINS that solve my problem, but it doesn't work in SQL access database
It was great if NeobookDBPro did it...
Maybe in a next update?

Best regards
User avatar
Leos
 
Posts: 178
Joined: Mon Apr 04, 2005 11:13 am
Location: Coimbra, Portugal

Re: Query in a multi fields

Postby David de Argentina » Sat Feb 01, 2014 8:20 pm

Hi,

Try something like this:

Code: Select all
SELECT *
FROM TESTE
WHERE
field1 IN("Neobook", "NeoPaint", "NeoDBPro") OR
field2 IN("Neobook", "NeoPaint", "NeoDBPro") OR
field3 IN("Neobook", "NeoPaint", "NeoDBPro");


Source:
http://office.microsoft.com/en-us/acces ... 66611.aspx

My devaluated (12x) cent,
David de Argentina
User avatar
David de Argentina
 
Posts: 1553
Joined: Mon Apr 04, 2005 4:13 pm
Location: Buenos Aires, Argentina

Re: Query in a multi fields

Postby Leos » Sat Feb 01, 2014 9:05 pm

Hi, David

This works (without the brackets), but is not yet what i need...
(if you have for example 20 fields, it's a problem...)

I need something like that:

Code: Select all
SELECT *
FROM TESTE
WHERE
* IN("Neobook", "NeoPaint", "NeoDBPro")


The second " * " it represents "ALL FIELDS"

Best regards to you
User avatar
Leos
 
Posts: 178
Joined: Mon Apr 04, 2005 11:13 am
Location: Coimbra, Portugal

Re: Query in a multi fields

Postby Gaev » Sun Feb 02, 2014 7:02 am

Leos:
The second " * " it represents "ALL FIELDS"

It was great if NeobookDBPro did it...
Maybe in a next update?
I do not think SQL supports the use of the * (wildcard) for the field names ... so it is unlikely that NeoBookDBPro can provide a similar command.

Note that it is highly unlikely that ALL fields in a Table are of the same type (text, number, boolean etc.) ... so such a requirement is not so common.

(if you have for example 20 fields, it's a problem...)
If it is a problem for you (the developer) to construct manually, there is a way around it i.e. you can pass some variables to a generic subroutine e.g. ...
Code: Select all
SetVar "[FieldList]" "field1,field2,field3"
SetVar "[InList]" "NeoBook,NeoPaint,NeoDBPro"
SetVar "[QueryConnector]" "OR"
GoSub "ComposeQuery"
... then issue the SQL command like ...
Code: Select all
dbpExecSQL "database id" "SELECT *
FROM TESTE WHERE [ComposedQuery]" "results table"
... where [ComposedQuery] would be constructed by the subroutine.

If you do not want to manually specify ALL fields in the Table for [FieldList], you can ...
Code: Select all
dbpGetTableNames "TableNameHere" "," "[FieldList]"

If you need help with the subroutine, ask here.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Query in a multi fields

Postby Leos » Sun Feb 02, 2014 9:04 am

Hi Gaev,
I really appreciate your help, but in this case is not exactly what i have in my mind...

What I need / suggest, is a command in NeobookDBPro, so simple, something like that:

Code: Select all
dbpFind "DataBase" "Table" "field1;field2;field3;field4;field5" "text to find" "ExactMatch=No;CaseSensitive=No"


But doing a query, not a search in the database...
something like that:

Code: Select all
what i want:

"dbpQuery "DataBase" "Table" "field1;field2;field3;field4;field5" "text to query" "ExactMatch=No/yes;CaseSensitive=No/yes"

OR

"dbpQuery "DataBase" "Table" "field1;field2;field3;field4;field5" " 'text' 'to' 'query' " "ExactMatch=No/yes;CaseSensitive=No/yes"


I know that we have the problem with different fields datatypes, etc... but the idea is exactly not care about it...

Somethink like that:
Code: Select all
query my table with this 'text to find' (string data, numeric, memo ... don´t care...)


Best regards to you
User avatar
Leos
 
Posts: 178
Joined: Mon Apr 04, 2005 11:13 am
Location: Coimbra, Portugal

Re: Query in a multi fields

Postby Neosoft Support » Mon Feb 03, 2014 11:56 am

I'm not sure if this is something that could be done through DBPro or not. Almost everything that DBPro does behind the scenes involves SQL. Apparently, MS Access does not support the SQL Contains function, so DBPro can't change that.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 3 guests