Magic SQL switches to parameterised SQL on joins when joining on multiple fields, my JITs don't seem

rbuser

I have noticed that when i create a master/detail relationsship
between two queries on the data tab, if the master detail is using
more than 1 field linking it switches to parameterised sql and
disables the Magic SQL option.

This works OK for me BUT whenever i have created my own JITS they only
seem to be able to link succesfully when Magic SQL is in use. As such
I can't seem to create reports using these JITS joining on more than
one field.

Breakpointing in delphi in my JIT's "createData" method i can see my
master Query SQL has the paremetrized syntax, eg WHERE
tblBlah.workid=:workid, by i have not been able to find where the
paremeters are being populated, if in fact they are (These JITs wre
writen years ago when magicSQL was the only option so I may be missing
extra code).

Any hints or examples you can point me to would be much appreciated;
i've spent the afternoon going round in circles.

Report builder 17.02 enterprise, delphi 10.1

Thanks,
Chris

nicocizik

Hi Chris,


I was not able to recreate this behavior with a simple demo. The only
reason the linking should automatically change to Parameterized is if
you are manual editing the SQL. If you are able to reliably recreate
this behavior, please give me the steps to recreate it here and we'll
research the cause.


When using parameterized linking, Delphi takes care of populating the
parameter values. This is essentially the same as linking two datasets
on a Delphi form.

rbuser

Hi Nico here's an example (attached linking example.rtm):

Not sure how much use this will be to you without tables to use it
with.

Unlinked there is a simple master detail as follows (pic1.png)

There SQl statements unlinked are as follows

tblWorkorderMaster: (the intended master, will list the workID and
each plate items from that workid are nested on, along with some
header info form the workorder)

SELECT DISTINCT TBLWORKORDER.WORKID,
TBLNESTING.PLATEID,
TBLWORKORDER.INVOICENUMBER,
TBLWORKORDER.PURCHASEORDER,
TBLWORKORDER.PURCHASEORDER2ND
FROM TBLWORKORDER TBLWORKORDER
INNER JOIN TBLWORKORDERITEM TBLWORKORDERITEM ON
(TBLWORKORDERITEM.WORKID = TBLWORKORDER.WORKID)
INNER JOIN TBLNESTING TBLNESTING ON
(TBLNESTING.WOITEMID = TBLWORKORDERITEM.WOITEMID)
GROUP BY TBLWORKORDER.WORKID, TBLNESTING.PLATEID,
TBLWORKORDER.INVOICENUMBER,
TBLWORKORDER.PURCHASEORDER,
TBLWORKORDER.PURCHASEORDER2ND
ORDER BY TBLWORKORDER.WORKID, TBLNESTING.PLATEID

and tblItemDetail(the intended detail table, will show how many of
each workorder item are placed on each plate)

SELECT TBLWORKORDER.WORKID, TBLNESTING.PLATEID,
TBLNESTING.WOITEMID, TBLNESTING.NRNESTED
FROM TBLWORKORDER TBLWORKORDER
INNER JOIN TBLWORKORDERITEM TBLWORKORDERITEM ON
(TBLWORKORDERITEM.WORKID = TBLWORKORDER.WORKID)
INNER JOIN TBLNESTING TBLNESTING ON
(TBLNESTING.WOITEMID = TBLWORKORDERITEM.WOITEMID)


When I Click on the linking button on tblItemDetail i get pic2, pic3 -
it links both workid and p[plateID (desired) via parameter not
magicSQL, and magicSQL is disabled.

I can manually drag one or the other of workid, plateid across and it
creates a single magicSQL link (or parameterised, whatever was used
last, but at least allows me to change to to magicSQL).

I would like to use the magicSQL where possible as when we link to our
JITs (as we might do in further detail queries) it seems that they
work more reliably when magicSQL is in effect (and too slowly when
manual SQL linking is used)

Thanks,Chris


On Fri, 19 Aug 2016 11:07:11 -0600, "Nico Cizik (Digital Metaphors)"

nicocizik

Hi Chris,

We do not permit attachments in these newsgroups. If possible please
resend this message (along with the attachments) to
support@digital-metaphors.com and we can continue this conversation via
email.

rbuser

will do