I Don't Understand <>

[wrgt9740]

Hi. I have a simple code:

create cursor csr1 (name C(5))
insert into csr1 (name) values ("A")
insert into csr1 (name) values ("B")
insert into csr1 (name) values ("C")
insert into csr1 (name) values ("D")
insert into csr1 (name) values ("E")

create cursor csr2 (name C(5), exist N(1))
insert into csr2 (name, exist) values ("A", 0)
insert into csr2 (name, exist) values ("B", 0)
insert into csr2 (name, exist) values ("F", 0)
insert into csr2 (name, exist) values ("G", 0)

update csr2 set exist = 1 where csr2.name <> csr1.name

The results are all records have values of csr2.exist as 1, when I thought only records of csr2.name with values A and B should have value of 1 in csr2.exist. Perhaps there is something wrong with my code, or my understanding of <>? Thank you for taking the time.

 

[Scott24x7]

Either operator <> or # can be used for "Not Equal".
It looks like your problem is with the "SET" where you're stating "Exist" is column 1. But looks to me like that is column 2.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[atlopes]

wrgt9740,

Although, in your statement, the equal operator would make more sense than the inequality operator, the problem is that you're not crossing the two cursors. Your UPDATE takes the current of csr1.name (which is 'E', because the cursor is at its last record) and compares the name value in each row of csr2. Since there is no 'E' name in the csr2 cursor, all 'exist' will be set to 1.

Try to GO TOP IN csr1 before issuing the UPDATE and you'll notice the difference.

You need to cross the two cursors. You can try these two solutions:

