Page 1 of 1

Query in a multi fields

PostPosted: Sat Feb 01, 2014 11:24 am
by Leos
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

Re: Query in a multi fields

PostPosted: Sat Feb 01, 2014 1:27 pm
by Gaev
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%';

Re: Query in a multi fields

PostPosted: Sat Feb 01, 2014 7:46 pm
by Leos
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

Re: Query in a multi fields

PostPosted: Sat Feb 01, 2014 8:20 pm
by David de Argentina
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

Re: Query in a multi fields

PostPosted: Sat Feb 01, 2014 9:05 pm
by Leos
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

Re: Query in a multi fields

PostPosted: Sun Feb 02, 2014 7:02 am
by Gaev
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.

Re: Query in a multi fields

PostPosted: Sun Feb 02, 2014 9:04 am
by Leos
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

Re: Query in a multi fields

PostPosted: Mon Feb 03, 2014 11:56 am
by Neosoft Support
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.