Problem with search in browse form

Gnu · Unread post by **Gnu** » Fri Apr 05, 2024 11:50 am

Hi, I have the problem that in a browse form with aggregated data, the search function generates an incorrect SQL query.
Here is my query for the browse form:

Code: Select all

SELECT *
FROM
  (SELECT a.ar_id AS ar_id,
          a.JahrMonat AS JahrMonat,
          a.saldo_re AS saldo_re,
          a.saldo_in AS saldo_in,
          a.saldo_offen AS saldo_offen,
          b.saldo_in_mo AS saldo_in_mo,
          c.saldo_auf_mo AS saldo_auf_mo
   FROM
     (SELECT ar.ar_id,
             DATE_FORMAT(ar.ar_dat, "%Y-%m") AS JahrMonat,
             SUM(CASE WHEN ar.ar_z_sghg IS NOT NULL THEN ar.ar_net ELSE 0 END) AS saldo_in,
             SUM(CASE WHEN ar.ar_z_sghg IS NULL THEN ar.ar_net ELSE 0 END) AS saldo_offen,
             SUM(ar.ar_net) AS saldo_re
      FROM ar
      GROUP BY JahrMonat) AS a
   LEFT JOIN
     (SELECT ar.ar_id,
             DATE_FORMAT(ar.ar_z_sghg, "%Y-%m") AS JahrMonat,
             SUM(CASE WHEN ar.ar_z_sghg IS NOT NULL THEN ar.ar_net ELSE 0 END) AS saldo_in_mo
      FROM ar
      GROUP BY JahrMonat) AS b ON (a.JahrMonat = b.JahrMonat)
   LEFT JOIN
     (SELECT az.az_id,
             DATE_FORMAT(az.az_start, "%Y-%m") AS JahrMonat,
             SUM(coalesce(az.az_vas,0)+coalesce(az.az_vsb,0)) AS saldo_auf_mo
      FROM az
      GROUP BY JahrMonat) AS c ON (a.JahrMonat = c.JahrMonat))T
ORDER BY JahrMonat DESC

For example, if I search for "2024", the following SQL query is generated by nuBuilder, which does not lead to any search results:

Code: Select all

SELECT ar_id,
       JahrMonat,
       saldo_auf_mo,
       saldo_re,
       saldo_in,
       saldo_offen,
       saldo_in_mo
FROM
  (SELECT a.ar_id AS ar_id,
          a.JahrMonat AS JahrMonat,
          a.saldo_re AS saldo_re,
          a.saldo_in AS saldo_in,
          a.saldo_offen AS saldo_offen,
          b.saldo_in_mo AS saldo_in_mo,
          c.saldo_auf_mo AS saldo_auf_mo
   FROM
     (SELECT ar.ar_id,
             DATE_FORMAT(ar.ar_dat, "%Y-%m") AS JahrMonat,
             SUM(CASE WHEN ar.ar_z_sghg IS NOT NULL THEN ar.ar_net ELSE 0 END) AS saldo_in,
             SUM(CASE WHEN ar.ar_z_sghg IS NULL THEN ar.ar_net ELSE 0 END) AS saldo_offen,
             SUM(ar.ar_net) AS saldo_re
      FROM ar
      WHERE 1
        AND ((CONVERT(JahrMonat USING utf8) LIKE "%2024%"
              OR CONVERT(saldo_auf_mo USING utf8) LIKE "%2024%"
              OR CONVERT(saldo_re USING utf8) LIKE "%2024%"
              OR CONVERT(saldo_in USING utf8) LIKE "%2024%"
              OR CONVERT(saldo_offen USING utf8) LIKE "%2024%"
              OR CONVERT(saldo_in_mo USING utf8) LIKE "%2024%"))
      GROUP BY JahrMonat) AS a
   LEFT JOIN
     (SELECT ar.ar_id,
             DATE_FORMAT(ar.ar_z_sghg, "%Y-%m") AS JahrMonat,
             SUM(CASE WHEN ar.ar_z_sghg IS NOT NULL THEN ar.ar_net ELSE 0 END) AS saldo_in_mo
      FROM ar
      GROUP BY JahrMonat) AS b ON (a.JahrMonat = b.JahrMonat)
   LEFT JOIN
     (SELECT az.az_id,
             DATE_FORMAT(az.az_start, "%Y-%m") AS JahrMonat,
             SUM(coalesce(az.az_vas,0)+coalesce(az.az_vsb,0)) AS saldo_auf_mo
      FROM az
      GROUP BY JahrMonat) AS c ON (a.JahrMonat = c.JahrMonat))T
