icase issues - unexpected results

[Thumper2004]

So I am a software technical support rep for my company, and I do data analysis and data cleanup when needed. I am having some issues with a SQL/FOX program. Using FoxPro 9.

UPDATE table1;
SET datefield1 =;
ICASE(;
        t1.datefield2 < CTOD("04/23/2018") AND t1.datefield2 > CTOD("01/01/1900") AND t2.datefield1 = ctod("") and t2.datefield2 = 
ctod("") and t2.datefield3 = ctod("") and t3.datefield1 = CTOD(""), t3.datefield2,;
        t1.datefield2 < CTOD("04/23/2018") AND t1.datefield2 > CTOD("01/01/1900") AND t2.datefield1 = ctod("") and t2.datefield2 = 
ctod("") and t2.datefield3 = ctod(""), t3.datefield1,;
        t1.datefield2 < CTOD("04/23/2018") AND t1.datefield2 > CTOD("01/01/1900") AND t2.datefield3 = ctod("") AND t2.datefield1 = 
ctod(""), t2.datefield2,;
        t1.datefield2 < CTOD("04/23/2018") AND t1.datefield2 > CTOD("01/01/1900") AND t2.datefield3 = ctod("") AND t2.datefield2 = 
ctod(""), t2.datefield1,;
	t1.datefield2 < CTOD("04/23/2018") AND t1.datefield2 > CTOD("01/01/1900") AND t2.datefield3 <> ctod(""), t2.datefield3,;
    t1.datefield1;
       );
from table1 t1;
join table2 t2 on t1.key=t2.key;
join table3 t3 on t3.key=t2.key;
where t1.datefield2 BETWEEN CTOD("01/01/1900") and CTOD("04/23/2018") and t1.datefield1 = ctod("")

I keep getting the table1 datefield1 populating with the first record's t2.datefild3 for every record in table1. The entire program is below: Any help on what I am doing wrong to have the datefield populate incorrectly would be a great help!

cd?

UPDATE table1;
SET datefield1 = CTOD("03/15/2017");
where datefield1 = CTOD("03/15/0314")
CLOSE ALL

SELECT * FROM table1.dbf
COPY TO table1_cleanup.dbf
CLOSE ALL

use table1
replace field_flag with "A" for datefield2 > ctod("04/23/2018") and 
datefield2 = ctod("") and not deleted()
close all


UPDATE table1;
SET datefield1 =;
ICASE(;
        t1.datefield2 < CTOD("04/23/2018") AND t1.datefield2 > CTOD("01/01/1900") AND t2.datefield1 = ctod("") and t2.datefield2 = 
ctod("") and t2.datefield3 = ctod("") and t3.datefield1 = CTOD(""), t3.datefield2,;
        t1.datefield2 < CTOD("04/23/2018") AND t1.datefield2 > CTOD("01/01/1900") AND t2.datefield1 = ctod("") and t2.datefield2 = 
ctod("") and t2.datefield3 = ctod(""), t3.datefield1,;
        t1.datefield2 < CTOD("04/23/2018") AND t1.datefield2 > CTOD("01/01/1900") AND t2.datefield3 = ctod("") AND t2.datefield1 = 
ctod(""), t2.datefield2,;
        t1.datefield2 < CTOD("04/23/2018") AND t1.datefield2 > CTOD("01/01/1900") AND t2.datefield3 = ctod("") AND t2.datefield2 = 
ctod(""), t2.datefield1,;
	t1.datefield2 < CTOD("04/23/2018") AND t1.datefield2 > CTOD("01/01/1900") AND t2.datefield3 <> ctod(""), t2.datefield3,;
    t1.datefield1;
       );
from table1 t1;
join table2 t2 on t1.key=t2.key;
join table3 t3 on t3.key=t2.key;
where t1.datefield2 BETWEEN CTOD("01/01/1900") and CTOD("04/23/2018") and t1.datefield1 = ctod("")
CLOSE ALL 

use table1 
replace datefield1 with ctod(""), field_flag with " " for field_flag="A" and not deleted() 

CLOSE ALL
QUIT

 

[Olaf Doschke]

If I take this from the first and longest condition your priority is to populate table1.datefield1 from fields in this preferred order:
t2.datefield1
t2.datefield2
t2.datefield3
t3.datefield1
t3.datefield2
and keep it as t1.datefield1 empty - ctod("") - if there's nothing found in the other fields?
Is that the plan?

The way you write the conditions for the cases is not working out.

If you prioritize t2.datefield1, then the first case would only check that field to not be empty - no more, no less.
And even if your prioritization is reverse, each case would just check the one field you'd take as source and know the previously checked fields are empty without rechecking them.

Besides all that, your fields might also be NULL instead of empty, but I don't know your data.

Bye, Olaf.

 

[Thumper2004]

So here is the order by which items should populate:

t2.datefield3
t2.datefield1
t2.datefield2
t3.datefield1
t3.datefield2

If none of the conditions are met leave as is. All of the date columns do not allow Nulls

Also remember guys I have no formal training in programming. Its just what I have picked up over the last 7 years since I have been working here.

Thanks again guys!

BRowe

 

[Olaf Doschke]

Well, anyway, then you check whether t2.datefield3 has a date or not, etc. That's a much simpler, slimmer ICASE than you did. You check one after the other:

UPDATE table1;
SET datefield1 =;
ICASE(;
        t2.datefield3 <> CTOD(""), t2.datefield3,;
        t2.datefield1 <> CTOD(""), t2.datefield1,;
        t2.datefield2 <> CTOD(""), t2.datefield2,;
        t3.datefield1 <> CTOD(""), t3.datefield1,;
	t3.datefield2 <> CTOD(""), t3.datefield2,;
        t1.datefield1;
       );
from table1 t1;
join table2 t2 on t1.key=t2.key;
join table3 t3 on t3.key=t2.key;
where t1.datefield2 BETWEEN CTOD("01/01/1900") and CTOD("04/23/2018") and t1.datefield1 = ctod("")

The condition [tt]t1.datefield2 < CTOD("04/23/2018") AND t1.datefield2 > CTOD("01/01/1900")[/tt] you had in any case is already in the where clause, so only records fulfilling these conditions are processed at all.

Bye, Olaf.

 

[Mike Lewis]

One other small simplification:

Instead of:

[tt]t2.datefield3 <> CTOD("")[/tt]

why not:

[tt]NOT EMPTY(t2.datefield3)[/tt]

As well as being simpler, I think this might be a little faster.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Actually not, Mike, as you likely have no index on EMPTY(field), but on the field. It might speed things up, if you compute CTOD("") or use {^} as empty date literal. It would need very many records, before that pays and this would need to be a regular job to matter.

Bye, Olaf.

 

[Thumper2004]

The t1.datefield1 is still being populated with the first records t2.datefield3 for all records in the table.

 

[Olaf Doschke]

You join table2 as t2, so you're not using just a single record of t2, unless you forget a semicolon.
I have the feeling you're not showing real names, translate names, and something goes wrong at that stage. I can't help you with that.

Besides, this query only updates WHERE ... t1.datefield1 = ctod(""), since your t1.datefield1 are alreaday wrongly updated, no correct update changes that. First empty that field.

Bye, Olaf.