Board index » delphi » A Question for an SQL Expert!

A Question for an SQL Expert!

Hello,

I need some help in how to implement an SQL Query using TQuery.
This is somewhat complicated and although I get no errors I also don't
really get any results either.

I have 3 tables on an AS400.
   Table1) The main table with the Permit Key, IndexCode, Username and Date

   Table2)  This is also looking at the same Data Table on the AS400.

   Table3) Is another Table with the Permit Key and A Status Field.

What I am attempting to do is the following.

A. Query on Table1 for all records that have a Match the desired IndexCode
and have a blank Username.

B. I now want to verify that the IndexCode field has had the necessary
    prior IndexCode completed. <EX: If I am querying Table1 for
IndexCode = 2, then in Table2 IndexCode = 1 has to have been completed. To
verify completion, A record should exist that has a Permit Key that matches
from Table1, a IndexCode of 1 and the Username field will not be blank

C. I also validate against Table3 using the Permit Key and check it's
     Status field to make sure it is not equal = 'HI'.

I don't know if this can be done nor do I have the background at this time
to know what steps need to be taken to accomplish this using
A TQuery and SQL.

Here is the Code I have used.
***NOTE : Index = 5 and IndxToSearch = -3

             BPData.qryAPPROVALS.Close;
             BPData.qryAPPROVALS.SQL.Clear;
             BPData.qryAPPROVALS.SQL.Add('SELECT D.BPSEQNUM, D.BPPERMIT,
M.BPOTHRIUSE, D.BPUSERNAME, D.BPENTERDAT,');
             BPData.qryAPPROVALS.SQL.Add('M.BPGRPKEY, M.BPADDRESS, M.BPDIR,
M.BPSTREET, M.BPAPT, M.BPSTATUS, M.BPCONSIZE');
             BPData.qryAPPROVALS.SQL.Add(' FROM DSDLIB/BPPDATES D,
DSDLIB/BPLDATES03 T, DSDLIB/BPPMASTER M');
             BPData.qryAPPROVALS.SQL.Add(' WHERE T.BPPERMIT = D.BPPERMIT' +
' AND D.BPSEQNUM = ' + IntToStr(Indx) +  ' AND T.BPSEQNUM = ' +
IntToStr(IndxToSearch));
             BPData.qryAPPROVALS.SQL.Add(' AND D.BPUSERNAME = ''' + '''' + '
AND T.BPUSERNAME <> ''' + '''');
             BPData.qryAPPROVALS.SQL.Add(' AND M.BPPERMIT = D.BPPERMIT AND
M.BPSTATUS <> ''' + 'HI' + '''');
             BPData.qryAPPROVALS.SQL.Add(' ORDER BY D.BPPERMIT');
BPData.qryAPPROVALS.Open;

All Help and Insight in getting this to work is greatly appreciated.

Sincerely,
Kim Hovorka
kchovo...@ci.orem.ut.us

 

Re:A Question for an SQL Expert!


On Fri, 15 Oct 1999 15:31:27 -0600, "Kim Hovorka" <kchovo...@ci.orem.ut.us>
wrote:

Quote
>I need some help in how to implement an SQL Query using TQuery.
>This is somewhat complicated and although I get no errors I also don't
>really get any results either.

>I have 3 tables on an AS400.
>   Table1) The main table with the Permit Key, IndexCode, Username and Date

>   Table2)  This is also looking at the same Data Table on the AS400.

>   Table3) Is another Table with the Permit Key and A Status Field.

>What I am attempting to do is the following.

>A. Query on Table1 for all records that have a Match the desired IndexCode
>and have a blank Username.

>B. I now want to verify that the IndexCode field has had the necessary
>    prior IndexCode completed. <EX: If I am querying Table1 for
>IndexCode = 2, then in Table2 IndexCode = 1 has to have been completed. To
>verify completion, A record should exist that has a Permit Key that matches
>from Table1, a IndexCode of 1 and the Username field will not be blank

>C. I also validate against Table3 using the Permit Key and check it's
>     Status field to make sure it is not equal = 'HI'.

>I don't know if this can be done nor do I have the background at this time
>to know what steps need to be taken to accomplish this using
>A TQuery and SQL.

>Here is the Code I have used.
>***NOTE : Index = 5 and IndxToSearch = -3

>             BPData.qryAPPROVALS.Close;
>             BPData.qryAPPROVALS.SQL.Clear;
>             BPData.qryAPPROVALS.SQL.Add('SELECT D.BPSEQNUM, D.BPPERMIT,
>M.BPOTHRIUSE, D.BPUSERNAME, D.BPENTERDAT,');
>             BPData.qryAPPROVALS.SQL.Add('M.BPGRPKEY, M.BPADDRESS, M.BPDIR,
>M.BPSTREET, M.BPAPT, M.BPSTATUS, M.BPCONSIZE');
>             BPData.qryAPPROVALS.SQL.Add(' FROM DSDLIB/BPPDATES D,
>DSDLIB/BPLDATES03 T, DSDLIB/BPPMASTER M');
>             BPData.qryAPPROVALS.SQL.Add(' WHERE T.BPPERMIT = D.BPPERMIT' +
>' AND D.BPSEQNUM = ' + IntToStr(Indx) +  ' AND T.BPSEQNUM = ' +
>IntToStr(IndxToSearch));
>             BPData.qryAPPROVALS.SQL.Add(' AND D.BPUSERNAME = ''' + '''' + '
>AND T.BPUSERNAME <> ''' + '''');
>             BPData.qryAPPROVALS.SQL.Add(' AND M.BPPERMIT = D.BPPERMIT AND
>M.BPSTATUS <> ''' + 'HI' + '''');
>             BPData.qryAPPROVALS.SQL.Add(' ORDER BY D.BPPERMIT');
>BPData.qryAPPROVALS.Open;

>All Help and Insight in getting this to work is greatly appreciated.

Could it be that you need to make that filtering comparison using
D.BPUSERNAME with the IS NULL predicate rather than the equals operator?
Empty strings are not the same as NULL values, though both comparisons are
valid SQL.

  ...
  BPData.qryAPPROVALS.SQL.Add(' AND D.BPUSERNAME IS NULL' +
    'AND T.BPUSERNAME IS NOT NULL');
  ...

All those embedded single quotation marks make your routine look too
cluttered and difficult to read. Consider using the QuotedStr function
instead to put the embedded quotation marks into the dynamically built SQL
statement.

  with BPData.qryAPPROVALS do begin
    Close;
    with SQL do begin
      Clear;
      Add('SELECT D.BPSEQNUM, D.BPPERMIT, M.BPOTHRIUSE,');
      Add('  D.BPUSERNAME, D.BPENTERDAT, M.BPGRPKEY, M.BPADDRESS,');
      Add('  M.BPDIR, M.BPSTREET, M.BPAPT, M.BPSTATUS, M.BPCONSIZE');
      Add('FROM DSDLIB/BPPDATES D, DSDLIB/BPLDATES03 T,');
      Add('  DSDLIB/BPPMASTER M');
      Add('WHERE T.BPPERMIT = D.BPPERMIT');
      Add('  AND M.BPPERMIT = D.BPPERMIT');
      Add('  AND D.BPSEQNUM = ' + IntToStr(Indx));
      Add('  AND T.BPSEQNUM = ' + IntToStr(IndxToSearch));
      Add('  AND D.BPUSERNAME IS NULL');
      Add('  AND T.BPUSERNAME IS NOT NULL');
      Add('  AND M.BPSTATUS <> ' + QuotedStr('HI'));
      Add('ORDER BY D.BPPERMIT');
    end;
    Open;
  end;

Then, visually verify that the SQL statement that you are dynamically
building with that Delphi code is what you expected it to be. One way to do
that is to display the SQL statement using the ShowMessage procedure.

  ShowMessage(BPData.qryAPPROVALS.SQL.Text);

If you are still not getting any rows in the result set, try simplifying
your somewhat complex SQL statement. For instance, you might start with
just the syntax needed to join the tables. For example:

  with BPData.qryAPPROVALS do begin
    Close;
    with SQL do begin
      Clear;
      Add('SELECT D.BPSEQNUM, D.BPPERMIT, M.BPOTHRIUSE,');
      Add('  D.BPUSERNAME, D.BPENTERDAT, M.BPGRPKEY, M.BPADDRESS,');
      Add('  M.BPDIR, M.BPSTREET, M.BPAPT, M.BPSTATUS, M.BPCONSIZE');
      Add('FROM DSDLIB/BPPDATES D, DSDLIB/BPLDATES03 T,');
      Add('  DSDLIB/BPPMASTER M');
      Add('WHERE T.BPPERMIT = D.BPPERMIT');
      Add('  AND M.BPPERMIT = D.BPPERMIT');
    end;
    Open;
  end;

Test the query. If it returns data, add one new filtering criteria.
Continue this process of incrementally complicating the query and testing
it until you have either added all elements back in or the query fails
again. The point at which it fails should give some indication of where to
go (what to adjust) from there.

==========================================================================
Steve Koterski                  "Computers are useless. They can only give
Technical Publications          you answers."
Borland                                       -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi

Re:A Question for an SQL Expert!


Hi Kim,

I wanted to clean up your query to make it easier to understand.  This
is how it would look if you had entered it by using the TQuery.SQL
editor, using parameters.  It is much easier to test in this form.
You set appropriate values for the INDEX and SEARCHINDEX parameters
and set the Active property to True.  If link a DBGrid to your query,
you can see the results ar design time.  I suggest that you create a
small project that you can use for testing queries.

SELECT D.BPSEQNUM, D.BPPERMIT, M.BPOTHRIUSE, D.BPUSERNAME,
       D.BPENTERDAT, M.BPGRPKEY, M.BPADDRESS, M.BPDIR,
       M.BPSTREET, M.BPAPT, M.BPSTATUS, M.BPCONSIZE
  FROM DSDLIB/BPPDATES D, DSDLIB/BPLDATES03 T, DSDLIB/BPPMASTER M
 WHERE T.BPPERMIT = D.BPPERMIT
   AND D.BPSEQNUM = :INDEX AND T.BPSEQNUM = :SEARCHINDEX
   AND D.BPUSERNAME = '' AND T.BPUSERNAME <> ''
   AND M.BPPERMIT = D.BPPERMIT AND M.BPSTATUS <> 'HI'
 ORDER BY D.BPPERMIT

As Steve suggested, the problem may lie with the following line:
   AND D.BPUSERNAME = '' AND T.BPUSERNAME <> ''
My understanding of DB2 on the AS400 is that you don't have null
values, and that you check for empty fields this way.  My experience
is limited to rather horrible Signon databases, so I may be wrong.

What may be causing your problem is if the two fields are filled with
blank characters.  The comparison may not work then.

What I suggest is that you remove various conditions until you find
something that works.  Here are a few possibilities:
 - There may be no matching DSDLIB/BPPMASTER records
 - The matching DSDLIB/BPPMASTER records may all have HI in the
BPSTATUS column
 - There may be no DSDLIB/BPLDATES03 records with the given
IndxToSearch
 - There may be no DSDLIB/BPPDATES records with the given Indx
 - Are you sure that DSDLIB/BPLDATES03 contains all the data (I assume
that it is a View of DSDLIB/BPLDATES)

If the two tables are the same, why use the second one at all?  SQL
allows you to use a table as often as you need if you use alias names
as you have done.

Just another comment on your coding style.  If you must use inline
queries, the following layout is much easier to understand.  It is
also easier to edit out the Delphi bits if you want to test your query
with Database Explorer:

const sqApprovals =
    'SELECT D.BPSEQNUM, D.BPPERMIT, M.BPOTHRIUSE,'#13#10 +
    '       D.BPUSERNAME, D.BPENTERDAT, M.BPGRPKEY,'#13#10 +
    '       M.BPADDRESS, M.BPDIR, M.BPSTREET, M.BPAPT,'#13#10 +
    '       M.BPSTATUS, M.BPCONSIZE'#13#10 +
    '  FROM DSDLIB/BPPDATES D, DSDLIB/BPLDATES03 T,'#13#10 +
    '       DSDLIB/BPPMASTER M'#13#10 +
    ' WHERE T.BPPERMIT = D.BPPERMIT'#13#10 +
    '   AND D.BPSEQNUM = %d AND T.BPSEQNUM = %d'#13#10 +
    '   AND D.BPUSERNAME = '''' AND T.BPUSERNAME <> '''''#13#10 +
    '   AND M.BPPERMIT = D.BPPERMIT AND M.BPSTATUS <> ''HI'''#13#10 +
    ' ORDER BY D.BPPERMIT';

...

             BPData.qryAPPROVALS.Close;
             BPData.qryAPPROVALS.SQL.Clear;
             BPData.qryAPPROVALS.SQL.Text :=
               Format(sqApprovals, [Indx, IndxToSearch]);
             BPData.qryAPPROVALS.Open;

A few comments:
 - The #13#10 at the end of each line are carriage return/line feed
characters, to ensure that the DBMS sees the text the same way as you
do.  If you prefer not using them, make sure that you put at least one
space before the final quote on each line.
 - If you have quotes in a Delphi string, just double them up.  This
is a lot easier to read than your code.
 - I used the Format function to insert the variable values at the
proper locations.  The %d tells Format to put a decimal value at that
point.  I also use %s for string values.  Any additional formatting
(e.g. DateToStr) can be done in the Format values list.

My basic suggestion is to extract your query from the Delphi code and
to test it by itself.  When you have it right, you then put it back
into your program.  The best place for queries is in the query's SQL
property so that you can work on it more easily.

Write a small stand alone program that you can use for testing
queries.

Hope this helps.

Jamie O

Other Threads