>Hi all
>i have a sql select like this :
>
>Select Iif(Isnull(a.fpath),'',a.fpath) As afpath, a.qdate, a.qtime,a.fnum, Iif(Isnull(b.fpath),'',b.fpath) As bfpath,;
> iif(Isnull(b.Status),0,1) As Status, c.family, c.Name, d.Name,a.userpass,d.ccode;
> FROM ;
> questions a ;
> LEFT Outer Join answers b ;
> ON a.fnum = b.fnum ;
> LEFT Outer Join students c ;
> ON a.userpass = c.userpass ;
> LEFT Outer Join classes d ;
> ON c.ccode = d.ccode Into Cursor temp
>
>the problem is that b.fpath is a memo field and if the first record from answres table is empty the rest field in rescords converts to a character field but if it's not empty the rest is ok.
>
>What's wrong?
Nothing is wrong, just SQL parser get the length of the field from first record of the dataset.
In VFP8 you could do this:
CREATE CURSOR crsTest (Fld1 M)
Select Iif(Isnull(a.fpath),crsTest.Fld1,a.fpath) As afpath,;
a.qdate, a.qtime,a.fnum,;
Iif(Isnull(b.fpath),crsTest.Fld1,b.fpath) As bfpath,;
iif(Isnull(b.Status),0,1) As Status, c.family, c.Name, d.Name,a.userpass,d.ccode;
FROM ;
questions a ;
LEFT Join answers b ON a.fnum = b.fnum ;
LEFT Join students c ON a.userpass = c.userpass ;
LEFT Join classes d ON c.ccode = d.ccode;
LEFT Join CrsTest ON 1 = 1;
Into Cursor temp
In VFP9 you could use CAST('' AS M) instead.
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.