update csr2 set exist = 1 where [highlight #FCE94F]csr2.name IN (SELECT name FROM csr1)[/highlight]

or, with VFP9,

update csr2 set exist = 1 [highlight #FCE94F]FROM csr1 where csr1.name == csr2.name[/highlight]

Both will set the exist column in A and B rows to 1, and leave the others unchanged.

 

[Chris Miller]

Well, to set you exist field ou could also make use of the SQL exists clause:

UPDATE crs2 Set exist=1 Where EXISTS Select * from crs1 WHERE name=crs2.name

I have no idea why you use <> (not equal) to find existing names anyway.

Chriss

 

[Chris Miller]

Plus, if you eventually want the list of names existing in both crs1 and crs2 by finally doing SELECT * FROM crs2 WHERE exist=1, then there's the much simpler and straight forward query:

Select name from crs2 where exists Select * from crs1 where crs1.name=crs2.name

What you're needing to just set the flag to 1 is reccount('crs2') exists queries, that's much worse than just one exists query, isn't it?

Chriss

 

[wrgt9740]

Atlopes, I tried with GO TOP and without GO TOP, and the results were the same, so I guess going to top of table is irrelevant. Your suggestion may work with my personal project. I was so obsessed with solving this problem using <>, that I did not think that the solution might be using = or ==.

Chris, it's my first time to hear about the EXISTS clause in SQL. Perhaps this could be the solution I'm looking for. I was insisting myself to use <> because my project is to convert our old clipper program into Foxpro, and I'm trying to improve or update the clipper code, which is something like this:

do while not eof()
select salesman
seek tempcode
if found()
replace salesman.collect with salesman.collect + salesman.pay
else
append blank
replace salesman.code with tempcode
replace salesman.name with "unlisted"
replace salesman.collect with salesman.collect + salesman.pay
endif
skip
enddo

I initially thought SQL code with <> could improve this clipper code, but it's not giving me the correct results. Then after reading everybody's replies, I realize I should try something else.

 

[Chris Miller]

This code does something completely different. And I still don't have any idea how you think <> has to do with this or is helping to get what you want, as SQL joins are made with matching conditions, needing an equal operator, not an unequal operator (<> literally means lower or greater, i.e. unequal).

The clipper code does add one payment (loan?) for a salesmen corresponding to some code (identifier?). I can't tell for surewhat it means, but I think collect is the collected payments for a salesmen, pay is something like the monthly loan so when this happens once a month collect is the aggregated loans a salesman has got in some period, perhaps for this year or since he was hired. Pay could also be some provision paid for certain deals. No idea.

Besides that, this would work 1:1 as is in VFP, you could combine the append blank and 3 replaces with one INSERT. And the first part could be done with an UPDATE, too, but just a simple UPDATE with WHEERE code=tempcode, nothing with EXISTS. In a sense that only updates a record that is first tested to exist, but this isn't going in the direction of EXISTS queries, this is simply finding the identifier. If this update doesn't find the code in the salesmen, nothing gets updated, then _tally is 0 and you can decide to do the INSERT if that's the case.

Overall that's adding a pay to an existing salesmen or add a new salesman, if not found. Whether collect for a new salesmen then is simply pay or higher depends on the default value for collect. That's not written in the code.

Overall: You have to look into more than just old code to translate it, you first have to learn the database tables, fields and their meaning to be able to understand that.

We can't help by just seeing some code, even less so just your first try to convert it, we have far less hands on all the surrounding things and data that would tell more about the meaning of all of it. The way you think is beyond me, sorry. Good luck to get through this and Happy New Year anyway.

Chriss

 

[wrgt9740]

Chris, I'm sorry if the code I posted was confusing. I attempted to simplify the code, as the original is quite long. Basically, the user would enter into the database the items sold and their corresponding amount, and that amount would also go to the salesman table and we would compute how much commission he or she would receive at the end of the month.

I tried the second part of the clipper code with something like:

insert into salesman (code, name, collect) ;
values (tempcode, "unlisted", salesman.collect + salesman.pay) ;
where tempcode <> salesman.code

This code will produce syntax error(s), but please disregard it as I only wish to show how I attempted and failed by using <>.

 

[Chris Miller]

Yes, I understand how that fails. Simply WHERE is no clause of an INSERT. The where reflects a condition that should decide whether to do the insert at all or not to do it.

And that could be as I said, when a previous update, that assumes a salesman with a certain code find it and updates that record. If it doesn't find it nothing bad happens, the table is unchanged and _TALLY is 0. And then you do the insert.

The other thing that can't work in an insert is setting a field to itself plus something else, as the insert just creates the new record, there isn't one et, not even with default values.

Still no idea why you thought <> would work here at all.

Chriss

 

[Chris Miller]

Still no idea why you thought <> would work here at all.

Also, since you made it the title of your thread.

I can easily guess and see you think such a condition will only insert a record when it doesn't find a salesman with code=tempcode, i.e. if all codes stored in salesmen are differing from tempcode.
But no logic of WHERE clauses work that way, they always work in the context of each single record. There is no result set of rows if you'd look for code=tempcode and the tempcode isn't in the table and so when you'd do a SELECT * .. WHERE code<>tempcode that's all records. What's dangerous in such thinking, if you'd do an UPDATE WHERE tempcode <> code, then you'd add the pay to EVERY salesman, EXCEPT the one that should get it.

Your logic is completely inverse to what it should be.

It's not rare, but it gets me every time. I see people doing the exact opposite of what they mean to do without seeing it. It's understandable if you go off a straight path, but still in the main direction you want to go, but how can it happen so often that one can think in a way that shows less orientation as even a blind man would have and how can people come up with things that are exactly aiming into the opposite direction. It's a kind of magic.

Chriss

 

[wrgt9740]

Chris, thanks for your replies. I agree with what you posted. You understand how beginners in Foxpro think, and your explanation clarifies where I went wrong and why. I appreciate you taking the time. I will change my approach with this problem by taking into consideration the suggestions posted above.

By the way, I had an idea while I was sleepily changing my child's soiled diaper at 3:45 in the morning: if the tempcode is not found in salesman table, then foxpro would prompt the user and let the user add a new record on the salesman table and give it a proper name, not just an "unlisted" salesman. But I'll have to investigate why the original programmer in clipper did not implement this.

Many thanks.

 

[Chris Miller]

Good idea,

I think at some point in the original code you'd find a place where "unlisted" sales men can be given a name, or they always just remain records to balance or store some promotions not given to any salesman but staying with the company.

VFP offers a project search within the tools menu, called "Code References". If you search for "unlisted" there, you should find that code. Not sure if you even have a pjx yet, perhaps just a folder with PRs, but Code references also covers that case in its options where to search.

And by the way, the two simplest approaches of updating an existing record ot inserting a new one, if no record with an id (tempcode) is found are:

1. SQL

UPDATE table Set somefield = somevalue ...(perhaps more changes)... WHERE somefield=someid (or tempcode in this case)
IF _tally=0
   INSERT INTO table (fieldlist) VALUES (valuelist)
Endif

2. xBase

Select table && in this case SELECT salesman
If NOT SEEK(tempcode,'salesman','code') && or whatever tag name for the code field exists
   APPEND BLANK
   REPLACE ...
  * in this special case:
  * REPLACE code with tempcode, name with "unlisted"
ENDIF

REPLACE somefield with somevalue...
* in this special case
REPLACE collect with collect + pay

In both cases there's no use of <>, you "think positive", you seek an existing record with either where field equals value (not unequal <>) or you SEEK a value in an index, which also means it equals that value. If it's not found, then you add a record. In both cases you're now on a record that has the code and a salesman name or "unlisted" and can add whatever pay is given in that record.

What I wonder is how pay is given, if it's differing per salesman, what is the default for it? If it should be a promotion payment that's depending on the deal/product and not the salesmen, it seems wrong to take it from the salesman record, it should be a variable/parameter. But if this commission is salesman dependent, then this is okay. In any case that's not a technical problem, that's just a business rule decision. What to look out for is how pay is defined for a new BLANK record. The original clipper code also doesn't set it, so there either is a default value for pay in new records or it's 0, and unlisted salesmen get no pay. In which case you could also skip the step of adding pay to collect for "unlisted" salesmen.

Last, not least: You can see the original code is repetitive, not most elegant in itself:

...
if found()
[highlight #FCE94F]replace salesman.collect with salesman.collect + salesman.pay[/highlight]
else
append blank
replace salesman.code with tempcode
replace salesman.name with "unlisted"
[highlight #FCE94F]replace salesman.collect with salesman.collect + salesman.pay[/highlight]
endif

instead what could be done is:

...
if not found()
append blank
replace code with tempcode, name with "unlisted"
endif
replace collect with collect + pay

The major change is not having the collect+pay replacement in both cases but as final step of either finding the record or creating it.
The not found case only needs to add a record with the right code and a name.

It's not fair to judge the clipper code by one case, but the original developer likely didn't solve things in the best way already. And once more, that code also works in VFP, there's actually no need to change it that much, if you want to get through with conversion it pays in saving time to leave code as is if it's also working in VFP that way.

Chriss

 

[wrgt9740]

Hi Chriss, I haven't played around with my project yet as I've been swamped with work since returning to the office after the holidays.
I have no idea what "code reference" is in foxpro. I also didn't know it exists in foxpro. I'll read more about this when I have free time. It never fails that I learn something new when I ask in this helpful forum.
The _tally approach seems to be an efficient solution. Is using the clipper code on vfp less efficient?

 

[Chris Miller]

Hi wrgt9740!

there recently was a discussion, perhaps slightly moderated and missing some portions, about whether REPLACE or INSERT is better. In short: INSERT is doing all what APPEND and REPLACE does in one step. Since old software usually does not involve new features like stored procs, table and field rules, default values and some more, you don't gain much in a legacy dbf, but in a dbf of a dbc, when the inserts already sets a field different from the APPPEND BLANK default, that saves evaluating the default value, if it's an expression or call, not so much of an issue with some constant value.

Then APPEND plus a series of REPLACE can mean checking table and field rules multiple times, modifying the index is perhaps the worst impact also for legacy dbfs, instead of once adding a new record in all related index tags, you first add a standard value (empty string, 0) and then change that to the real value, which isn't only an update of some cdx bytes, but may need restructuring of several pages of the cdx multiple times.

You can simply measure it. But if it's one record you add manually and the next only is due when you finish a form in minutes, then the split seconds difference won't matter. It can play a major role for data imports that are working in batches of data and there an absolute short difference can still become a factor, a magnitude of longer runtime in the unoptimal case.

Chriss

 

[Chris Miller]

To talk of another point separately.

The two lines I highlighted that repeat in the clipper code are not bad style because of performance. Programming isn't only nor mainly about performance, it's bad style because in a case of changing the requirements you could overlook the need to change both lines, especially if they are further apart in the code. It's coding style, really just bad coding style to have the same code twice and not think of a way to centralize it in one place.

Chriss

 

[wrgt9740]

Hi Chris. It's been a while. After a lot of reading and researching on the internet and playing around with Foxpro, I'm going to let the users manually add the missing salesman codes, if there are any. I could not apply _TALLY and WHERE EXISTS as suggested, but I used the opposite of SELECT... WHERE <condition> IN (SELECT...), which is as follows:

SELECT SMCODE FROM CSRTEST1 WHERE SMCODE NOT IN ;
(SELECT SMCODE FROM SALESMAN) ;
INTO CURSOR CSRTEST2

SELECT CSRTEST2
IF RECCOUNT() > 0
&& ask users to add salesman code or not
ENDIF

CSRTEST1 is where I manually entered salesman codes that exist in the salesman database, and intentionally one or two codes that are not in the database. CSRTEST2 is to check if there are codes from CSRTEST1 that are not in the database.

Many thanks for all the suggestions and information you guys posted.