0

I am working on a SQL statement with SQL Server. I am left joining the main select statement with a sub select statement, to get the value from sub select statement, and when there's no matching value in sub select statement, it returns the value from main select statement.

This is my SQL:

SELECT 
    OIVL.ItemCode, OIVL.BASE_REF, ISNULL(A.OrigLine, OIVL.DocLineNum)
FROM 
    OIVL
LEFT JOIN 
    (SELECT DISTINCT 
         IPF1.OrigLine, OIPF.DocNum, IPF1.BaseEntry, IPF1.Reference,  
         IPF1.ItemCode, IBT1.BatchNum
     FROM 
         IPF1
     LEFT JOIN 
         OIPF ON IPF1.DocEntry = OIPF.DocEntry
     LEFT JOIN 
         IBT1 ON IPF1.BaseEntry = IBT1.BaseEntry 
              AND IPF1.ItemCode = IBT1.ItemCode 
              AND IPF1.OrigLine = IBT1.BaseLinNum) A ON OIVL.BASE_REF = A.DocNum 
                                                     AND OIVL.ItemCode = A.ItemCode 
                                                     AND OIVL.DocLineNum = A.OrigLine

I've checked the result of sub select statement, it returns correct result - same item code on line 5 and 7.

But the result is returning OIVL.DocLineNum while A.OrigLine has value.

halfer
  • 19,824
  • 17
  • 99
  • 186
Fox Feng
  • 23
  • 4
  • Well, in case I didn't explain clearly what I want. I have two data sets, from table A and B. I am trying to get transaction details from table A, and fill in data from table B when there's a match of itemcode, entry number, transation type, line number in table B. The problem I have now is, even though there is a match in table B, the SQL is still returning data from table A as result – Fox Feng Mar 22 '20 at 11:15
  • If you add A.OrigLine and OIVL.DocLineNum by themselves to your first line, it should highlight your issue. Maybe it's wrong join conditions at the bottom, hard to tell without the data. – OwlsSleeping Mar 22 '20 at 11:16
  • hmm, sorry, can't give out the data. Guess I need to playing around the data and try to sort it out myself then – Fox Feng Mar 22 '20 at 11:28
  • 2
    Can you at least provide some sample/dummy (not original) data? – mkRabbani Mar 22 '20 at 11:43
  • 1
    @FoxFeng Are you sure that you are receiving values for the combination of BASE_REF, ItemCode and DocLineNum from the sub-select statement? – sacse Mar 22 '20 at 13:48
  • That's one of your join columns and you're looking for the two values to be equal. The left table will never be null. – shawnt00 Mar 25 '20 at 13:16

0 Answers0