SELECT SQL return wrong result 
Author Message
 SELECT SQL return wrong result

Hi, All!
Please help me to understand!
I calculate db_sum and cr_sum with same parameters in calculate's sum() and
in Select SQL's sum(), but i receive different results (SQL calculate
wrong). Why?

d0={01.07.1998}
d1={31.07.1998}

calculate sum(iif(journal.date<d0, iif(allt(cur_acc)= ;
    allt(journal.ac_db), journal.sum,000000000.00),000000000.00)) ;
    for client_cod=219 and date<=d1 to db_sum

calculate sum(iif(journal.date<d0, iif(allt(cur_acc)= ;
    allt(journal.ac_cr), journal.sum,000000000.00),000000000.00)) ;
    for client_cod=219 and date<=d1 to cr_sum

go top
select client.code, client.name, ;

sum(iif(journal.date<d0,iif(allt(cur_acc)=allt(journal.ac_db),journal.sum,00
0000000.00), 000000000.00)) ;
        as db_sum, ;

sum(iif(journal.date<d0,iif(allt(cur_acc)=allt(journal.ac_cr),journal.sum,00
0000000.00), 000000000.00)) ;
        as cr_sum, ;
    from client, journal ;
    where client.code=journal.client_cod ;
        and (allt(journal.ac_db)=allt(cur_acc) or
allt(journal.ac_cr)=allt(cur_acc)) and journal.date<=d1 ;
    group by client.code ;
    order by client.name ;
    into dbf temp

browse for code=219



Mon, 29 Jan 2001 03:00:00 GMT  
 SELECT SQL return wrong result
Hi Alexandre
Your SQL Select repeats the conditonals of the nested IIF clauses in the
WHERE clause. It's either superflious or seems to lead to different results.
From which table does cur_acc come. It's doesn't have a table alias. When
you run the CALCULATE, is there a relation set from Journal to Client? Which
version of FoxPro are you using?
-Anders

Quote:

>Hi, All!
>Please help me to understand!
>I calculate db_sum and cr_sum with same parameters in calculate's sum() and
>in Select SQL's sum(), but i receive different results (SQL calculate
>wrong). Why?

>d0={01.07.1998}
>d1={31.07.1998}

>calculate sum(iif(journal.date<d0, iif(allt(cur_acc)= ;
>    allt(journal.ac_db), journal.sum,000000000.00),000000000.00)) ;
>    for client_cod=219 and date<=d1 to db_sum

>calculate sum(iif(journal.date<d0, iif(allt(cur_acc)= ;
>    allt(journal.ac_cr), journal.sum,000000000.00),000000000.00)) ;
>    for client_cod=219 and date<=d1 to cr_sum

>go top
>select client.code, client.name, ;

>sum(iif(journal.date<d0,iif(allt(cur_acc)=allt(journal.ac_db),journal.sum,0
0
>0000000.00), 000000000.00)) ;
>        as db_sum, ;

>sum(iif(journal.date<d0,iif(allt(cur_acc)=allt(journal.ac_cr),journal.sum,0
0
>0000000.00), 000000000.00)) ;
>        as cr_sum, ;
>    from client, journal ;
>    where client.code=journal.client_cod ;
>        and (allt(journal.ac_db)=allt(cur_acc) or
>allt(journal.ac_cr)=allt(cur_acc)) and journal.date<=d1 ;
>    group by client.code ;
>    order by client.name ;
>    into dbf temp

>browse for code=219



Mon, 29 Jan 2001 03:00:00 GMT  
 SELECT SQL return wrong result
Alexandr,
I would guess one, or both, of these could cause problems:
First, your SELECT is joining two tables (client, journal).  If there is
not a one-to-one relationship between these tables, based on
client.code=journal.client_cod, your calculations will be wrong because it
will sum some records more than once.

Second, your WHERE clause includes an "OR":
  where client.code=journal.client_cod ;
  and (allt(journal.ac_db)=allt(cur_acc) or
  allt(journal.ac_cr)=allt(cur_acc)) and journal.date<=d1
This will select records that meet one of the two conditions, where your
CALCULATE uses records that meet only one condition.  Would adding the
"(allt(journal.ac_db)=allt(cur_acc) or allt(journal.ac_cr)=allt(cur_acc))"
to your CALCULATE also make it incorrect?

--

James H. Blythe
TCOSS Team Leader



Quote:
> Hi, All!
> Please help me to understand!
> I calculate db_sum and cr_sum with same parameters in calculate's sum()
and
> in Select SQL's sum(), but i receive different results (SQL calculate
> wrong). Why?

> d0={01.07.1998}
> d1={31.07.1998}

> calculate sum(iif(journal.date<d0, iif(allt(cur_acc)= ;
>     allt(journal.ac_db), journal.sum,000000000.00),000000000.00)) ;
>     for client_cod=219 and date<=d1 to db_sum

> calculate sum(iif(journal.date<d0, iif(allt(cur_acc)= ;
>     allt(journal.ac_cr), journal.sum,000000000.00),000000000.00)) ;
>     for client_cod=219 and date<=d1 to cr_sum

> go top
> select client.code, client.name, ;

sum(iif(journal.date<d0,iif(allt(cur_acc)=allt(journal.ac_db),journal.sum,00

Quote:
> 0000000.00), 000000000.00)) ;
>         as db_sum, ;

sum(iif(journal.date<d0,iif(allt(cur_acc)=allt(journal.ac_cr),journal.sum,00

- Show quoted text -

Quote:
> 0000000.00), 000000000.00)) ;
>         as cr_sum, ;
>     from client, journal ;
>     where client.code=journal.client_cod ;
>         and (allt(journal.ac_db)=allt(cur_acc) or
> allt(journal.ac_cr)=allt(cur_acc)) and journal.date<=d1 ;
>     group by client.code ;
>     order by client.name ;
>     into dbf temp

> browse for code=219



Mon, 29 Jan 2001 03:00:00 GMT  
 SELECT SQL return wrong result
Hello Anders!
Thank you for answer!

Quote:

>Your SQL Select repeats the conditonals of the nested IIF clauses in the
>WHERE clause.

I do not use IIF in WHERE clause.

Quote:
>It's either superflious or seems to lead to different results.

That means "superflious" word? (I am Russian)

Quote:
>From which table does cur_acc come. It's doesn't have a table alias.

cur_acc is memory variable (character type).

Quote:
>When you run the CALCULATE, is there a relation set from Journal to Client?

No, this is not required, I think.

Quote:
>Which version of FoxPro are you using?

VFP 5.0 Build 402


Tue, 30 Jan 2001 03:00:00 GMT  
 SELECT SQL return wrong result
Hello Jim!
Thank you for answer to me!

Quote:
You wrote...
>First, your SELECT is joining two tables (client, journal).  If there is
>not a one-to-one relationship between these tables, based on
>client.code=journal.client_cod, your calculations will be wrong because it
>will sum some records more than once.

Yes, there is one-to-many relationship, and what i need to do?
May be, this does not influence upon calculations in my case?
I sum record from 'journal', they will sum more than once?


Tue, 30 Jan 2001 03:00:00 GMT  
 SELECT SQL return wrong result
Alexandr, you are right -- the one-to-many relationship will not cause harm
-- only a many-to-many would.  I just noted this as a difference from the
single table used in your CALCULATE commands.
I would focus on the "OR" I mentioned.  Separate the SELECTs like the
CALCULATEs are -- one sum at a time.  This will prove, or not, that the
"OR" is changing the SUMs.

Anders is right, there was superfluous (repeated) checking in the "iif" and
the WHERE clause.
Try these:

select client.code, client.name, ;
 sum(iif(journal.date<d0, journal.sum,000000000.00)) ;
  as db_sum, ;
 from client, journal ;
 where client.code=journal.client_cod ;
       and allt(journal.ac_db)=allt(cur_acc) and journal.date<=d1 ;
    group by client.code ;
    order by client.name ;
    into dbf tempdb

browse for code=219

select client.code, client.name, ;
 sum(iif(journal.date<d0, journal.sum,000000000.00)) ;
  as cr_sum, ;
 from client, journal ;
 where client.code=journal.client_cod ;
       and allt(journal.ac_cr)=allt(cur_acc) and journal.date<=d1 ;
    group by client.code ;
    order by client.name ;
    into dbf tempcr

browse for code=219

--

James H. Blythe
TCOSS Team Leader



Quote:
> Hello Jim!
> Thank you for answer to me!

> You wrote...
> >First, your SELECT is joining two tables (client, journal).  If there is
> >not a one-to-one relationship between these tables, based on
> >client.code=journal.client_cod, your calculations will be wrong because
it
> >will sum some records more than once.

> Yes, there is one-to-many relationship, and what i need to do?
> May be, this does not influence upon calculations in my case?
> I sum record from 'journal', they will sum more than once?



Tue, 30 Jan 2001 03:00:00 GMT  
 SELECT SQL return wrong result
Hi again Alexandr

I think it's possible to simplify the nested IIF clauses this way:
CALCULATE SUM(IIF(Journal.date<d0 ;
AND cur_acc= Journal.ac_db, Journal.sum,0.0)) ;
    FOR client_code = 219 AND date<=d1 TO db_sum
As only dates < {01.07.1998} are included the condition <=  {31.07.1998}
is superfluous.  This would lead to the simplification
SELECT Journal
 CALCULATE SUM(sum) ;
FOR client_code = 219 AND date < d0 AND  ac_db = M.cur_ac

The SQL Select would then be:

SELECT Client.code, Client.name, ;
 SUM(IIF(Journal.ac_db=M.cur_acc,Journal.sum, 0.0)) AS sum_db, ;
 SUM(IIF(Journal.ac_cr=M.cur_acc,Journal.sum, 0.0)) AS sum_cd ;
 FROM Client JOIN Journal ON Client.code=Journal.code ;
 WHERE Journal.date < M.d0 ;
 GROUP BY Client.code

using reserved keywords as sum, name and code as column names can be
confusing and in some cases an SQL query will refuse to compile when such
words appear in the wrong place, as the compiler sees things.

It always helps a lot in these tricky queries if there's a CREATE TABLE
commands,
some INSERT INTO Table VALES (sample data) and an example of the query
result set you expect.
I used the following tables and data to try and figure this out and it may
be all wrong <g>:

CREATE CURSOR Journal (code I, date D, ac_db I, ac_cr I, SUM Y)
SET DATE DMY
d0={01.07.1998}
d1={31.07.1998}
cur_acc = 2
CREATE CURSOR Client (code I, name c(10))
INSERT INTO Client VALUES (218, 'client 1')
INSERT INTO Client VALUES (219, 'client 2')
INSERT INTO Client VALUES (220, 'client 3')
INSERT INTO Journal VALUES (218,{01.07.1998},1,0, 20)
INSERT INTO Journal VALUES (218,{01.06.1998},1,0, 15)
INSERT INTO Journal VALUES (218,{31.07.1998},1,0, 16)
INSERT INTO Journal VALUES (218,{30.07.1998},1,0, 17)
INSERT INTO Journal VALUES (219,{01.07.1998},1,0, 20)
INSERT INTO Journal VALUES (219,{01.06.1998},1,0, 15)
INSERT INTO Journal VALUES (219,{31.07.1998},1,0, 16)
INSERT INTO Journal VALUES (219,{30.07.1998},1,0, 17)
CALCULATE SUM(IIF(Journal.date < d0, IIF(cur_acc= ;
 Journal.ac_db, Journal.sum,000000000.00),000000000.00)) ;
 FOR code = 219 AND date <= M.d1 TO db_sum
? db_sum
CALCULATE SUM(IIF(Journal.date <d0 AND M.cur_acc= ;
 Journal.ac_db, Journal.sum,0.0)) ;
 FOR code = 219 AND date <= M.d1 TO db_sum
? db_sum

SELECT Client.code, Client.name, ;
 SUM(IIF(Journal.ac_db=cur_acc,Journal.sum, 0.0)) AS sum_db, ;
 SUM(IIF(Journal.ac_cr=cur_acc,Journal.sum, 0.0)) AS sum_cd ;
 FROM Client JOIN Journal ON Client.code=Journal.code ;
 WHERE Journal.date < d0 ;
 GROUP BY Client.code

-Anders

Quote:

>Hi, All!
>Please help me to understand!
>I calculate db_sum and cr_sum with same parameters in calculate's sum() and
>in Select SQL's sum(), but i receive different results (SQL calculate
>wrong). Why?

>d0={01.07.1998}
>d1={31.07.1998}

>calculate sum(iif(journal.date<d0, iif(allt(cur_acc)= ;
>    allt(journal.ac_db), journal.sum,000000000.00),000000000.00)) ;
>    for client_cod=219 and date<=d1 to db_sum

>calculate sum(iif(journal.date<d0, iif(allt(cur_acc)= ;
>    allt(journal.ac_cr), journal.sum,000000000.00),000000000.00)) ;
>    for client_cod=219 and date<=d1 to cr_sum

>go top
>select client.code, client.name, ;

>sum(iif(journal.date<d0,iif(allt(cur_acc)=allt(journal.ac_db),journal.sum,0
0
>0000000.00), 000000000.00)) ;
>        as db_sum, ;

>sum(iif(journal.date<d0,iif(allt(cur_acc)=allt(journal.ac_cr),journal.sum,0
0
>0000000.00), 000000000.00)) ;
>        as cr_sum, ;
>    from client, journal ;
>    where client.code=journal.client_cod ;
>        and (allt(journal.ac_db)=allt(cur_acc) or
>allt(journal.ac_cr)=allt(cur_acc)) and journal.date<=d1 ;
>    group by client.code ;
>    order by client.name ;
>    into dbf temp

>browse for code=219



Wed, 31 Jan 2001 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Different results with SELECT SQL and DELETE SQL.

2. How big the return result from SQL Server?

3. avoiding the prompt message when sql server is down and just returning a -1 result

4. Return TO returns to wrong procedure

5. SQL-SELECT, what's wrong?

6. Select - SQL without a result set

7. what settings can effect results of SELECT SQL

8. SELECT - SQL result set dependant on whether table is indexed

9. FP 2.5 erratic Select SQL results when using date fields

10. Select - SQL gives inconsistent results

11. results: Filtering no-match record/field (SELECT-SQL)

12. IIF results in a Select - SQL statement

 

 
Powered by phpBB® Forum Software