BCS Multiple Table Database Query

There are those time when several tables must be combined to complete a reporting retirement. When those situation arise simply use a query to select all the data elements from each table. If there are multiple records in the table the where clause should be used in each primary key so only the record desired are returned in the results. The query also be parameterized so the key variable are seamlessly applied to the query.
The example below will demonstrate the overall desired effect.
[codesyntax lang=”sql”]

select * from cd01,cd02,cd03 where did = :rdid and motid = :rmotid and sigid = 1

[/codesyntax]
The fields prefixed by the colon (:) are the parameters.  Make sure parameters are of the respective type and are defined prior to invoking the code to start the result retrieval.
The following snippet shows how to actually invoke the parameterized query.
[codesyntax lang=”delphi”]

{*-----------------------------------------------------------------------------
  Procedure: RunReps1Click
  Date:      13-Dec-2013
  @Param     Sender: TObject
  @Return    None
-----------------------------------------------------------------------------}
procedure TBCSCcXqtC.RunReps1Click(Sender: TObject);
begin
  if BCSCcHeadCmp1.Execute then
  begin
    if BCSCcBodyCmp1.Execute then
    begin
      UniQuery1.Close;
      UniQuery1.ParamByName('rdid').AsInteger := BCSCcHeadCmp1.RDid;
      UniQuery1.ParamByName('rmotid').AsInteger := BCSCcBodyCmp1.RMot;
      // UniQuery1.ParamByName('rsigid').AsInteger := 1;
      UniQuery1.Open;
      frxReport2.ShowReport(true);
    end;
  end;

[/codesyntax]
The first component selects the document id or RDid while the second component sets the motion parameter for the query to be set RMot.   The query is closed, the parameters are set, the query is opened and the report is generated.
Parameterized queries are very useful to positioning data in a table and returning a queried result.  To achieve this level of functionality with table components would be difficult if not impossible.
Mr. Arch Brooks, Software Engineer, Brooks Computing Systems, LLC authored this article.