Erroneous SQL when using Parameterized SQL data linking

jeroenrottink · May 2019

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.

nardmoseley · May 2019

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.

jeroenrottink · June 2019

FYI: I send a small example to support@ on Monday.

nardmoseley · June 2019

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).

Erroneous SQL when using Parameterized SQL data linking

Comments