Removing duplicates from TDBLookupListBox
lazarus / 16-Databases

----------------------------------------------------------------------
heebiejeebies · 2022-12-19 10:14

Hi godly Pascal geniuses,

I have a TDBLookupListBox that selects field 'Date' from a SQLite
table and presents it to the user.  This is the code I'm using:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SessionQuery.Close;  SessionQuery.SQL.Text:=('SELECT CAST(Date AS
CHAR) AS Date, ID FROM CurrentSession');  SessionQuery.Open;
So far so good.  But how do I get it to strip out the duplicates, so
that each date appears only once?  I've tried 'SELECT DISTINCT CAST'
and all other combinations of that I could think of, but it didn't
work.  :'(

Thank you!

----------------------------------------------------------------------
dseligo · 2022-12-19 10:35

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SessionQuery.SQL.Text:=('SELECT distinct CAST(Date AS CHAR) AS Date
FROM CurrentSession');
It should work with distinct.
Remove ID field if you need only dates.

----------------------------------------------------------------------
heebiejeebies · 2022-12-19 11:00

Unfortunately it doesn't.  It just gives me a blank box.   :'(

I've left the ID in so I can work with the selected line based on the
ID, although it is not shown.  I thought it had to be specified if I'm
going to call on it?

----------------------------------------------------------------------
Zvoni · 2022-12-19 11:13

Why are you casting to Char?
What's the DataType in your SQlite for that Field?
What's the output look like if you just plain SELECT that field (no
casting, no nothing)?

QuoteI've left the ID in so I can work with the selected line based on
the ID, although it is not shown.  I thought it had to be specified if
I'm going to call on it?That's making no sense whatsoever if you have
duplicates.
Let's say you have 5 records with the same date.
Since you want no duplicates in your Listbox, which ID of the 5
records should be pulled?

----------------------------------------------------------------------
heebiejeebies · 2022-12-19 11:22

Quote from: Zvoni on December 19, 2022, 12:13:56 pmWhy are you casting
to Char?
What's the DataType in your SQlite for that Field?
What's the output look like if you just plain SELECT that field (no
casting, no nothing)?
Datatype is TEXT.  If I get rid of the casting, I get a box that is
blank except for one line that says '(Memo)'.

QuoteQuoteI've left the ID in so I can work with the selected line
based on the ID, although it is not shown.  I thought it had to be
specified if I'm going to call on it?That's making no sense whatsoever
if you have duplicates.
Let's say you have 5 records with the same date.
Since you want no duplicates in your Listbox, which ID of the 5
records should be pulled?

Fair enough, so in this instance I don't need the ID.

----------------------------------------------------------------------
dseligo · 2022-12-19 13:22

Quote from: heebiejeebies on December 19, 2022, 12:22:41 pmQuote from:
Zvoni on December 19, 2022, 12:13:56 pmWhy are you casting to Char?
What's the DataType in your SQlite for that Field?
What's the output look like if you just plain SELECT that field (no
casting, no nothing)?
Datatype is TEXT.  If I get rid of the casting, I get a box that is
blank except for one line that says '(Memo)'.

You didn't say that you get '(Memo)'.

Try this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SessionQuery.SQL.Text:= 'SELECT distinct CAST(Date AS CHAR(10)) AS
Date FROM CurrentSession';
QuoteQuoteQuoteI've left the ID in so I can work with the selected
line based on the ID, although it is not shown.  I thought it had to
be specified if I'm going to call on it?That's making no sense
whatsoever if you have duplicates.
Let's say you have 5 records with the same date.
Since you want no duplicates in your Listbox, which ID of the 5
records should be pulled?

Fair enough, so in this instance I don't need the ID.

I told you in my first response.

----------------------------------------------------------------------
Zvoni · 2022-12-19 14:57

Quote from: heebiejeebies on December 19, 2022, 12:22:41 pmDatatype is
TEXT.  If I get rid of the casting, I get a box that is blank except
for one line that says '(Memo)'.
Ah, OK, the "classic".

QuoteWhat's the output look like if you just plain SELECT that field
(no casting, no nothing)?Could you post plain output of that query?

----------------------------------------------------------------------
heebiejeebies · 2022-12-19 20:15

Quote from: dseligo on December 19, 2022, 02:22:44 pm
You didn't say that you get '(Memo)'.

Try this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SessionQuery.SQL.Text:= 'SELECT distinct CAST(Date AS CHAR(10)) AS
Date FROM CurrentSession';
With your original example I only got blank, not '(Memo)'.  Anyway,
this worked. Thanks 

Thanks for your help also, Zvoni!