Oracle and master-detail queries

Problem/Question/Abstract:

Oracle and master-detail queries

Answer:

Just the other day I was writing an application that had master-detail queries, using the TQuery component. The queries were made against an Oracle 8 database using BDE and Delphi 3, but the problem was also present on Oracle 7 and in different oracle set ups. The picture was the following:

QUERY1 (master):

SELECT
   A.NAME, A.CODE, B.AREA
FROM
   CLIENTS A, AREAS B
WHERE
   (A.AREACODE = B.CODE)

Note: CODE is CHAR(10)

QUERY2 (set as detail of QUERY1):

SELECT
   A.ORDERNUM, A.DATE, A.VALUE
FROM
   ORDERS A
WHERE
   (A.CLIENTCODE = :CODE)

Note: CLIENTCODE is CHAR(10)

The problem is that even 'though all the clients with a valid AREACODE would be listed, no order for that client would be listed, even if there were data in the ORDERS table for all the customers. The QUERY2 dataset was always empty (yes, it was Active).

FIXED QUERY2 (set as detail of QUERY1):

SELECT
   A.ORDERNUM, A.DATE, A.VALUE
FROM
   ORDERS A
WHERE
   (RTRIM(A.CLIENTCODE) = RTRIM(:CODE))

This fixed once and for all the problem, and all the orders for each customer were correctly returned by the query. It seemed that when the parameter was passed, it's data type was changed, or some padding was added to the field. Anyway, trimming both fields and comparing only the data part worked.