ORDER BY JahrMonat DESC

After changing as follows, the expected search results are found:

Code: Select all

SELECT ar_id,
       JahrMonat,
       saldo_auf_mo,
       saldo_re,
       saldo_in,
       saldo_offen,
       saldo_in_mo
FROM
  (SELECT a.ar_id AS ar_id,
          a.JahrMonat AS JahrMonat,
          a.saldo_re AS saldo_re,
          a.saldo_in AS saldo_in,
          a.saldo_offen AS saldo_offen,
          b.saldo_in_mo AS saldo_in_mo,
          c.saldo_auf_mo AS saldo_auf_mo
   FROM
     (SELECT ar.ar_id,
             DATE_FORMAT(ar.ar_dat, "%Y-%m") AS JahrMonat,
             SUM(CASE WHEN ar.ar_z_sghg IS NOT NULL THEN ar.ar_net ELSE 0 END) AS saldo_in,
             SUM(CASE WHEN ar.ar_z_sghg IS NULL THEN ar.ar_net ELSE 0 END) AS saldo_offen,
             SUM(ar.ar_net) AS saldo_re
      FROM ar
      GROUP BY JahrMonat) AS a
   LEFT JOIN
     (SELECT ar.ar_id,
             DATE_FORMAT(ar.ar_z_sghg, "%Y-%m") AS JahrMonat,
             SUM(CASE WHEN ar.ar_z_sghg IS NOT NULL THEN ar.ar_net ELSE 0 END) AS saldo_in_mo
      FROM ar
      GROUP BY JahrMonat) AS b ON (a.JahrMonat = b.JahrMonat)
   LEFT JOIN
     (SELECT az.az_id,
             DATE_FORMAT(az.az_start, "%Y-%m") AS JahrMonat,
             SUM(coalesce(az.az_vas,0)+coalesce(az.az_vsb,0)) AS saldo_auf_mo
      FROM az
      GROUP BY JahrMonat) AS c ON (a.JahrMonat = c.JahrMonat))T
WHERE 1
  AND ((CONVERT(JahrMonat USING utf8) LIKE "%2024%"
        OR CONVERT(saldo_auf_mo USING utf8) LIKE "%2024%"
        OR CONVERT(saldo_re USING utf8) LIKE "%2024%"
        OR CONVERT(saldo_in USING utf8) LIKE "%2024%"
        OR CONVERT(saldo_offen USING utf8) LIKE "%2024%"
        OR CONVERT(saldo_in_mo USING utf8) LIKE "%2024%"))
ORDER BY JahrMonat DESC

The search conditions may only be inserted at the end of the query.

Is this a bug, or how can I achieve the desired result?

Unread post by **kev1n** » Fri Apr 05, 2024 1:58 pm

Try changing this line

Code: Select all

GROUP BY JahrMonat) AS c ON (a.JahrMonat = c.JahrMonat)) T

into:

Code: Select all

GROUP BY JahrMonat) AS c ON (a.JahrMonat = c.JahrMonat)) T WHERE 1

Gnu · Unread post by **Gnu** » Fri Apr 05, 2024 4:10 pm

When I use:

Code: Select all

