Help needed with SELECT DISTINCT

Classic Servoy
1

Hi all.

I am trying to make a select work to load records into a popup form with only distinct values for what I’ve called ‘invoice_number’

My solution has an estimates table, a line_items table, an invoice_credit_join table, and an invoices table. Invoice credit join stores line item ids, and invoice ids, so that a history of all invoices and credits that an item has been on is saved, and we can look at an invoice containing line items from the past, even if those line items have been credited, and re-invoiced on another invoice.

My problem is in trying to get a popup window to show a list of invoices / credits that an estimate has had. It does work, but it shows multiple entries for each invoice number like this

8766
8766
8766
8766
8765
8765
8765
8765

When I want it to show only:

8766
8765

Seems simple enough…

So, my method which works but shows all numbers is:

var sql_query = "SELECT invoice_credit_join_id, invoice_id FROM invoice_credit_join WHERE sales_id = ?"
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), sql_query , [forms.sale_master.salesid],-1)
forms.invoice_credit_history.controller.loadRecords(dataset)
application.showFormInDialog(forms.invoice_credit_history,-1,-1,-1,-1,'Invoice and credit history',false,false,false);

If I take out the invoice_credit_join_id, I get no foundset
If I add ‘DISTINCT’ after my select and ‘GROUP BY invoice_id’ after WHERE sales_id = ?, I get an error (SQL Anywhere Error -149: Function or column reference to ‘invoice_credit_join_id’ must also appear in a GROUP BY).
If I add invoice_credit_join_id to the GROUP BY, it just goes back to the original problem and shows multiples of each invoice / credit number.

What I want is to select every invoice_credit_join id where invoice_id is distinct from a table whose pk is invoice_credit_join_id and load the results (invoice_id) into a popup window.

I’d be grateful for any help…

Thanks

Bevil

2

Hi Thunder or whatever is your real name .-)

For what table column are you expecting DISTINCT to work? For one of your id columns? Distinct in SQL Anywhere works as far as we found out always on all columns you name in the SELECT statement. So may be the result is not what you expect. I would love if DISTINCT would be a function workin on 1 … n columns as specified in the brackets, like DISTINCT (invoice_id), invoice_credit_join_id from … but that doesn’t work.

May be that helps.

Regards, Robert

PS: May be a case for a request at Sybase.

Thunder:
Hi all.

I am trying to make a select work to load records into a popup form with only distinct values for what I’ve called ‘invoice_number’

My solution has an estimates table, a line_items table, an invoice_credit_join table, and an invoices table. Invoice credit join stores line item ids, and invoice ids, so that a history of all invoices and credits that an item has been on is saved, and we can look at an invoice containing line items from the past, even if those line items have been credited, and re-invoiced on another invoice.

My problem is in trying to get a popup window to show a list of invoices / credits that an estimate has had. It does work, but it shows multiple entries for each invoice number like this

8766
8766
8766
8766
8765
8765
8765
8765

When I want it to show only:

8766
8765

Seems simple enough…

So, my method which works but shows all numbers is:

var sql_query = "SELECT invoice_credit_join_id, invoice_id FROM invoice_credit_join WHERE sales_id = ?"

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), sql_query , [forms.sale_master.salesid],-1)
forms.invoice_credit_history.controller.loadRecords(dataset)
application.showFormInDialog(forms.invoice_credit_history,-1,-1,-1,-1,‘Invoice and credit history’,false,false,false);




If I take out the invoice_credit_join_id, I get no foundset
If I add 'DISTINCT' after my select and 'GROUP BY invoice_id' after WHERE sales_id = ?, I get an error (SQL Anywhere Error -149: Function or column reference to 'invoice_credit_join_id' must also appear in a GROUP BY).
If I add invoice_credit_join_id to the GROUP BY, it just goes back to the original problem and shows multiples of each invoice / credit number.

What I want is to select every invoice_credit_join id where invoice_id is distinct from a table whose pk is invoice_credit_join_id and load the results (invoice_id) into a popup window.

I'd be grateful for any help..

