My Visual Database — SQL queries

SQL Query

null@example.com (Ansel) — Tue, 24 Feb 2026 15:08:00 +0000

I have a table (Cassa) with the fields
(Giorno, Cifra, record_count, id_Nuova_Causale, Anno).
I need to select all the records in the table with the value
Anno = 2025
and display the updated TableGrid1 with only those values.
I've tried everything, but it doesn't work.
Can someone give me an example of how to write the script? Thanks.

SQL Reset/Delete

null@example.com (Ansel) — Thu, 19 Feb 2026 14:38:31 +0000

Hi. How can I delete all records in a table and reset the ID whitout delete table?

Batch Update Multiple Textboxs

null@example.com (Just4Fun) — Mon, 29 Sep 2025 14:26:43 +0000

I need a bit of help with creating a Batch Update Textbox, I need to filter locations and then have the textbox text value sent to all selected items via apply Link button. I have put together a quick sample to make it easier to understand on what I am trying to accomplish. also if any one could offer any advise on my report format, notes and screenshot in application.
Thanks in advance :-)

FooterValue

null@example.com (agusecc) — Mon, 16 Jun 2025 03:00:42 +0000

please help me fix this procedure, to generate column 6 footer value from last ROW column 6 value

procedure FrmBukuBesar_TableGrid1_OnChange(Sender: TObject);
var
  i: Integer;
  Debet, Kredit, Hasil: double;
begin
  // Format dan hitung total untuk kolom Debet dan Kredit
  for i := 4 to 5 do
  begin
    if FrmBukuBesar.TableGrid1.Columns[i] is TNxNumberColumn then
    begin
      with TNxNumberColumn(FrmBukuBesar.TableGrid1.Columns[i]) do
      begin
        FormatMask := '###,##0';
        Footer.FormatMask := '###,##0';
        Footer.FormulaKind := fkSum;
        FrmBukuBesar.TableGrid1.CalculateFooter;
        end;
    end;
  end;

  // Hitung saldo akhir = Debet - Kredit
  //Debet := FrmBukuBesar.TableGrid1.Columns[4].Footer.FormulaValue;
  //Kredit := FrmBukuBesar.TableGrid1.Columns[5].Footer.FormulaValue;
  //Hasil  := Debet - kredit;
  //FrmBukuBesar.TableGrid1.Columns[6].Footer.Caption := FloatToStr(Hasil);

  with TNxNumberColumn(FrmBukuBesar.TableGrid1.Columns[6]) do
  begin
    FormatMask := '###,##0';
    Footer.FormatMask := '###,##0';
    Footer.FormulaKind := fkNone;
    Footer.FormulaValue := Debet - Kredit;
  end;

  // Kalkulasi ulang footer dan sembunyikan kolom ke-7
  FrmBukuBesar.Edit1.Value := Debet;

  FrmBukuBesar.TableGrid1.CalculateFooter;
  FrmBukuBesar.TableGrid1.Columns[7].Visible := False;
end;

How To Concat Ling Break On SQLite

null@example.com (prahousefamily) — Thu, 27 Mar 2025 03:51:30 +0000

How to Edit Script Insert Line Break On SQLite ?
Code

SELECT 'A'||'\n'||'B'

Response

A\nB

----------

Code

SELECT 'A'||'\r'||'B'

Response

A\rB

----------

Code

SELECT 'A'||'\r\n'||'B'

Response

A\r\nB

----------

Code

SELECT 'A'||char(10)||'B'

Response

[Err] 1 - no such function: char

Отрицательное значение в Вычисляемом поле

null@example.com (Новичок) — Sat, 22 Mar 2025 19:44:36 +0000

Здравствуйте, помогите исправить ошибку при заполнении Вычисляемого поля.
Необходимо, при отрицательном значении вывести текст или ноль.

Ошибка

