Please help with BDE SQL !!! Maybe BUG in BDE? 
Author Message
 Please help with BDE SQL !!! Maybe BUG in BDE?

Please help! The following SQL works but it seems not to display records if
either t1.a or t1.b are blank.
Actually t2.a and t2.b are the same as t1.a and t1.b since the table aliases
point to the same table (t1 = t2 = table).

select t1.a, t1.b
from table t1
where exists (
  select t2.a, t2.b
  from table t2
  where (t2.a = t1.a) and (t2.b = t1.b)
  )

The whole SQL is:

select t1.a, t1.b
from table t1
where exists (
  select t2.a, t2.b
  from table t2
  where (t2.a = t1.a) and (t2.b = t1.b)
  group by t2.a, t2.b
  having sum(t2.c) != 0
  )



Sat, 28 Apr 2001 03:00:00 GMT  
 Please help with BDE SQL !!! Maybe BUG in BDE?


Quote:
>Please help! The following SQL works but it seems not to display records if
>either t1.a or t1.b are blank.
>Actually t2.a and t2.b are the same as t1.a and t1.b since the table aliases
>point to the same table (t1 = t2 = table).

One key bit of information missing from your post is what database type is
used here. If it is Paradox, dBASE, or FoxPro, then local SQL (the BDE
implementation of SQL) is used. If it is any other database type, the
operation is subject to the capabilities and limitations of the database
back-end -- not the BDE. So if something were going wrong with valid SQL,
you would need to look to the database system or its vendor for the
solution.

If you are using a local database type, the version of the BDE could be a
critical factor. Obviously, you are using 32-bit BDE (nested SELECT
queries). But what version of 32-bit BDE? 3.x, 4.x, or 5.x?

Quote:
>select t1.a, t1.b
>from table t1
>where exists (
>  select t2.a, t2.b
>  from table t2
>  where (t2.a = t1.a) and (t2.b = t1.b)
>  )

Assuming as you say that the token "Table" is the same table, in two
instances, one would assume that all rows in the first instance would be
returned by the above statement. The two columns A and B would produce four
combinations of data:

1. Both A and B contain a NULL value.
2. A contains a non-NULL value and B contains a NULL value.
3. A contains a NULL value and B contains a non-NULL value.
4. Both A and B contain a non-NULL value.

BUT, because these are two instances of the same base table, there should
be a direct, one-for-one match of rows between the two tables. That is, for
any given row in the first instance, there should be the same row in the
second instance.

So in actuality, what is being returned? No rows at all? Some rows? If only
some rows, what is the correlation between the rows not returned? What do
the returned rows have in common?

It seems to me that your first statement could be accomplished with an
inner join -- and the performance versus an EXISTS query would be
considerably better.

  SELECT t1.a, t1.b
  FROM table t1, table t2
  WHERE (t1.a = t2.a) AND (t1.b = t2.b)

Quote:
>The whole SQL is:

>select t1.a, t1.b
>from table t1
>where exists (
>  select t2.a, t2.b
>  from table t2
>  where (t2.a = t1.a) and (t2.b = t1.b)
>  group by t2.a, t2.b
>  having sum(t2.c) != 0
>  )

This statement is another matter altogether. It bears little in common with
the first statement as regards conditions that would include rows in or
exclude rows from the result set. In addition to simple commonality of
column values between the two instances of the table, you now introduce a
condition based on aggregation.

The first thing I would suggest is separating the subquery out and testing
it separately to ensure it is returning the result set you expect and need.

  SELECT t2.a, t2.b, SUM(t2.c)
  FROM table t2
  GROUP BY t2.a, t2.b
  HAVING SUM(t2.c) != 0

What this says to me is that for any distinct combination of the values in
the T2.A and T2.B columns, there would be one row in the result set. Of
that set of rows, only those where the summing of the T2.C column produces
a nonzero value are included in the final result set. Visually associate
the values in the T2.A and T2.B columns for these rows with any rows
returned by the original statement. Is there a direct correlation?

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "The knowledge of the world is only to
Technical Publications              be acquired in the world, and not in a
INPRISE Corporation                 closet."
http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)



Sat, 28 Apr 2001 03:00:00 GMT  
 Please help with BDE SQL !!! Maybe BUG in BDE?

Thank you for your extensive reply. My fault was that I tried to simplify and internationalize the matter so I stripped the SQL of essential details. I submit the actual SQL in its original form with additional information.

The database:  Delphi 4.02, BDE 5.0, Paradox tables.

The SQL:

SELECT *
FROM dnevnik d1
WHERE EXISTS (
    SELECT d2.vdokvrsta, d2.vdokbroj, d2.konto, SUM(d2.dug), SUM(d2.pot)
    FROM dnevnik d2
    WHERE (d2.vdokvrsta = d1.vdokvrsta)
        AND (d2.vdokbroj = d1.vdokbroj)
        AND (d2.konto = d1.konto)
    GROUP BY d2.vdokvrsta, d2.vdokbroj, d2.konto
    HAVING (SUM(d2.dug) - SUM(d2.pot)) != 0)

The table dnevnik:

vdokvrsta    vdokbroj    konto        dug        pot
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A            1           1000    1.000,00       0,00
A            1           1000        0,00   1.000,00
A            1           120       800,00       0,00
A            2           1000    2.000,00       0,00
A            2           1000        0,00   2.000,00
A            2           120       400,00      80,00
A            2           120         0,00     400,00
             3           1000        0,00     800,00
             3           1000      700,00       0,00
B                        120       400,00       0,00
B                        120         0,00   1.400,00
                         1000      900,00     500,00

The query returns the following answer:

vdokvrsta    vdokbroj    konto sum of dug sum of pot
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A            1           120       800,00       0,00
A            2           120       400,00      80,00
A            2           120         0,00     400,00

Running only the subquery part (without the WHERE clause of course):

SELECT d2.vdokvrsta, d2.vdokbroj, d2.konto, SUM(d2.dug), SUM(d2.pot)
FROM dnevnik d2
GROUP BY d2.vdokvrsta, d2.vdokbroj, d2.konto
HAVING (SUM(d2.dug) - SUM(d2.pot)) != 0

Returns the following:

vdokvrsta    vdokbroj    konto sum of dug sum of pot
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                         1000      900,00     500,00

             3           1000      700,00     800,00

A            1           120       800,00       0,00

A            2           120       400,00     480,00

B                        120       400,00   1.400,00

Which is exactly what I intended it to do. But when the WHERE clause is inserted and the whole thing is added to the WHERE EXISTS, the records that contain empty fields are left out. Why? The expected output of the whole query should be:

vdokvrsta    vdokbroj    konto sum of dug sum of pot
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A            1           120       800,00       0,00
A            2           120       400,00      80,00
A            2           120         0,00     400,00
             3           1000        0,00     800,00 *
             3           1000      700,00       0,00 *
B                        120       400,00       0,00 *
B                        120         0,00   1.400,00 *
                         1000      900,00     500,00 *

Note that the fields marked with an asterisk (*) are missing in the real output.

Am I really not seeing something here. Please help.

Vedran Matesic



Sat, 05 May 2001 03:00:00 GMT  
 Please help with BDE SQL !!! Maybe BUG in BDE?
Thank you for your extensive reply. My fault was that I tried to simplify
and internationalize the matter so I stripped the SQL of essential details.
I submit the actual SQL in its original form with additional information.

The database:  Delphi 4.02, BDE 5.0, Paradox tables.

The SQL:

SELECT *
FROM dnevnik d1
WHERE EXISTS (
    SELECT d2.vdokvrsta, d2.vdokbroj, d2.konto, SUM(d2.dug), SUM(d2.pot)
    FROM dnevnik d2
    WHERE (d2.vdokvrsta = d1.vdokvrsta)
        AND (d2.vdokbroj = d1.vdokbroj)
        AND (d2.konto = d1.konto)
    GROUP BY d2.vdokvrsta, d2.vdokbroj, d2.konto
    HAVING (SUM(d2.dug) - SUM(d2.pot)) != 0)

The table dnevnik:

vdokvrsta    vdokbroj    konto        dug        pot
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A            1           1000    1.000,00       0,00
A            1           1000        0,00   1.000,00
A            1           120       800,00       0,00
A            2           1000    2.000,00       0,00
A            2           1000        0,00   2.000,00
A            2           120       400,00      80,00
A            2           120         0,00     400,00
             3           1000        0,00     800,00
             3           1000      700,00       0,00
