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

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

edited August 2016 in DADE
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

Comments

  • edited August 2016
    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.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited August 2016

    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)"
  • edited August 2016
    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.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited August 2016
    will do
This discussion has been closed.