select case when value < 0 then 'нет данных' when value > 0 then (tb_forma.stiomost-((DATEDIFF(CURRENT_DATE,forma_user.forma_data)+1)*tb_forma.summa)) end

I have problem sql query displays empty id in tablegrid

null@example.com (agusecc) — Fri, 15 Nov 2024 09:00:38 +0000

anyone can help me?
I have problem sql query displays empty id in tablegrid on my aplcation when i search between datetimepicker1 and datetimepicker2 at Laporan Absensi

there are my script

select distinct 
(select employees.id from employees where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as  id,                                 

(select  trim(
ifnull(firstname||' ','')||       
ifnull(lastname,'')
)     from employees where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as Nama,                                 
(select  jabatan from jabatan where jabatan.id = employees.id_jabatan AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}  ) as Jabatan,
(select  unitkerja from unitkerja where unitkerja.id = employees.id_unitkerja AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}  ) as UnitKerja,
(select  romawi from unitkerja where unitkerja.id = employees.id_unitkerja AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}  ) as romawi,
(select  harikerja from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}  ) as HariKerja,
(select sum (hadir) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}  ) as Hadir,
(select sum (alpha) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as Alpha,
(select sum (sakit) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}  ) as sakit,
(select sum (ijincuti) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2}   ) as Ijin_Cuti, 
(select sum (terlambat) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as Terlambat,
(select sum (sanksiapel) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as SanksiApel,
(select sum (lembur) from attendancedays where employees.id = attendancedays.id_employees AND attendancedays.date >= {datetimepicker1} and  attendancedays.date <= {datetimepicker2} ) as Lembur


from attendancedays
left outer join employees on attendancedays.id_employees = employees.id    
left outer join unitkerja on employees.id_unitkerja = unitkerja.id    

order by  unitkerja.id asc

this is the link app :
https://www.mediafire.com/file/0e1neg10 … 7.rar/file

SQL filter passed 30 days.

null@example.com (jeffmtl@hotmail.com) — Sat, 02 Nov 2024 22:10:26 +0000

Hi Everybody

I'm attempting to generate a report from a GRID which will automatically (by opening the form willclick a button, starting the sql) list all clients that are passed 30 days.

Here is what i managed to develop, but it still doesn't work.

SELECT Sipad,Namecombined,date30days
From ClientDbf
WHERE DATE(date30days) > DATE(('now', '-30 days');

Any tips ?

Also I did manage to attempt something that didn't work, and ended up getting the date columb in the grid with hours 00:00:00 is there something in the SQL script i must do to truncate it to only the date ?

Tks.

jeff

SQL query for numerical values

null@example.com (Step-in) — Fri, 11 Oct 2024 07:17:54 +0000

Greetings to all. I have a button with SQL query. The problem is when to specify a search on a field with numbers.

WHERE                                            
    (
        ({Edit3} = '' OR PReg.PSum >= {Edit3}) AND
        ({Edit4} = '' OR PReg.PSum <= {Edit4})
    )

constant error that the syntax is not valid for MySQL.

i tried

(
        (IFNULL({Edit3}, '') = '' OR PReg.PSum >= IFNULL({Edit3}, 0))
        AND 
        (IFNULL({Edit4}, '') = '' OR PReg.PSum <= IFNULL({Edit4}, 999999999999))
    )

but the same thing.

Maybe someone has an example of a SQL query using a request for numeric (more and/or less), dates (more and/or less), checkboxes. Because when I don't have certain fields filled in on the search form, the request gives a constant error about incorrect syntax.

Or maybe it is better to do it through a script in the program to additionally control the conditions. But how is it correct to edit the selected record after filling the table?

Update Database column between two dates

null@example.com (jeffmtl@hotmail.com) — Tue, 08 Oct 2024 10:06:06 +0000

Hi Everybody

I'm a beginner in scripting and attempted to find the answer in the form but might not have had the vocabulary to getting about finding what I wanted.

I am working with a waiting list for clients such that I need to update the whole database of how many days people have been waiting.

An initial date has been entered when a request was made, but unless I enter in each file manually and save the database will not update how many days the client is waiting.

I am use this script when I enter the form (Onshow) and when I click the Save button to update the table.

VAR
// Calculate number of days client on waiting list.
i : integer;
iDays: integer;
start, stop: integer;

Begin // Calculate the number of days client is on waiting list.
if ClientCreate.DateAssigned.Checked = false then
begin
iDays := 0;
start := Trunc (ClientCreate.DateDiscipline.date);
stop := Trunc (date);
for i := start to stop do
Inc(iDays);
Begin
ClientCreate.ClientDaysWaiting.Value := iDays;
end;
end;

I would like to have a script that upon entering the main menu, the table for all the clients is updated automatically.

Any help would be greatly appreicated.

Tks.
Jeff

I need script query sum for (Real)

null@example.com (agusecc) — Tue, 08 Oct 2024 03:34:42 +0000

I try use sql query sum for Real NOT Integer but can't display in table grid. anyone please help me..

(select sum (Real) from attendancedays where employees.id = attendancedays.id_employees ) as Terlambat

I need new webGrid

null@example.com (mouasomar) — Wed, 11 Sep 2024 23:23:11 +0000

I need I need new webGrid

I think the previous version has a problem (Bug)

use XAMPP to connect MVDB (server)

null@example.com (mouasomar) — Mon, 09 Sep 2024 20:34:31 +0000

Experts, please can use XAMPP to connect MVDB (server)?
how

Viewing sqlite view of an MVD project on mobile

null@example.com (jrga) — Tue, 16 Jul 2024 15:19:51 +0000

Sharing: after searching the Play Store, I found the Filex application (freeware), which reads sqlite files (the best I found). I wanted to access a project's sqlite.db, run queries, and save them for future use. I came to the conclusion that it would be faster to create views in sqlite.db using, for example, Sqlite Expert Personal (freeware), than to create a query directly in Filex. Then, with the help of ChatGPT, I generated the views. Below is an example of a view script and a screen with the data displayed by the view, which is the same as what Filex shows on a cell phone. Just copy the sqlite.db to a folder on your phone. It's not the best of all worlds, but it's a simple way to have synthetic analytical information from an MVD project on your cell phone!

WITH
  [subquery] AS(
    SELECT 
           STRFTIME ("%Y", [data]) AS [ano], 
           [lancamento_all_view].[id_tipo_operacao], 
           CASE WHEN [lancamento_all_view].[id_tipo_operacao] = 1 THEN "Receitas" ELSE "Despesas" END AS "Operacao", 
           [lancamento_all_view].[id_categoria], 
           [lancamento_all_view].[categoria], 
           CASE WHEN [id_tipo_operacao] = 1 THEN SUM ([entrada] - [saida]) ELSE SUM ([saida] - [entrada]) END AS [valor]
    FROM   [lancamento_all_view]
    WHERE  [lancamento_all_view].[id_categoria] <> 0
             AND [lancamento_all_view].[ocultar_mining] = 0
    GROUP  BY
              [lancamento_all_view].[id_tipo_operacao], 
              [lancamento_all_view].[categoria], 
              [ano]
  ),
  [category_totals] AS(
    SELECT 
           [categoria], 
           SUM ([valor]) AS [total_valor_categoria]
    FROM   [subquery]
    GROUP  BY [categoria]
  ),
  [cumulative_totals] AS(
    SELECT 
           [a].[ano], 
           [a].[id_tipo_operacao], 
           [a].[Operacao], 
           [a].[categoria], 
           [a].[valor], 
           (SELECT SUM ([b].[valor])
           FROM   [subquery] [b]
           WHERE  [b].[categoria] = [a].[categoria]
                    AND [b].[ano] <= [a].[ano]) AS [acumulado]
    FROM   [subquery] [a]
  ),
  [total_receitas] AS(
    SELECT 
           NULL AS [ano], 
           "Total Receitas" AS [Operacao], 
           NULL AS [categoria], 
           SUM ([valor]) AS [valor], 
           SUM ([valor]) AS [acumulado], 
           NULL AS [part%], 
           NULL AS [mini_grafico]
    FROM   [subquery]
    WHERE  [id_tipo_operacao] = 1
  ),
  [total_despesas] AS(
    SELECT 
           NULL AS [ano], 
           "Total Despesas" AS [Operacao], 
           NULL AS [categoria], 
           SUM ([valor]) AS [valor], 
           SUM ([valor]) AS [acumulado], 
           NULL AS [part%], 
           NULL AS [mini_grafico]
    FROM   [subquery]
    WHERE  [id_tipo_operacao] = 2
  ),
  [main_query] AS(
    SELECT 
           [subquery].[ano], 
           [subquery].[Operacao], 
           [subquery].[categoria], 
           PRINTF ('%.2f', [subquery].[valor]) AS [valor], 
           PRINTF ('%.2f', [cumulative_totals].[acumulado]) AS [acumulado], 
           PRINTF ('%.2f', ([subquery].[valor] / [category_totals].[total_valor_categoria]) * 100) AS [part%], 
           SUBSTR ('██████████████████████████████████████████████████████', 1, ROUND (([subquery].[valor] / [category_totals].[total_valor_categoria]) * 50)) || " (" || [subquery].[id_categoria] || ")" AS [mini_grafico], 
           [subquery].[id_categoria]
    FROM   [subquery]
           JOIN [category_totals] ON [subquery].[categoria] = [category_totals].[categoria]
           JOIN [cumulative_totals] ON [subquery].[ano] = [cumulative_totals].[ano]
                AND [subquery].[categoria] = [cumulative_totals].[categoria]
                AND [subquery].[id_tipo_operacao] = [cumulative_totals].[id_tipo_operacao]
  ),
  [blank_lines] AS(
    SELECT DISTINCT 
                    NULL AS [ano], 
                    NULL AS [Operacao], 
                    [categoria], 
                    NULL AS [valor], 
                    NULL AS [acumulado], 
                    NULL AS [part%], 
                    NULL AS [mini_grafico], 
                    [id_categoria]
    FROM   [main_query]
  )
SELECT 
       [ano], 
       [Operacao], 
       [categoria], 
       [valor], 
       [acumulado], 
       [part%], 
       [mini_grafico]
FROM   (SELECT 
               [ano], 
               [Operacao], 
               [categoria], 
               [valor], 
               [acumulado], 
               [part%], 
               [mini_grafico], 
               [id_categoria], 
               0 AS [sort_order]
        FROM   [main_query]
        UNION ALL
        SELECT 
               '***********' AS [ano], 
               '***********' AS [Operacao], 
               '***********' AS [categoria], 
               '***********' AS [valor], 
               '***********' AS [acumulado], 
               '***********' AS [part%], 
               '***********' AS [mini_grafico], 
               [id_categoria], 
               1 AS [sort_order]
        FROM   [blank_lines])
ORDER  BY
          [id_categoria],
          [sort_order],
          [ano],
          [Operacao]

Why one Query works in mvd but not in SqliteStudio?

null@example.com (kavinacomputers) — Thu, 16 May 2024 12:09:23 +0000

This query works in MVD.

@derek

select total(quantity) from transactions where transactions.id_products = products.id and quantity > 0
why no joins?

tables join auto in MVD so you guys write only some sql
if so
how I know which joins or pre provided query in MVD?

[17:25:06] Error while executing SQL query on database 'sqlite0': no such column: products.id

So I was learning sql - found join with group by

This works in SqliteStudio

SELECT products.id, products.name, SUM(transactions.quantity) AS total_quantity
FROM transactions
JOIN products ON transactions.id_products = products.id
WHERE transactions.quantity > 0
GROUP BY products.id, products.name;

Explain why?