左外连接没有给出任何结果

I want to join the tables which would have all values from the table Portal_Order alongwith other matching values from other tables. LEft outer Join returns no values

我想加入表，这些表将包含表Portal_Order中的所有值以及来自其他表的其他匹配值。 LEft外部Join不返回任何值

Query :

查询：

With Max_Date As (Select Max(Change_Date) Change_Date, Ocoe_Job_Id 
From Ocoe_Job_Status Where Ocoe_Job_Id In 
(Select Ocoe_Job_Id From Portal_Order Where To_Char(Created_Date,'YYYY-MM-DD') 
Between ( Select To_Char(Sysdate-1000,'YYYY-MM-DD') From Dual) 
And ( Select To_Char(Sysdate,'YYYY-MM-DD') From Dual)  ) 
Group By Ocoe_Job_Id) 

Select Order_Id, Order_Name, Order_Desc, B.Description As Order_Status, C.Ocoe_Job_Id, C.Comments 
As Communication_Id , Communication_Name, Created_By, Count(*) Over () As Total_Record_Count, Row_Number()
Over ( Order By Order_Id ) Row_Number 
From  Max_Date D,
Ocoeowner.Portal_Order A
Left Outer Join Ocoeowner.Ocoe_Job_Status C On C.Ocoe_Job_Id =A.Ocoe_Job_Id
LEFT OUTER JOIN Ocoeowner.Portal_Order_Status_Code_Lk B on A.Order_Status = B.Status_Code
 Where To_Char(Created_Date,'YYYY-MM-DD') 
Between ( Select To_Char(Sysdate-1000,'YYYY-MM-DD') From Dual) And ( Select To_Char(Sysdate,'YYYY-MM-DD') From Dual)  
And C.Ocoe_Job_Id = D.Ocoe_Job_Id And D.Change_Date = C.Change_Date AND communication_name='ptuletters';

2 个解决方案

#1

The predicate condition needs to be in the On clause for the join, not in the where clause. The way Outer joins work, is after the join conditions are analyzed, all the rows from the "outer side" that do not match the inner side are added back in.... But this all happens before the where clause is processed. So if the where clause predicate filters on an attribute from the outer side of an outer join, all those rows will be removed again... (They are all null). Put the predicate in the join condition instead

谓词条件需要在连接的On子句中，而不是在where子句中。外部连接工作的方式是，在分析连接条件之后，“内侧”与内侧不匹配的所有行都被添加回....但这一切都发生在where子句被处理之前。因此，如果where子句谓词从外连接的外侧过滤属性，则所有这些行将再次被删除...（它们都是空的）。将谓词放在连接条件中

And C.Ocoe_Job_Id = D.Ocoe_Job_Id And D.Change_Date = C.Change_Date

has to be moved, and may be too AND communication_name='ptuletters', depending of the source table

必须被移动，并且可能也是AND communication_name ='ptuletters'，具体取决于源表

#2

try this:

尝试这个：

WITH Max_Date AS
  (SELECT MAX(Change_Date) Change_Date,
    Ocoe_Job_Id
  FROM Ocoe_Job_Status
  WHERE Ocoe_Job_Id IN
    (SELECT Ocoe_Job_Id
    FROM Portal_Order
    WHERE TO_CHAR(Created_Date,'YYYY-MM-DD') BETWEEN
      ( SELECT TO_CHAR(Sysdate-1000,'YYYY-MM-DD') FROM Dual
      )
    AND ( SELECT TO_CHAR(Sysdate,'YYYY-MM-DD') FROM Dual)
    )
  GROUP BY Ocoe_Job_Id
  )

SELECT Order_Id,
  Order_Name,
  Order_Desc,
  B.Description AS Order_Status,
  C.Ocoe_Job_Id,
  C.Comments AS Communication_Id ,
  Communication_Name,
  Created_By,
  COUNT(*) Over () AS Total_Record_Count,
  Row_Number() Over ( Order By Order_Id ) Row_Number
FROM 
 Ocoeowner.Portal_Order A LEFT OUTER JOIN Ocoeowner.Ocoe_Job_Status C ON
  A.Ocoe_Job_Id=C.Ocoe_Job_Id
 LEFT OUTER JOIN Ocoeowner.Portal_Order_Status_Code_Lk B ON
  A.Order_Status = B.Status_Code
 LEFT OUTER JOIN Max_Date D ON
  C.Ocoe_Job_Id     = D.Ocoe_Job_Id
  AND C.Change_Date     = D.Change_Date
WHERE 
 TO_CHAR(Created_Date,'YYYY-MM-DD') BETWEEN
  ( SELECT TO_CHAR(Sysdate-1000,'YYYY-MM-DD') FROM Dual
  )
 AND ( SELECT TO_CHAR(Sysdate,'YYYY-MM-DD') FROM Dual)
AND NVL(communication_name, 'ptuletters')='ptuletters';

#1

And C.Ocoe_Job_Id = D.Ocoe_Job_Id And D.Change_Date = C.Change_Date