> >
> > Steve
> >
> > You will soon be the resident expert of SQL here :).
> >
> > I should have told you this long ago. Don't use A,B,C for alias of tables in the SQL. It may lead to problems.
> >
> > Also, you are using BlockProfile in 1 query and 2 subqueries. Each time you have given the table the same alias. That may be confusing like
> > (A.ddate>=C.dstart AND A.ddate <=C.dend)
> > in this A is Dailywork2 of the outside SELECT but C can be of the outside SELECT or of the SUB SELECT.
> >
> > Regards
> > Tushar
>
>
>
> How does this look, Tushar?
>
>

**Improvement ... Changing the Alias Name
> SELECT DW.ddate Date,;
> DW.cblock Block,;
> BL.nArea Area,;
> BP.cName SubBlock,;
> ROUND(iif(not isnull(BP.nspx) and not isnull(BP.nspy) and BP.nspx<>0 and BP.nspy<>0,BP.narea*(43560/(BP.nspx*BP.nspy)),BP.nrdmPlants),2) as Plants,; 
> IIF(BP.cparent=DW.Cblock,(SELECT sum(RowPlants(BP2.nspx,BP2.nspy,BP2.narea,BP2.nrdmPlants)) as TotPlants FROM Blockprofile BP2 ;
> WHERE BP2.cParent=DW.cBlock AND (DW.ddate>=BP2.dstart AND DW.ddate <=BP2.dend)),;
> ROUND(iif(not isnull(BP.nspx) and not isnull(BP.nspy) and BP.nspx<>0 and BP.nspy<>0,BP.narea*(43560/(BP.nspx*BP.nspy)),BP.nrdmPlants),2))as TotPlants,;
> (DW.nMen+DW.nWmn+DW.nadl) as Heads,;
> ROUND((DW.nMen+DW.nWmn+DW.nadl)/;
> (IIF(BP.cparent=DW.Cblock,(SELECT sum(RowPlants(BP2.nspx,BP2.nspy,BP2.narea,BP2.nrdmPlants)) as TotPlants FROM Blockprofile BP2 ;
> WHERE BP2.cParent=DW.cBlock AND (DW.ddate>=BP2.dstart AND DW.ddate <=BP2.dend)),ROUND(iif(not isnull(BP.nspx) and not isnull(BP.nspy) and BP.nspx<>0 and BP.nspy<>0,BP.narea*(43560/(BP.nspx*BP.nspy)),BP.nrdmPlants),2)))*(BP.narea*(43560/(BP.nspx*BP.nspy))),2) as HdsSpread;
> From Dailywork2 DW;
> LEFT Outer JOIN BlockProfile BP;
> ON(DW.cBlock = BP.cParent OR DW.cBlock=BP.cName) and (DW.ddate>=BP.dstart AND DW.ddate <=BP.dend);
> LEFT OUTER JOIN Block2 BL;
> on DW.cBlock=BL.block
> 
> 
> 
> FUNCTION RowPlants
> LPARAMETERS lnX,lnY,lnArea,lnNrdm
> IF ISNULL(lnX) OR ISNULL(lnY) OR lnX=0 OR lnY=0
> 	RETURN lnNrdm
> ELSE
> 	RETURN ROUND(lnArea*43560/(lnX*lnY),2)
> ENDIF
> 

> Do you think that by not declassifying my sub-selects, it was the reason why I wasn't able to segregate my select at the bottom with a join like how you had shown me in one of your earlier threads, treating the sub-select with a new name, which was used as a source of the main fields in the main select?
>
> Steve

Steve

The aliases all look OK. I have never done an IIF(Expl,SELECT1,SELECT2). Don't know if it works. One thing I would most probably do is

select ....., 000000.000 as HdsSpread , .......

and make the cursor readwrite. Afterwards I would do
REPLACE ALL HdsSpread with Heads/TotPlants

Regards
Tushar