getting duplicate rows when adding a new join

• mcummings

  SSC Rookie

  Points: 29

  More actions

  July 31, 2013 at 11:39 am

  #280736

  I am working on some Else's code, I am enhancing a report

  the program the code is coming from is visual foxpro as pass through SQL (same as sql server query code)

  the code is being sent to sql server 2005 through foxpro's sqlexec command

  I have been working on this for 3 days, I am on a contract and need to resolve this, please help

  Thanks,

  Michael

  *********************************************************************8

  the SQL server tables are -

  aritem - count: 79018, match fields - fcustno, fpartno, substring(fsokey,1,6), frev, fcinvoice

  slcdpm - count: 909, match fields - fcustno

  sorels - count:62080 match fields - fsono

  inmast - count:9505 match fields - fpartno, frev

  armast - count: 54191 match fields - fcinvoive

  sql code below

  *******************************************************

  use m2mdata01

  go

  select aritem.fcustno,

  slcdpm.fcompany,

  slcdpm.fcity,

  slcdpm.fstate,

  slcdpm.fcountry,

  aritem.fcinvoice,

  aritem.fitem,

  aritem.fpartno,

  aritem.frev,

  aritem.fmdescript,

  armast.finvdate,

  sorels.fmatlcost,

  sorels.flabcost,

  sorels.fovhdcost,

  sorels.fsono,

  CASE WHEN aritem.fprodcl IS NULL

  THEN ''

  ELSE aritem.fprodcl

  END AS fprodcl,

  CASE WHEN inmast.fgroup IS NULL

  THEN ''

  ELSE inmast.fgroup

  END AS fgroup,

  aritem.fshipqty,

  aritem.fprice,

  aritem.ftotprice,

  aritem.fcost AS INV_cost,

  CASE WHEN inmast.fstdcost IS NULL

  THEN .00000

  WHEN SUBSTRING(aritem.fcinvoice,1,2) = 'CM'

  AND armast.fcsource NOT IN ('', 'R')

  THEN .00000

  ELSE inmast.fstdcost

  END AS fstdcost,

  CASE WHEN inmast.fstdcost IS NULL

  THEN .00000

  WHEN SUBSTRING(aritem.fcinvoice,1,2) = 'CM'

  AND armast.fcsource NOT IN ('', 'R')

  THEN .00000

  ELSE inmast.fstdcost * aritem.fshipqty

  END AS STD_T_COST,

  CASE WHEN inmast.flastcost IS NULL

  THEN .00000

  WHEN SUBSTRING(aritem.fcinvoice,1,2) = 'CM'

  AND armast.fcsource NOT IN ('', 'R')

  THEN .00000

  ELSE inmast.flastcost

  END AS flastcost,

  CASE WHEN inmast.flastcost IS NULL

  THEN .00000

  WHEN SUBSTRING(aritem.fcinvoice,1,2) = 'CM'

  AND armast.fcsource NOT IN ('', 'R')

  THEN .00000

  ELSE inmast.flastcost * aritem.fshipqty

  END AS LST_T_COST,

  CASE WHEN slcdpm.fcountry IN ('United States')

  THEN '1'

  ELSE '2'

  END as intl,

  CASE WHEN SUBSTRING(aritem.fsalesacc,1,1) = '4'

  THEN '1'

  ELSE '2'

  END as nonsales,

  CASE WHEN aritem.fcustno IN ('010134', '010164', '011202', '010011')

  THEN '1'

  ELSE '2'

  END AS interco,

  aritem.fsalesacc

  FROM aritem LEFT OUTER JOIN slcdpm

  ON aritem.fcustno = slcdpm.fcustno

  ***********************************************************************

  when I add this code to the select it changes the results record count from 198 to 3075

  the correct count is 198

  ************************************************************************

  left outer join sorels

  on sorels.fsono = substring(aritem.fsokey,1,6)

  end of added code

  *************************************************************************

  LEFT OUTER JOIN inmast

  ON aritem.fpartno = inmast.fpartno

  AND aritem.frev = inmast.frev

  LEFT OUTER JOIN armast

  ON aritem.fcinvoice = armast.fcinvoice

  WHERE armast.fdgldate >= '06/01/2013'

  AND armast.fdgldate <= '06/30/2013'

  AND armast.fcstatus <> 'V'

  AND ((SUBSTRING(aritem.fcinvoice,1,2) <> 'CM' AND armast.fcsource = 'S')

  OR (SUBSTRING(aritem.fcinvoice,1,2) = 'CM' AND armast.fcsource IN ('R', 'C', ' ', 'I')))

  AND 1=1

  AND 1=1

  AND 1=1

  order by aritem.fcustno, aritem.fcinvoice, aritem.fsokey

• Alvin Ramard

  SSC-Forever

  Points: 41190

  More actions

  July 31, 2013 at 11:49 am

  #1637504

  If you're getting more rows than you should after adding a join, it's probably because you should be joining to that table using more than just 1 join condition. We can't say for sure what the problem is without knowing more about the data and tables.

  ---

  
  Alvin Ramard
  Memphis PASS Chapter[/url]

  All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

  For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  July 31, 2013 at 12:56 pm

  #1637529

  what happens if you replace "left outer join sorels" with an inner join?

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• mcummings

  SSC Rookie

  Points: 29

  More actions

  July 31, 2013 at 12:58 pm

  #1637533

  same results, tried it

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  July 31, 2013 at 1:07 pm

  #1637535

  mcummings (7/31/2013)

  ---

  same results, tried it

  I would check the data - visually validate if returned rows are the correct ones(*) or not, if not, check what is the query returning.

  (*) correct ones as defined on the actual predicate, not on the business specification.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  July 31, 2013 at 1:08 pm

  #1637536

  You haven't supplied enough information, but you just need to figure out the correct join.

  My guess is that because sorels has costs in it that they might relate to items and you might need to also join on

  aritem.fitem

  or maybe

  aritem.fpartno

  But we don't know the structure of the tables, so can only guess

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]