Hi,

Rewriting an approx. 15 years old application makes me headache...

I have a table with current settings and historic settings. The table has the following fields:

ID : integer
Name: String
History : integer  ( 0 = current; >0 Historic values)
Setting: String

At the moment I have a Grid where I show all records like (taken over from the old software):

Name         History No.          Setting

The result is that a Setting with 10 Histories are shown 10x in the table. In other words this list is unusable with all the records and histories.


It should be changed to
Name          Number of Histories      Current Setting

I am using the following SQL Statement, which does not work:

SELECT
    name,
    Setting,
    SELECT COUNT(ID) FROM machinesettings WHERE name = name  AS Historynumbers
FROM
    machinesettings
WHERE
    History = 0

I haven't found in the Documentation how to to make the selection for the current record against the whole database. How to change/expand the SQL to something like

SELECT
    name,
    Setting,
         SELECT COUNT(ID) FROM machinesettings WHERE machinesetting.name = currentrow.name  AS Historynumbers
FROM
    machinesettings
WHERE
    History = 0

currentrow.name = the record which is currently processed in the querry.

On 7/21/2020 10:39 AM, Teco wrote:
>
> I haven't found in the Documentation how to to make the selection for the current record against the whole database. How to change/expand the SQL to something like
>
> SELECT
>       name,
>       Setting,
>            SELECT COUNT(ID) FROM machinesettings WHERE machinesetting.name = currentrow.name  AS Historynumbers
> FROM
>       machinesettings
> WHERE
>       History = 0
>
> currentrow.name = the record which is currently processed in the querry.
>

You basically need to use seperate table instance names so EDB can
figure out which row you're referring to

Try something like this

SELECT
     A.name,
     A.Setting,
     SELECT COUNT(ID) FROM machinesetting B WHERE A.name = B.name  AS
Historynumbers
FROM
     machinesetting A
WHERE
     History = 0

Raul

Thank you Raul.

Works perfectly.




Raul wrote:

On 7/21/2020 10:39 AM, Teco wrote:
>
> I haven't found in the Documentation how to to make the selection for the current record against the whole database. How to change/expand the SQL to something like
>
> SELECT
>       name,
>       Setting,
>            SELECT COUNT(ID) FROM machinesettings WHERE machinesetting.name = currentrow.name  AS Historynumbers
> FROM
>       machinesettings
> WHERE
>       History = 0
>
> currentrow.name = the record which is currently processed in the querry.
>

You basically need to use seperate table instance names so EDB can
figure out which row you're referring to

Try something like this

SELECT
     A.name,
     A.Setting,
     SELECT COUNT(ID) FROM machinesetting B WHERE A.name = B.name  AS
Historynumbers
FROM
     machinesetting A
WHERE
     History = 0

Raul