>>If you have character expressions in your view, use PADR() to make sure that size of the filed doesn't change depending on data retrieved.
>
>I used PADR(expr, LEN(field)) in the NVL expressions...
>
>No, wait! I know! It's because there's a memo field in it.

(* sigh *) No, it's not. The memo field is a memo field, and requerying doesn't change it.

It has to do with nested left joins behaving in a way I can't understand.

With obvious name changes, I have

CREATE SQL VIEW v_Name AS ;
	SELECT Tbl1.*, ;
		NVL(Tbl2.Fld1, PADR("*ERROR*", LEN(Tbl2.Fld1))) AS Fld1, ;
		NVL(Tbl3.Fld2, PADR("*ERROR*", LEN(Tbl3.Fld2))) AS Fld2, ;
		NVL(Tbl3a.Fld2a, PADR("*ERROR*", LEN(Tbl3a.Fld2a))) AS Fld2a, ;
		NVL(Tbl3b.Fld2b, PADR("*ERROR*", LEN(Tbl3b.Fld2b))) AS Fld2b, ;
		NVL(Tbl4.Fld5, PADR("*ERROR*", LEN(Tbl4.Fld5))) AS Fld5 ;
	FROM dbc!Tbl1 ;
		LEFT OUTER JOIN dbc!Tbl2 ON Tbl2.t2_pk = Tbl1.t2_fk ;
		LEFT OUTER JOIN dbc!Tbl3 ;
			LEFT OUTER JOIN dbc!Tbl3a ON Tbl3a.t3a_pk = Tbl3.t3a_fk ;
			LEFT OUTER JOIN dbc!Tbl3b ON Tbl3b.t3b_pk = Tbl3.t3b_fk ;
			ON Tbl3.t3_pk = Tbl1.t3_fk ;
		LEFT OUTER JOIN dbc!Tbl4 ON Tbl4.t4_pk = Tbl1.t4_fk ;
	WHERE Tbl1.FK = ?MemVarPK

When I USE v_CallForCust NODATA, the nested foreign lookup fields (Tbl3a.Fld2a, Tbl3b.Fld2b) are the length I expect, LEN(table.field) in the lookup table.

However, if I run the SQL-SELECT part by itself (as VFP does when I set pnCus_pk and REQUERY()), the foreign fields from the three nested tables are all C(7) fields, with ".NULL. " as their value.

(Note, the intermediate table of the nested join (Tbl3) has no matching values to the main table.)

This seems like a VFP SQL bug to me! I gave them a NVL() to use!

Whassup?