What does this command do?

[foxsu]

HI. I have some lines in a Foxpro 5.0 Form

****************
SELECT cars.brand, owners.name, country.name,;
FROM Cars,owners,country;
WHERE Cars.model = owners.like;
AND ALLTRIM(Cars.color)= SUBSTR(owners.earn,1,LEN(ALLTRIM(Cars.tire)));
ORDER BY Cars,brans, owners.name;
INTO CURSOR test
************
What exactly does this instruction do? --> AND ALLTRIM(Cars.color)= SUBSTR(owners.earn,1,LEN(ALLTRIM(Cars.tire)));

Sorry for the question, I´m just beginning the programmming
Area.


THanks

 

[1oldfoxman]

Part of the SQL statemenet's WHERE clause, the expression
AND ALLTRIM(Cars.color)= SUBSTR(owners.earn,1,LEN(ALLTRIM(Cars.tire)));
adds to the filter on the select results.

Specifically, ALLTRIM(Cars.color) is a function which returns the contents of Cars.color field with leading and trailing blanks removed.

The SUBSTR() function in your code will return a substring of the contents of the Owners.earn field beginning at the first character, and including all characters up to length of the ALLTRIM(Cars.tire) expression.

 

[ramani]

If you analyse the code...

ALLTRIM(Cars.color)= SUBSTR(owners.earn,1,LEN(ALLTRIM(Cars.tire)));


LEN(ALLTRIM(Cars.tire)) = LENGTH of Cars.Tire field

Owners.Earn field value from the 1st character position to the length of Cars.tire field is matched to the cars.tire field.. It appears, pwners.earn field always contain the cars.tire color and so that is matched in the selection of record.




ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[jimoo]

They are selecting the brand, owner, and county from 3 different tables - using an implied inner join.

It looks like it is based on the brand the owner's like, but then it gets really goofey because it's based on the color = the owners.earn, but only for the lenght of the tire field trimmed.

They must have setup a really goofey key to match on. It doesn't make sense to me.

A side note - these types of select statements (using an implied inner join) are usually not the best way to program a join.

Here is a better example:

SELECT Customer.cust_id, Customer.company, Customer.country,;
Orders.order_id, Orders.order_amt;
FROM orders INNER JOIN customer ;
ON Orders.cust_id = Customer.cust_id


Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com