[fpc-pascal] Create dynamic SQL according to available params

[Michael Thompson]

On 7 April 2016 at 20:00, Luiz Americo Pereira Camara <
luizamericop at gmail.com> wrote:
> Is there any code that given a SQL Template would generate the second
filter when paramy is available and keep blank when not available?

Nope, or at least I don't think so.  I've spent a long time looking as
well.  Been meaning to bring up the discussion either here or in the forum

I ended up implementing my own "Macro" functionality.  Unfortunately I've
broken a few rules and tied the functionality to the UI, making it hard to
share.
I need this all the time.  My primary usage is in Master-Detail
relationships, where I always want the Master to contain an "ALL" option.
To achieve I write SQL's like...

   Select Incident_Type As "Incident_Type",
           Description As "Description",
           'Where Incident_Type=''' || Incident_Type || '''' As "Filter_ID"
    From PIG
    Union
    Select
         '(ALL)' As "Incident_Type",
         'All Types' As "Description",
         '' As "Filter_ID"
    From DUAL

The first Select returns the Master Table, the second Select appends my
ALL.  Note that "Filter_ID" contains my full SQL Where clause.

Over in my Detail SQL, I can write stuff like

    Select *
    From Incident
    :Filter_ID
    Order By Incident_Type, Incident

I implement the detail refresh myself (easy), and I implement the Macro
substitution myself before executing the Detail SQL.  (Any SQL field ending
in _ID is automatically hidden in my code)

I use this so often, and in so many different ways (Macro's don't have to
contain Where clauses, they can be any SQL elements, including simple
data), that I cannot consider SQL development without it.  And as such,
I've often wondered how do others deal with this...

Mike
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20160407/0c241323/attachment.html>