Home General

Wrong SQL when using a single entry and a criteria value that contains a space

edited November 2016 in General
Hi,

ReportBuilder version 17.03
I have a simple report that contains a 'In list" criteria, a single
default value is provided [In dienst], the value is provided without
the brackets but it contains a space.

The resulting SQL is
SELECT werknemers.achternaam,
werknemers.werknemerid, werknemers.status
FROM werknemers werknemers
WHERE ( werknemers.status IN ('In dienst
') )
ORDER BY werknemers.achternaam

Which returns no rows.

If I add another dummy varibale to the in list criteria the resulting
sql is
SELECT werknemers.achternaam,
werknemers.werknemerid, werknemers.status
FROM werknemers werknemers
WHERE ( werknemers.status IN ('In dienst','a') )
ORDER BY werknemers.achternaam

which returns the right data

If I try to add another dummy criteria which contains a space it also
works
SELECT werknemers.achternaam,
werknemers.werknemerid, werknemers.status
FROM werknemers werknemers
WHERE ( werknemers.status IN ('In dienst','a a') )
ORDER BY werknemers.achternaam

Why is, in the first example, the closing apostroph placed on a
newline?


regards
Paul

Comments

  • edited November 2016
    Some more information:
    In design mode the SQL is correct:

    SELECT werknemers.achternaam,
    werknemers.werknemerid, werknemers.status
    FROM werknemers werknemers
    WHERE ( werknemers.status IN ('In dienst') )
    ORDER BY werknemers.achternaam

    regards
    Paul

    On Mon, 14 Nov 2016 07:43:45 +0100, Paul Sjoerdsma
  • edited November 2016
    Hi Paul,

    Please provide steps to reproduce or create a simple example project that I
    can build and run in the debugger. For example projects, please use standard
    Delphi components and RB. Use the RBDemos database. Send in zip format to
    support@. Include only .dpr, .pas, .dfm



    Best regards,

    -
    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
This discussion has been closed.