SELECT *
FROM
  (SELECT a.ar_id AS ar_id,
          a.JahrMonat AS JahrMonat,
          a.saldo_re AS saldo_re,
          a.saldo_in AS saldo_in,
          a.saldo_offen AS saldo_offen,
          b.saldo_in_mo AS saldo_in_mo,
          c.saldo_auf_mo AS saldo_auf_mo
   FROM
     (SELECT ar.ar_id,
             DATE_FORMAT(ar.ar_dat, "%Y-%m") AS JahrMonat,
             SUM(CASE WHEN ar.ar_z_sghg IS NOT NULL THEN ar.ar_net ELSE 0 END) AS saldo_in,
             SUM(CASE WHEN ar.ar_z_sghg IS NULL THEN ar.ar_net ELSE 0 END) AS saldo_offen,
             SUM(ar.ar_net) AS saldo_re
      FROM ar
      GROUP BY JahrMonat) AS a
   LEFT JOIN
     (SELECT ar.ar_id,
             DATE_FORMAT(ar.ar_z_sghg, "%Y-%m") AS JahrMonat,
             SUM(CASE WHEN ar.ar_z_sghg IS NOT NULL THEN ar.ar_net ELSE 0 END) AS saldo_in_mo
      FROM ar
      GROUP BY JahrMonat) AS b ON (a.JahrMonat = b.JahrMonat)
   LEFT JOIN
     (SELECT az.az_id,
             DATE_FORMAT(az.az_start, "%Y-%m") AS JahrMonat,
             SUM(coalesce(az.az_vas,0)+coalesce(az.az_vsb,0)) AS saldo_auf_mo
      FROM az
      GROUP BY JahrMonat) AS c ON (a.JahrMonat = c.JahrMonat)
)T WHERE 1
ORDER BY JahrMonat DESC

then I get in the Form without searching:

Code: Select all

SELECT ar_id,
       JahrMonat,
       saldo_auf_mo,
       saldo_re,
       saldo_in,
       saldo_offen,
       saldo_in_mo
FROM
  (SELECT a.ar_id AS ar_id,
          a.JahrMonat AS JahrMonat,
          a.saldo_re AS saldo_re,
          a.saldo_in AS saldo_in,
          a.saldo_offen AS saldo_offen,
          b.saldo_in_mo AS saldo_in_mo,
          c.saldo_auf_mo AS saldo_auf_mo
   FROM
     (SELECT ar.ar_id,
             DATE_FORMAT(ar.ar_dat, "%Y-%m") AS JahrMonat,
             SUM(CASE WHEN ar.ar_z_sghg IS NOT NULL THEN ar.ar_net ELSE 0 END) AS saldo_in,
             SUM(CASE WHEN ar.ar_z_sghg IS NULL THEN ar.ar_net ELSE 0 END) AS saldo_offen,
             SUM(ar.ar_net) AS saldo_re
      FROM ar
      GROUP BY JahrMonat) AS a
   LEFT JOIN
     (SELECT ar.ar_id,
             DATE_FORMAT(ar.ar_z_sghg, "%Y-%m") AS JahrMonat,
             SUM(CASE WHEN ar.ar_z_sghg IS NOT NULL THEN ar.ar_net ELSE 0 END) AS saldo_in_mo
      FROM ar
      GROUP BY JahrMonat) AS b ON (a.JahrMonat = b.JahrMonat)
   LEFT JOIN
     (SELECT az.az_id,
             DATE_FORMAT(az.az_start, "%Y-%m") AS JahrMonat,
             SUM(coalesce(az.az_vas,0)+coalesce(az.az_vsb,0)) AS saldo_auf_mo
      FROM az
      GROUP BY JahrMonat) AS c ON (a.JahrMonat = c.JahrMonat))T
WHERE 1
GROUP BY JahrMonat) AS a
LEFT JOIN
  (SELECT ar.ar_id,
          DATE_FORMAT(ar.ar_z_sghg, "%Y-%m") AS JahrMonat,
          SUM(CASE WHEN ar.ar_z_sghg IS NOT NULL THEN ar.ar_net ELSE 0 END) AS saldo_in_mo
   FROM ar
   GROUP BY JahrMonat) AS b ON (a.JahrMonat = b.JahrMonat)
LEFT JOIN
  (SELECT az.az_id,
          DATE_FORMAT(az.az_start, "%Y-%m") AS JahrMonat,
          SUM(coalesce(az.az_vas,0)+coalesce(az.az_vsb,0)) AS saldo_auf_mo
   FROM az
   GROUP BY JahrMonat) AS c ON (a.JahrMonat = c.JahrMonat) )T