B                        120       400,00       0,00
B                        120         0,00   1.400,00
                         1000      900,00     500,00

The query returns the following answer:

vdokvrsta    vdokbroj    konto sum of dug sum of pot
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A            1           120       800,00       0,00
A            2           120       400,00      80,00
A            2           120         0,00     400,00

Running only the subquery part (without the WHERE clause of course):

SELECT d2.vdokvrsta, d2.vdokbroj, d2.konto, SUM(d2.dug), SUM(d2.pot)
FROM dnevnik d2
GROUP BY d2.vdokvrsta, d2.vdokbroj, d2.konto
HAVING (SUM(d2.dug) - SUM(d2.pot)) != 0

Returns the following:

vdokvrsta    vdokbroj    konto sum of dug sum of pot
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                         1000      900,00     500,00

             3           1000      700,00     800,00

A            1           120       800,00       0,00

A            2           120       400,00     480,00

B                        120       400,00   1.400,00

Which is exactly what I intended it to do. But when the WHERE clause is
inserted and the whole thing is added to the WHERE EXISTS, the records that
contain empty fields are left out. Why? The expected output of the whole
query should be:

vdokvrsta    vdokbroj    konto sum of dug sum of pot
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A            1           120       800,00       0,00
A            2           120       400,00      80,00
A            2           120         0,00     400,00
             3           1000        0,00     800,00 *
             3           1000      700,00       0,00 *
B                        120       400,00       0,00 *
B                        120         0,00   1.400,00 *
                         1000      900,00     500,00 *

Note that the fields marked with an asterisk (*) are missing in the real
output.

Am I really not seeing something here. Please help.

Vedran Matesic



Sat, 05 May 2001 03:00:00 GMT  
 Please help with BDE SQL !!! Maybe BUG in BDE?

Quote:

> Thank you for your extensive reply. My fault was that I tried to simplify
> and internationalize the matter so I stripped the SQL of essential details.
> I submit the actual SQL in its original form with additional information.

> The database:  Delphi 4.02, BDE 5.0, Paradox tables.

<CUT HERE>

I am not a SQL expert by far, but might a RIGHT JOIN solve this?

SELECT *
FROM dnevnik d1
WHERE EXISTS (
    SELECT d2.vdokvrsta, d2.vdokbroj, d2.konto, SUM(d2.dug), SUM(d2.pot)
    FROM dnevnik d2
    WHERE (d2.vdokvrsta =* d1.vdokvrsta)
        AND (d2.vdokbroj =* d1.vdokbroj)
        AND (d2.konto =* d1.konto)
    GROUP BY d2.vdokvrsta, d2.vdokbroj, d2.konto
    HAVING (SUM(d2.dug) - SUM(d2.pot)) != 0)

Note the asterisks (*) in the WHERE clause.

I believe that will give you every record in the d1 table, regardless if
it's match in the d2 table has just NULL values..

Hope that helps!

Rkr

--
                   \|||/
                   /'^'\
                  ( 0 0 )
--------------oOOO--(_)--OOOo--------------
. Reid Roman                              .
. Delphi Programmer / Analyst             .
. TVisualBasic:=class(None)               .
. May the Source be With You              .
-------------------------------------------
. Auto-By-Tel (http://www.autobytel.com)  .
. Irvine, CA U.S.A                        .
. E-Mail : rkroman (at) pacbell (dot) net .
. or reidr (at) autobytel (dot) com       .
-------------------------------------------



Sun, 06 May 2001 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Outer Joins in BDE - maybe a bug

2. Strange problem using local SQL / BDE 3.5 (bug?)

3. Possible bug in BDE ? (SQL troubles)

4. BDE 5.0 SQL on Paradox tables so slow in comparisont to BDE 2.52

5. bugs in bde 3.0, HELP!

6. OUTER JOINS difference - maybe a bug?

7. Maybe a bug in Delphi (->properties)

8. Using LOCAL-SQL or Transact-SQL with SQL-server and BDE

9. Help please: non-BDE, non-ODBC database files

10. oracle/bde HELP Please!!!!!

11. Please help urgent: Installing BDE manually

12. Please help configure BDE in Delphi 2

 

 
Powered by phpBB® Forum Software