'Conditional' Joins?

[rabbit75]

Hi,
I have this SELECT that is almost working, but the LEFT OUTER JOIN is giving me a problem:

SELECT artran.invno, 'C' AS source, artran.item, artran.custno, artran.invdte,
    artran.extprice, artran.salesmn, artran.price, artran.qtyshp, artran.arstat,
    artran.artype, arcust.company, icitem.itmdesc,
    icitem.plinid, icitem.itmclss, icitem.code, icitem.comcode, icitem.price AS itemprice,
    icitem.ruitem, arcust.terr, arcust.pricecode, armast.cshipno,
    icitem.sunmsid, icitem.weight, 0 as unitpr1
  FROM artran JOIN arcust ON (arcust.custno = artran.custno)
    JOIN armast ON (armast.invno = artran.invno)
    JOIN icitem ON (icitem.item = artran.item) 
    LEFT OUTER JOIN icpric ON ((icpric.custno = artran.custno AND icpric.item=artran.item) OR
               (icpric.item=artran.item AND icpric.popt='I' AND icpric.psched=arcust.pricecode)) 
  WHERE artran.invdte >= @lc_beg_invdte
    AND artran.invdte <= @lc_end_invdte AND artran.arstat <> 'V'
    AND artran.currhist <> 'X'

The problem is, sometimes there are records in icpric that meet both the conditions in the LEFT OUTER JOIN:
((icpric.custno = artran.custno AND icpric.item=artran.item) OR
(icpric.item=artran.item AND icpric.popt='I' AND icpric.psched=arcust.pricecode)). This is correct, there can be 2 records, each one satisfies one condition. I am getting both records if both conditions are met. I only need the first record. Should I be using a join or something else?

 

[swampBoogie]

SELECT artran.invno, 'C' AS source, artran.item, artran.custno, artran.invdte,
    artran.extprice, artran.salesmn, artran.price, artran.qtyshp, artran.arstat,
    artran.artype, arcust.company, icitem.itmdesc,
    icitem.plinid, icitem.itmclss, icitem.code, icitem.comcode, icitem.price AS itemprice,
    icitem.ruitem, arcust.terr, arcust.pricecode, armast.cshipno,
    icitem.sunmsid, icitem.weight, 0 as unitpr1
  FROM artran JOIN arcust ON (arcust.custno = artran.custno)
    JOIN armast ON (armast.invno = artran.invno)
    JOIN icitem ON (icitem.item = artran.item)    
  WHERE artran.invdte >= @lc_beg_invdte
    AND artran.invdte <= @lc_end_invdte AND artran.arstat <> 'V'
    AND artran.currhist <> 'X'
    and exists (select * from icpric
   where (icpric.custno = artran.custno AND icpric.item=artran.item) OR
      (icpric.item=artran.item AND icpric.popt='I' AND icpric.psched=arcust.pricecode))

 

[rabbit75]

Thanks SwampBoogie, but I need to capture the record even if it is not in icpric. Using the exists statement won't give me those.

Thanks,
rabbit75

 

[swampBoogie]

Then I don't see why you have the icpric table in the query at all. You don't fetch any data from the table and as you have a left outer join it does not matter if there is any matching rows.

 

[rabbit75]

Ooops.. I should be picking up icpric.unitpr1 instead of 0 as unitpr1. Icpric is used for custom pricing. We need to show the price, if it is custom, from the icpric table. Otherwise, use the price from the item table.

Thanks,
rabbit75

 

[swampBoogie]

SELECT artran.invno, 'C' AS source, artran.item, artran.custno, artran.invdte,
    artran.extprice, artran.salesmn, artran.price, artran.qtyshp, artran.arstat,
    artran.artype, arcust.company, icitem.itmdesc,
    icitem.plinid, icitem.itmclss, icitem.code, icitem.comcode, icitem.price AS itemprice,
    icitem.ruitem, arcust.terr, arcust.pricecode, armast.cshipno,
    icitem.sunmsid, icitem.weight, 
(select min(icpric.unitpr1) from icpric
   where (icpric.custno = artran.custno 
     AND icpric.item=artran.item) 
      OR (icpric.item=artran.item 
     AND icpric.popt='I' 
     AND icpric.psched=arcust.pricecode))as unitpr1
  FROM artran JOIN arcust ON (arcust.custno = artran.custno)
    JOIN armast ON (armast.invno = artran.invno)
    JOIN icitem ON (icitem.item = artran.item)    
  WHERE artran.invdte >= @lc_beg_invdte
    AND artran.invdte <= @lc_end_invdte AND artran.arstat <> 'V'
    AND artran.currhist <> 'X'

You said that you wanted the first price but there is no such concept as first in RDBMS.