Hi Carlos,
The following code produces identical result in VFP9 and VFP8:
CLEAR
CREATE CURSOR taba (id C(4), field1 C(4), field2 C(5))
INSERT INTO taba values("0101","desc","1")
INSERT INTO taba values("0201","desc","2 X")
CREATE CURSOR tabb (id C(6), field1 C(1))
INSERT INTO tabb values("010101","1")
INSERT INTO tabb values("010102","2")
INSERT INTO tabb values("010103","3")
INSERT INTO tabb values("020101","1")
INSERT INTO tabb values("020102","2")
INSERT INTO tabb values("020103","3")
select * from tabb left join taba on left(tabb.id,4)=taba.id where not empty(taba.field2) INTO CURSOR res
list
The result is (it looks correct to me):
Record# ID_A FIELD1_A ID_B FIELD1_B FIELD2
1 010101 1 0101 desc 1
2 010102 2 0101 desc 1
3 010103 3 0101 desc 1
4 020101 1 0201 desc 2 X
5 020102 2 0201 desc 2 X
6 020103 3 0201 desc 2 X
Could you modify the code to get results you are describing?
Thanks,
Aleksey.
>I have executed the following SQL command both in VFP8 and VFP9 and the results were different.
>
>select * from tabb left join taba on left(tabb.id,4)=taba.id where not empty(taba.field2)
>
>where taba is:
>
>id field1 field2
>0101 desc 1
>0201 desc 2 X
>
>and tabb is:
>
>id field1
>010101 1
>010102 2
>010103 3
>020101 1
>020102 2
>020103 3
>
>the result (which i think is the correct) in VFP8 is:
>
>id_a field1_a id_b field1_b field2
>020101 1 0201 desc 2 X
>020102 2 0201 desc 2 X
>020103 3 0201 desc 2 X
>
>and the result in VFP9 is:
>
>id_a field1_a id_b field1_b field2
>010101 1 .null. .null. .null.
>010102 2 .null. .null. .null.
>010103 3 .null. .null. .null.
>020101 1 0201 desc 2 X
>020102 2 0201 desc 2 X
>020103 3 0201 desc 2 X
>
>I haven´t read anything in the documentation about different behavior in the SQL command that could cause this.
>
>If someone have a clue, please tell me.
>
>Thnk you.