Hi George,

The explanation definetely helped, but my original suggestion still applies.

1) Change to INNER JOIN instead of where (this way it would be easier to follow, what is where condition and what is join condition).

2) Try to join only few tables first, then run subsequential select statements. I actually looked only on the first command, that seems correct at the first glance, but still very hard to grasp. I bet, dividing by several SQL statements would help at least from maintenance point of view...

>Nadya -
>
>Sorry about any confusion. I actually stripped out the comments, because they didn't add to the clarity.
>
>The data is structured like this:
>
>A provider (V_PROV) can have many releases (R_RELEAS), and many products (V_PROD)
>A product can have many registrations (P_PRODR)
>Registrations are on channels (V_CHAN)
>R_PRODR contains all the registrations for this release
>
>We're trying to get the following, in English [With field translations in brackets]:
>

"All of the movie [v_prod.prodclass='MOV'] registrations on VC channels
>[v_chan.vcchan=.t.] for the provider of the release which are either in this
>release [from R_PRODR] or are for a guarantee Product [v_prod.guarantee] or
>are for an Estimated provider [v_prov.est]."

>
>We pass this function the releasid (the PK of R_RELEAS), as tnReleasid.
>
>RELATIONS:
>V_PROV-->R_RELEAS (via prov_id)
>V_PROV-->V_PROD (via prov_id)
>V_PROD-->P_PRODR (via prod_id)
>P_PRODR-->V_CHAN (via chan_id)
>
>R_PRODR has 2 fields: releasid & prodrid.
>
>My problem is that I think that the three statements should get the same results. We ran the three statements against 90 different releases, and got the same results for 89 of them. For one release, we get three different tally's for the three different queries. This causes the provider to get underpaid by over $200,000 (which is an undesirable result!)
>
>We tried the having clause because it worked last month. Or so we thought. This month it turned up different results. We've re-indexed all the data, and are still perplexed.
>
>Thanks in advance for any information, and for even looking into this mess.
>
>I guess I could ZIP up the data and put it on our website, if it's necessary. It's not too big.
>
>- George
>
>>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