Home General
New Blog Posts: Merging Reports - Part 1 and Part 2

Erroneous SQL when using Parameterized SQL data linking

edited May 2019 in General
Hi,

Using RB19.04

In a report I need to add some manual crafted sql. This query should be connected to a master query using DADE linking using parameterized sql. The child query is

select
r.id,
r.omschrijving,
r.aantal,
info.*
from
orders o
inner join orderdeel d
on (d.orderid = o.id)
inner join orderregel r
on (r.orderdeel = d.id)
left outer join some_sp(r.id) info
on (1=1)
where
o.id = :id
-- some other criteria
order by
d.orderid,
d.volgorde,
r.orderdeel,
r.volgorde
The master query contains a field called ID. When I create a link master.ID = detail.ID, RB is adding 'and (ID = :ID)' to the query. In RB18.04 this was even 'Where ID = :ID'. In this case that is not working because multiple tables in the query have an ID field.
Isn't it possible to have RB only add the extra criteria then the original query doesn't contain a parameter equal to the linkfield(s)? That way we could have the best of both worlds.
Kind regards,

Jeroen Röttink
JR-soft software solutions

Comments

  • Hi Jeroen,

    Can you create a simple example using the rbDemos data and send to support@. I'll research it.

    Apologies, I lost track of this post.


    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited June 2019
    FYI: I send a small example to support@ on Monday.
    Kind regards,

    Jeroen Röttink
    JR-soft software solutions
  • Hi Jeroen,

    Thanks for providing the example. I'll follow up via email.

    This is beyond the scope of what's currently supported, but something we can consider improving in the future. It's a challenging issue.

    For now, try using the Manual SQL linking option. This requires selecting all records required for the report and ordering by the linking field(s).



    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
Sign In or Register to comment.