October 6, 2010 at 11:56 am
I have a query that is not pull all the data that it should be pulling. It should return 170 records. Before i added on Table SOP30300 I was getting 170 records. Once I added table SOP30300 I'm only getting 93 records. Can someone please tell me what I'm doing wrong.
SELECT '1060' AS bukrs, 'ZO' AS blart, CONVERT(VARCHAR(35), RM20101.DOCDATE, 112) AS bldat, CONVERT(VARCHAR(35), RM20101.POSTDATE, 112)
AS budat, RM20101.DOCNUMBR AS xblnr, RM00101.CUSTNAME AS bktxt, 'USD' AS waers, CONVERT(VARCHAR(35), RM20101.DOCDATE, 112)
AS wwert, '50' AS newbs, SOP30300.UNITPRCE AS wrbtr,
CASE WHEN RM20101.ORTRXAMT = RM20101.CURTRXAM THEN 0 ELSE RM20101.CURTRXAM END AS 'Remaining Amt',
RM20101.ORTRXAMT AS dmbrt, CN00300.TXTFIELD AS sgtxt, '' AS zuonr, '999901' AS newko, '' AS prctr, RM20101.CUSTNMBR AS Custnmbr,
RM00101.USERDEF1 AS 'SAP CUSTNUM', CONVERT(VARCHAR(35), RM20101.DOCDATE, 112) AS zfbdt, '' AS xref1, SOP10106.USRTAB09 AS xref2,
SOP30200.PRSTADCD, SOP30200.CNTCPRSN, SOP30200.ShipToName, SOP30200.ADDRESS1, SOP30200.ADDRESS2, SOP30200.ADDRESS3,
SOP30200.CITY, SOP30200.STATE, SOP30200.ZIPCODE, GL00105.ACTNUMST
FROM RM20101 AS RM20101 INNER JOIN
SOP10106 AS SOP10106 ON RM20101.TRXDSCRN = SOP10106.SOPNUMBE INNER JOIN
RM00101 AS RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR INNER JOIN
SOP30200 AS SOP30200 ON RM20101.DOCNUMBR = SOP30200.SOPNUMBE INNER JOIN
SOP10102 AS SOP10102 ON RM20101.TRXDSCRN = SOP10102.SOPNUMBE INNER JOIN
GL00105 AS GL00105 ON SOP10102.ACTINDX = GL00105.ACTINDX INNER JOIN
CN20100 AS CN20100 ON RM20101.CUSTNMBR = CN20100.CUSTNMBR AND RM20101.DOCNUMBR = CN20100.DOCNUMBR INNER JOIN
SOP30300 ON SOP30200.SOPNUMBE = SOP30300.SOPNUMBE AND RM20101.DOCNUMBR = SOP30300.SOPNUMBE INNER JOIN
CN00300 AS CN00300 ON CN20100.NOTEINDX = CN00300.NOTEINDX
WHERE (RM20101.CURTRXAM > '0.00') AND (RM20101.DOCDATE > '9/30/2009') AND (RM20101.ORTRXAMT >= RM20101.CURTRXAM) AND
(SOP10102.DISTTYPE = 1)
thank you,
October 6, 2010 at 12:02 pm
You're using INNER JOIN. Isn't it possible that only the 93 rows have matching values in that table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2010 at 12:18 pm
As Gail said - the inner join is probably limiting your result set.
Try an outer join.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 6, 2010 at 12:26 pm
kbnyny (10/6/2010)
I have a query that is not pull all the data that it should be pulling. It should return 170 records. Before i added on Table SOP30300 I was getting 170 records. Once I added table SOP30300 I'm only getting 93 records. Can someone please tell me what I'm doing wrong.
Write down your query in plain English (meaning, not in code). Examine it from all angles to make sure your query is doing precisely what the original request means it to do. You might find that 93 records is your expected result set, not 170.
But definitely, the JOINs are what are doing you in. The question is, should they be INNER or OUTER?
October 7, 2010 at 2:33 am
Here's a pretty version of your query which is much easier on the eye.
SELECT
'1060' AS bukrs,
'ZO' AS blart,
CONVERT(VARCHAR(35), RM20101.DOCDATE, 112) AS bldat,
CONVERT(VARCHAR(35), RM20101.POSTDATE, 112) AS budat,
RM20101.DOCNUMBR AS xblnr,
RM00101.CUSTNAME AS bktxt,
'USD' AS waers,
CONVERT(VARCHAR(35), RM20101.DOCDATE, 112) AS wwert,
'50' AS newbs,
SOP30300.UNITPRCE AS wrbtr,
CASE
WHEN RM20101.ORTRXAMT = RM20101.CURTRXAM THEN 0
ELSE RM20101.CURTRXAM END AS 'Remaining Amt',
RM20101.ORTRXAMT AS dmbrt,
CN00300.TXTFIELD AS sgtxt,
'' AS zuonr,
'999901' AS newko,
'' AS prctr,
RM20101.CUSTNMBR AS Custnmbr,
RM00101.USERDEF1 AS 'SAP CUSTNUM',
CONVERT(VARCHAR(35), RM20101.DOCDATE, 112) AS zfbdt,
'' AS xref1,
SOP10106.USRTAB09 AS xref2,
SOP30200.PRSTADCD,
SOP30200.CNTCPRSN,
SOP30200.ShipToName,
SOP30200.ADDRESS1,
SOP30200.ADDRESS2,
SOP30200.ADDRESS3,
SOP30200.CITY,
SOP30200.STATE,
SOP30200.ZIPCODE,
GL00105.ACTNUMST
FROM RM20101 AS RM20101
INNER JOIN SOP10106 AS SOP10106
ON RM20101.TRXDSCRN = SOP10106.SOPNUMBE
INNER JOIN RM00101 AS RM00101
ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN SOP30200 AS SOP30200
ON RM20101.DOCNUMBR = SOP30200.SOPNUMBE
INNER JOIN SOP10102 AS SOP10102
ON RM20101.TRXDSCRN = SOP10102.SOPNUMBE
INNER JOIN GL00105 AS GL00105
ON SOP10102.ACTINDX = GL00105.ACTINDX
INNER JOIN CN20100 AS CN20100
ON RM20101.CUSTNMBR = CN20100.CUSTNMBR AND RM20101.DOCNUMBR = CN20100.DOCNUMBR
INNER JOIN SOP30300
ON SOP30200.SOPNUMBE = SOP30300.SOPNUMBE AND RM20101.DOCNUMBR = SOP30300.SOPNUMBE
INNER JOIN CN00300 AS CN00300
ON CN20100.NOTEINDX = CN00300.NOTEINDX
WHERE (RM20101.CURTRXAM > '0.00')
AND (RM20101.DOCDATE > '9/30/2009')
AND (RM20101.ORTRXAMT >= RM20101.CURTRXAM)
AND (SOP10102.DISTTYPE = 1)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 7, 2010 at 2:36 am
So, what's this all about?
INNER JOIN SOP30300
ON SOP30200.SOPNUMBE = SOP30300.SOPNUMBE AND RM20101.DOCNUMBR = SOP30300.SOPNUMBE
Are you absolutely sure that RM20101.DOCNUMBR should match to SOP30300.SOPNUMBE ? Look at the join to SOP10106:
RM20101.TRXDSCRN = SOP10106.SOPNUMBE
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 7, 2010 at 5:44 am
Chris Morris-439714 (10/7/2010)
So, what's this all about?INNER JOIN SOP30300
ON SOP30200.SOPNUMBE = SOP30300.SOPNUMBE AND RM20101.DOCNUMBR = SOP30300.SOPNUMBE
Are you absolutely sure that RM20101.DOCNUMBR should match to SOP30300.SOPNUMBE ? Look at the join to SOP10106:
RM20101.TRXDSCRN = SOP10106.SOPNUMBE
And yet, there must be some matching numbers if he's getting records returned. Of course, whether or not this matching is correct is another matter.
kbnyny, beware the assumption of Table1.ID = Table2.ID. It's a sign of a lazy DB developer when other-table referencing columns aren't named to reflect what they truly contain.
October 7, 2010 at 6:00 am
Brandie Tarvin (10/7/2010)
Chris Morris-439714 (10/7/2010)
So, what's this all about?INNER JOIN SOP30300
ON SOP30200.SOPNUMBE = SOP30300.SOPNUMBE AND RM20101.DOCNUMBR = SOP30300.SOPNUMBE
Are you absolutely sure that RM20101.DOCNUMBR should match to SOP30300.SOPNUMBE ? Look at the join to SOP10106:
RM20101.TRXDSCRN = SOP10106.SOPNUMBE
And yet, there must be some matching numbers if he's getting records returned. Of course, whether or not this matching is correct is another matter...
Common problem with integer ID's but the density of results indicates otherwise - looks like a LEFT JOIN should be used, as suggested earlier.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 7, 2010 at 12:47 pm
Chris,
That table column fields do match they just don't have the same name. The tables represent some History and some Present. So the Present column tables will have a different column name when the data move to History. There was nothing about me being lazy.
Thanks for your help. The Left Join help. The record result did go up and I verified the data.
Thanks all for your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply