>>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?
Rich Addison, Micro Vane, Inc., Kalamazoo, MI
Relax, don't worry, have a homebrew.
- Charlie Papazian, The New Complete Joy of Home Brewing