Find Distinct values

Classic Servoy
1

Hi All

Been playing around with this one and just getting frustrated.

I want to find records in a table with a certain code and omit records with a onhand value of 0. Using the folowing code:

var current_item = maintenance_to_lineitems.item_id

forms.lines_out.controller.find();
forms.lines_out.item_id = current_item;
forms.lines_out.lineitemsgrn_to_lineitemsgrn.quantity_onhandgrn = “!0”;
forms.lines_out.controller.search();

application.showFormInDialog( forms.line_items_grnonhand, -1, -1, 450, 700, ‘GRNs On Hand’, false, false, true);

The search for the item code works fine but the records with a zero blance still show. I have used this on other methods and it works OK. No matter how i play with it i’m still showing these records.

Also, how can you reduce a search to only show distinct values as in the sql command SELECT DISTINCT. I can get this working with a SQL query using another tool but not through raw sql in servoy.

2

rodneysieb:
Also, how can you reduce a search to only show distinct values as in the sql command SELECT DISTINCT. I can get this working with a SQL query using another tool but not through raw sql in servoy.

Hi Rodney,

Servoy lets you leverage your exisiting SQL skills :-), so take the plunge and try databasemanager.getDataSetByQuery() for searching… this is safe and easy to use.

The RawSQL plugin is for ‘expert use’, as it will do anything you tell it to. Health warning: It is up to you to tell Servoy you’ve changed the data… if you don’t…

3

Also have a look at the loadRecords function.

//Load records can be used in 4 different ways
//1) to load a (related)foundset
controller.loadRecords(order_to_orderdetails);

//2) to load a primary key dataset (max ~200 records due to limits with SQL IN statement), will remove related sort!
//var dataset = databaseManager.getDataSetByQuery(...);
//controller.loadRecords(dataset);

//3) to reload all last related records again, if for example when searched in tabpanel
//controller.loadRecords();

//4) to load records in to the form based on a query (also known as 'Form by query')
//controller.loadRecords(sqlstring,parameters);
//limitations/requirements for sqlstring are:
//-must start with 'select'
//-must contain 'from' and 'order by' keywords
//-the 'from' must be a comma separated list of table names
//-must at least select from the table used in Servoy Form
//-cannot contain 'group by' or 'having'
//-all columns must be fully qualified like 'orders.order_id'
//-the selected columns must be the (Servoy Form) table primarykey columns (alphabetically ordered like 'select a_id, b_id,c_id ...')
//-can contain '?' which are replaced with values from the array supplied to parameters function argument
4

Hi

Thanks guys, will play around with this a while now i understand the limits of how servoy handles the SQL. Can you confirm that there is no way of finding DISTINCT values using servoy functions?

5

I have a requirement like Rodney’s. I want to allow user to use a special character to find DISTINCT values as in FileMaker Pro (! symbol)

Thanks!!

6

faheemhameed:
I have a requirement like Rodney’s. I want to allow user to use a special character to find DISTINCT values as in FileMaker Pro (! symbol)

In fact FileMaker Pro’s ! operator does exactly (kind of) the opposite of DISTINCT.
It finds the duplicate records (for those field(s)) for you.
As far as I know FileMaker Pro doesn’t have a DISTINCT function at all.

7

So do we have a similar function in Servoy? That’s the function I am looking for.

8

Depends on what you want to do. Can you describe in detail what you want to accomplish? What do you have, what do you want to display and what action is the user going to perform on the found data?

9

The user does an import of a raw excel data into a contact table. Now he wants find all the doubles or multiples in the first_name field. Then decides which one to keep and which ones delete.

10

Will be very interested to hear how the Servoy guru’s handle this as I have similar function in a solution that works but feels clunky:

Psuedo code from memory:

Sort by first_name

goto record 1 & set global field gFirstName

loop through foundset

if firstname = gFirstName
{
set record duplicateField flag
set record -1 duplicateField flag
}
else
{ set new gFirstName}

Then just do a search on duplicateField to show all the records with duplicate flag set. If you sort by firstName & PK it’s easy to spot which are the new records.

Works fine although could be slow for very large record sets.

My feeeling is that Marcel probably has one line of Javascipt that achieves all this and makes coffee at the same time :)

Graham Greensall
Worxinfo Ltd

11

Well in SQL you could find the data by using a query like this:

SELECT first_name, count(first_name) FROM tableName GROUP BY first_name HAVING count(first_name) > 1;

Only problem with this query is that you don’t have the PK’s of those records. Adding the PK field to the query would render it useless.
So you can’t use this query in a controller.loadRecords(SQL). (darn!)

Another way that would work is a technique (trick if you will) I used in FMPro.
Create a relationship on first_name = first_name (a selfjoin).
An index on column first_name would be nice too ;)

Create a stored calculation that looks like this:

// Am I looking at myself or is it an earlier record...
if ( selfjoinRelationship && selfjoinRelationship.pkField != pkField)
{
   // Yes, it an older record I see...lets mark myself as double
   return 1;
} else {
   return 0;
}

Recalc your foundset (only this one time) so that your calculation is stored in all records.

databaseManager.recalculate(foundset);

(Yes, this can take a while…)

Now do a search on your stored calculation for the value 1;
Voila…you have your multiples (without the originals).

12

ROCLASI:
Well in SQL you could find the data by using a query like this:

SELECT first_name, count(first_name) FROM tableName GROUP BY first_name HAVING count(first_name) > 1;

Only problem with this query is that you don’t have the PK’s of those records. Adding the PK field to the query would render it useless.
So you can’t use this query in a controller.loadRecords(SQL). (darn!)

Ofcourse you can!

select t1.pkid, t1.firstname
from people t1, 
(select firstname 
from people 
group by firstname 
having count(*)>1) vw1 
where t1.firstname=vw1.firstname

Probably the easiest next step is to flag the dupes and do a servoy find on them. For example if you have an extra column isdup you could execute this with the raw sql plugin:

update people set isdup=1 where pkid IN (select t1.pkid
from people t1, 
(select firstname 
from people 
group by firstname 
having count(*)>1) vw1 
where t1.firstname=vw1.firstname )
13

jaleman:
Ofcourse you can!

select t1.pkid, t1.firstname

from people t1,
(select firstname
from people
group by firstname
having count(*)>1) vw1
where t1.firstname=vw1.firstname

Ofcourse! :shock: Hey, I don’t mind to be proven wrong in this matter. :D
I was kinda looking for this query but I didn’t see it. I guess it’s time to polish up my SQL skills a bit ;)

14

Hi all

thanks to everyone for your suggestions/help but i still have not worked out how to find distinct values in servoy using sql.

I have attached a screenshot to help demonstrate.

From the screenshot you can see that for this item code there are only 3 distinct grn’s : BAE5912, BAE5920, BAE5925

What i want to do is strip out/omit all the duplicate grns to show only 3 records not the current 12 ( the number data are aggregations from another table and are unique to each grn)

i can run the following sql command from a query tool and it gets me the 3 grn’s straight away. but i can’t seem to fire it from within servoy.

SELECT DISTINCT grn FROM lineitems WHERE item_code = “xyz”

This will return 3 grns, great. but i can’t seem to do this from within servoy. I am using mySQL.

I would appreciate any help to fire this sql from within servoy using either execute sql, getdatasetbyquery, or loadRecords or whatever and load it into a FID.

15

What version of MySQL are you using ?
I know that older versions of MySQL don’t support subselects.

16

If the list is to click only I’d consider using a html list, get your data using your distinct sql query and stick it in a global field using html_area display. Use ahref’s to bind it back to a method for the click.

17 
var sql = 'SELECT DISTINCT grn FROM lineitems WHERE item_code = ?';
var args = new Array();
args[0] = 'xyz';

var dataset = getdatasetbyquery(etc etc... look up the exact syntax here)
controller.loadRecords(dataset);

I do this kind of thing all the time with PostgreSQL, and I don’t see any reason why it shouldn’t work with mySQL.

or have a missed something?

18

OK, I have missed something. The problem is that you have to select a unique pk.

19

You need to display this list using a form based on a table where the pk is grn. If you don’t have such a table, it may be worth adding one.

20

Hi all

Thanks everyone for their help.

My current fix for this currently uses grahams technique to loop the foundset and set a isDuplicate? type value then search on the non duplicates. This works but i am concerned that with foundset size the method will take too long.

My next attempt will be to use self-relationships to determine duplicate values in a stored calculation field and omit these first which should speed the loop considerably.

Thanks to all who have posted.