WHERE 1
ORDER BY JahrMonat DESC

an with search term "2024":

Code: Select all

SELECT ar_id,
       JahrMonat,
       saldo_auf_mo,
       saldo_re,
       saldo_in,
       saldo_offen,
       saldo_in_mo
FROM
  (SELECT a.ar_id AS ar_id,
          a.JahrMonat AS JahrMonat,
          a.saldo_re AS saldo_re,
          a.saldo_in AS saldo_in,
          a.saldo_offen AS saldo_offen,
          b.saldo_in_mo AS saldo_in_mo,
          c.saldo_auf_mo AS saldo_auf_mo
   FROM
     (SELECT ar.ar_id,
             DATE_FORMAT(ar.ar_dat, "%Y-%m") AS JahrMonat,
             SUM(CASE WHEN ar.ar_z_sghg IS NOT NULL THEN ar.ar_net ELSE 0 END) AS saldo_in,
             SUM(CASE WHEN ar.ar_z_sghg IS NULL THEN ar.ar_net ELSE 0 END) AS saldo_offen,
             SUM(ar.ar_net) AS saldo_re
      FROM ar
      GROUP BY JahrMonat) AS a
   LEFT JOIN
     (SELECT ar.ar_id,
             DATE_FORMAT(ar.ar_z_sghg, "%Y-%m") AS JahrMonat,
             SUM(CASE WHEN ar.ar_z_sghg IS NOT NULL THEN ar.ar_net ELSE 0 END) AS saldo_in_mo
      FROM ar
      GROUP BY JahrMonat) AS b ON (a.JahrMonat = b.JahrMonat)
   LEFT JOIN
     (SELECT az.az_id,
             DATE_FORMAT(az.az_start, "%Y-%m") AS JahrMonat,
             SUM(coalesce(az.az_vas,0)+coalesce(az.az_vsb,0)) AS saldo_auf_mo
      FROM az
      GROUP BY JahrMonat) AS c ON (a.JahrMonat = c.JahrMonat))T
WHERE 1
  AND ((CONVERT(JahrMonat USING utf8) LIKE "%2024%"
        OR CONVERT(saldo_auf_mo USING utf8) LIKE "%2024%"
        OR CONVERT(saldo_re USING utf8) LIKE "%2024%"
        OR CONVERT(saldo_in USING utf8) LIKE "%2024%"
        OR CONVERT(saldo_offen USING utf8) LIKE "%2024%"
        OR CONVERT(saldo_in_mo USING utf8) LIKE "%2024%"))
GROUP BY JahrMonat) AS a
LEFT JOIN
  (SELECT ar.ar_id,
          DATE_FORMAT(ar.ar_z_sghg, "%Y-%m") AS JahrMonat,
          SUM(CASE WHEN ar.ar_z_sghg IS NOT NULL THEN ar.ar_net ELSE 0 END) AS saldo_in_mo
   FROM ar
   GROUP BY JahrMonat) AS b ON (a.JahrMonat = b.JahrMonat)
LEFT JOIN
  (SELECT az.az_id,
          DATE_FORMAT(az.az_start, "%Y-%m") AS JahrMonat,
          SUM(coalesce(az.az_vas,0)+coalesce(az.az_vsb,0)) AS saldo_auf_mo
   FROM az
   GROUP BY JahrMonat) AS c ON (a.JahrMonat = c.JahrMonat) )T
WHERE 1
ORDER BY JahrMonat DESC

Both does not work.

Unread post by **kev1n** » Fri Apr 05, 2024 4:20 pm

nuBuilder's query parser is somewhat limited and can't handle complex queries.

However, there are 2 ways to work around this:

1. Move the SQL to a view
2. Use a temporary table (e.g. see here)

Gnu · Unread post by **Gnu** » Mon Apr 08, 2024 6:08 pm

Using a temporary table i got it going!
Thanks for the link!

nuBuilder Forum

Problem with search in browse form Topic is solved

Problem with search in browse form

Re: Problem with search in browse form

Re: Problem with search in browse form

Re: Problem with search in browse form

Re: Problem with search in browse form