Hi George,

Are you supporting legacy app? Can you divide this select into smaller parts and join result of first select with others? Right now it's really hard to follow the logic in this SQL. Can you clarify, what exactly you're trying to achieve? E.g. comment this SQL?

Thanks in advance.

>I have a problem.
>
>The follow three commands generate different results for certain data.
>
>Anyone here able to spot a difference?
>
>Command #1:
>

>SELECT releasid, prodrid, v_prod.titfull, v_prov.zeromonths, v_prod.prod_id, v_prod.guarantee, v_prod.guartype ;
>	FROM p_prodr, v_prod, r_releas, v_prov, v_chan ;
>	WHERE p_prodr.prod_id = v_prod.prod_id ;
>		AND v_prod.prov_id = r_releas.prov_id ;
>		AND v_prov.prov_id = r_releas.prov_id ;
>		AND r_releas.releasid = tnReleasid ;
>		AND v_prod.prodclass = 'MOV' ;
>		AND v_chan.chan_id = p_prodr.chan_id ;
>		AND p_prodr.playmonth >= {1/1/95} ;
>		AND (v_prod.guarantee OR v_prov.est OR ;
>			p_prodr.prodrid IN (SELECT prodrid FROM r_prodr WHERE releasid = tnReleasid)) ;
>		AND v_chan.vcchan ;
>	ORDER BY 3, 5, 2 ;
>	INTO CURSOR cTempCalc
>

>
>Command #2:
>

>SELECT releasid, prodrid, v_prod.titfull, v_prov.zeromonths, v_prod.prod_id, v_prod.guarantee, v_prod.guartype, ;
>		v_chan.vcchan AS xxxx ;
>	FROM p_prodr, v_prod, r_releas, v_prov, v_chan ;
>	WHERE p_prodr.prod_id = v_prod.prod_id ;
>		AND v_prod.prov_id = r_releas.prov_id ;
>		AND v_prov.prov_id = r_releas.prov_id ;
>		AND r_releas.releasid = tnReleasid ;
>		AND v_prod.prodclass = 'MOV' ;
>		AND v_chan.chan_id = p_prodr.chan_id ;
>		AND p_prodr.playmonth >= {1/1/95} ;
>		AND (v_prod.guarantee OR v_prov.est OR ;
>			p_prodr.prodrid IN (SELECT prodrid FROM r_prodr WHERE releasid = tnReleasid)) ;
>	ORDER BY 3, 5, 2 ;
>	HAVING xxxx = .t. ;
>	INTO CURSOR cTempCalc
>

>
>Command #3:
>

>SELECT p_prodr.* FROM p_prodr, v_prod, v_prov ;
>	WHERE p_prodr.prod_id = v_prod.prod_id ;
>		AND v_prod.prov_id = v_prov.prov_id ;
>		AND (p_prodr.prodrid IN (SELECT prodrid FROM r_prodr WHERE releasid = tnReleasid) OR v_prod.guarantee OR v_prov.est) ;
>		AND p_Prodr.playmonth >= {1/1/95} ;
>	INTO CURSOR xProdr
>
>SELECT releasid, prodrid, v_prod.titfull, v_prov.zeromonths, v_prod.prod_id, v_prod.guarantee, v_prod.guartype ;
>	FROM xProdr, v_prod, r_releas, v_prov, v_chan ;
>	WHERE xProdr.prod_id = v_prod.prod_id ;
>		AND v_prod.prov_id = r_releas.prov_id ;
>		AND v_prov.prov_id = r_releas.prov_id ;
>		AND r_releas.releasid = tnReleasid ;
>		AND v_chan.chan_id = xProdr.chan_id ;
>		AND v_chan.vcChan ;
>	ORDER BY 3, 5, 2 ;
>	INTO CURSOR cTempCalc
>
>USE IN xProdr
>

>
>Please note the following (at least in all of the data which is pertinent):
> - v_prod.guarantee is always .F.
> - v_prov.est is always .F.
> - v_chan.vcchan is always .T.
> - Removing the "AND v_chan.vcchan" in the first query causes more data to be selected, even though every result has vcChan = .T.
>
>We're scratching our heads.
>
>- George