Thanks

Bevil
3

Hi Bevil,

Robert is right.
DISTINCT works on all columns of your query.
If you want to query all the distinct values you will have to do just that, create a separate query for that like:

SELECT DISTINCT invoice_number FROM invoice WHERE …

You can do the same with joins.

4

Hi Jeroen

Thanks for confirming. I still think it’s an omission not to have DISTINCT implemented as a Function. As it it is implemented now, there is no way (or I don’t know of any) to use DISTINCT for example if there is composite key but DISTINCT is only needed for certain columns. The correct way (I think) is done for example in Oracle with a function, so as arguments one gives the columns needed for the DISTINCT. As it’s done in SQL Anywhere is a bit too simple, for whatever reason!?

Best regards, Robert

5

Hi Robert,

I think SyBase implementation is just a correct implementation of the ANSI standard.
Some databases, Foxpro, MySql (I don’t know about Oracle) allow grouped queries with partial fieldlist like:

SELECT A,B,C FROM MYTABLE GROUP BY A

but the answer is non-deterministic. The outcome of B and C is random.

The same would be true if DISTINCT would be allowed on partial fieldlist. The distinct values will be OK but the other values will be ambiguous.
I don’t know if Oracle really implements DISTINCT as a function. As far as I know, DISTINCT is identical to UNIQUE in Oracle.
However,

SELECT DISTINCT (A),B,C FROM MYTABLE

is valid ANSI SQL (als true for Sybase) but here, the parenthesis are not part of DISTINCT being a function, they merely serve a part of the field expressions. The result will give you all possible UNIQUE combinations of A, B and C.

6

Hi Jeroen

Thanks for your examples! What I meant is that

SELECT DISTINCT (A),B,C FROM MYTABLE

does not led to the same result as for example

SELECT DISTINCT (A,B),C FROM MYTABLE

and this is what is sometimes needed. To me, it’s very misleading and we had a long time to find out that SQL Anywhere behaves always like

SELECT DISTINCT (A,B,C) FROM MYTABLE

independent of how you set the brackets. Of course it was misleading to me because I expected something else. It would have ben nice to get a warning saying that using brackets in any way is useless as the result is always the same or something similar. As using brackets leds to another expectation, doesn’t it?

Do you have an idea how to achieve for example

SELECT DISTINCT (A,B),C FROM MYTABLE

with distinct values only for column A and B, but not C?

Thanks and regards, Robert

7

Thanks for the replies

What I really want is:

SELECT DISTINCT invoice_number FROM invoice_credit_join

But unless I also specifically select invoice_credit_join_id as well, I get no dataset. Is THIS normal behaviour?? In other words, I apparently cannot select a distinct column which is not the PK.

Thanks

Bevil

8

Hi Robert,

SELECT DISTINCT (A,B),C FROM MYTABLE

would be equivalent to

SELECT A,B,C FROM MYTABLE

think of it:
How can A and B be distinct in the result if you want to see all values of C?
If your table looks like this:

A | B | C
--+---+--
1 | 1 | a
1 | 1 | b
1 | 1 | c
1 | 1 | c

your ‘distinct’ A/B will appear 4 times in the result anyway. It’s a SQL contradictio in terminis.

9

Hi Bevil,

Looks like you need to retrieve the distinct values from multiple joined tables or use a subquery like:

SELECT DISTINCT invoice_number FROM invoice WHERE invoice_if IN (
 SELECT invoice_id FROM invoice_credit_join WHERE ...)
10

Bevil,

If you call forms.invoice_credit_history.controller.loadRecords(sql) directly, Servoy will skip all the duplicate pks for you.

Rob

11

Hi Rob

I’m not sure what you mean by that but it sounds interesting. What is the syntax? Is that it?? loadRecords(sql)? or do I set a sql dataset? Sorry for asking stupid questions… :)

Bevil

12

also, the PKs are not duplicate. The invoice_id is duplicated, invoice_credit_join_id is the PK and is unique per line. What I want though is only one instance of each invoice_id