×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

icase issues - unexpected results

icase issues - unexpected results

icase issues - unexpected results

(OP)
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.


CODE --> FoxPro/SQL

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!


CODE --> FoxPro/SQL

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 

RE: icase issues - unexpected results

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.

RE: icase issues - unexpected results

(OP)
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

RE: icase issues - unexpected results

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:

CODE

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 t1.datefield2 < CTOD("04/23/2018") AND t1.datefield2 > CTOD("01/01/1900") you had in any case is already in the where clause, so only records fulfilling these conditions are processed at all.

Bye, Olaf.

RE: icase issues - unexpected results

One other small simplification:

Instead of:

t2.datefield3 <> CTOD("")

why not:

NOT EMPTY(t2.datefield3)

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

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: icase issues - unexpected results

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.

RE: icase issues - unexpected results

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

RE: icase issues - unexpected results

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close