Customized master-detail query

rbuser

I need to generate a report with a specialized master-detail query. The
join is of type DetailTable.Index LIKE Master.Value + '%'.
In other words for each Index field in the Master table I want to get a
summary based on that Index (using the LIKE operator). The problem with a
reqular master detail relationship is that the join is Master.ID =
Detail.ID.

How can I achieve this? It would be great if this could be designed using
end-user reporting?

I know you can link to datasets in the designer but that essentially uses
the "=" operator and not the "LIKE" operator

digitalmetaphors

While this type of query is legal it is not possible with the query
designer. In order for you to select values from multiple tables you are
required to perform a join and LIKE is not one of the operators allowed by
the designer. The query you have in mind looks something like the following.
Assume I have a table of words and a table of prefixes, the query below
would retrieve for every prefix every word that begins with that prefix and
creates a row out of every combination.
SELECT Prefixes.Prefix, Words.Word

FROM Prefixes, Words

WHERE Words.Word LIKE Prefixes.Prefix + '%'

ORDER BY Words.Word

You can certainly implement this query in a Delphi query object. You can
'trick' DADE into doing it by editing the query manually. You then however
lose the advantages provided to you by using DADE.


--
Cheers,


Alexander Kramnik
Digital Metaphors

rbuser

Thanks. When you say "trick" do you mean use the "Edit SQL" and paste the
SQL statement into the designer?
If so, yes, it would be unfortunate to lose the DADE.

"Alexander Kramnik (Digital Metaphors)" wrote

digitalmetaphors

Exactly. As long as your database supports that syntax since DADE does not
validate the statement once you edit it manually.

--
Cheers,

Alexander Kramnik
Digital Metaphors