• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

Oracle -- LEFT Join "Linking" ?

Below fails when I uncomment the TRUNC line.

How can I setup this LEFT JOIN so it works
and makes b.amount "0" if there are no
associated EMPLOYEE_SALARY_TAB records ?

  SELECT a.emp_no,
    SUM(a.hours) Total_Hours,
    SUM(a.hours)*b.amount Total_Dollars,
    MAX(a.foreign_trn_id) foreign_trn_id
  FROM EXT_WAGE_TRANSACTION_TAB A
  LEFT JOIN EMPLOYEE_SALARY_TAB B
  ON A.EMP_NO = B.EMP_NO
  WHERE
  --TRUNC (a.from_time) >= TRUNC(b.valid_from) AND TRUNC (a.from_time) <= TRUNC(b.valid_to) and
  a.emp_no = '465521'
  GROUP BY a.emp_no,
    B.AMOUNT
  ORDER BY a.EMP_NO
0
finance_teacher
Asked:
finance_teacher
4 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need to make hte condition part of the left join;
SELECT a.emp_no,
    SUM(a.hours) Total_Hours,
    SUM(a.hours)*b.amount Total_Dollars,
    MAX(a.foreign_trn_id) foreign_trn_id
  FROM EXT_WAGE_TRANSACTION_TAB A
  LEFT JOIN EMPLOYEE_SALARY_TAB B
  ON A.EMP_NO = B.EMP_NO
  AND TRUNC (a.from_time) >= TRUNC(b.valid_from) 
   AND TRUNC (a.from_time) <= TRUNC(b.valid_to) 
  WHERE  a.emp_no = '465521'
  GROUP BY a.emp_no,
    B.AMOUNT
  ORDER BY a.EMP_NO

Open in new window

0
 
HainKurtSr. System AnalystCommented:
try this:

TRUNC (a.from_time) >= TRUNC(b.valid_from) AND TRUNC (a.from_time) <= TRUNC(b.valid_to) and

-->

TRUNC (a.from_time) >= TRUNC(nvl(b.valid_from,a.from_time)) AND TRUNC (a.from_time) <= TRUNC(nvl(b.valid_to,a.from_time)) and
0
 
sdstuberCommented:
you'll have to use NVL to make the b.amount 0 if there are no records because the left join will cause it to be null


SUM(a.hours)*NVL(b.amount,0) Total_Dollars,


you should put your join conditions in the ON clause vs the WHERE clause as angeliii pointed out too
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
finance_teacherAuthor Commented:
I will test
0
 
gajmpCommented:
angelIII:
    If there is no records in EMPLOYEE_SALARY_TAB  then the below condition also will fail so whats the purpose of adding those condition in ON clause
  AND TRUNC (a.from_time) >= TRUNC(b.valid_from)
   AND TRUNC (a.from_time) <= TRUNC(b.valid_to)


as sdstuber: said nvl(amount,0), is this not enough and is we need to add those condition in ON clause?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
angelIII:
    If there is no records in EMPLOYEE_SALARY_TAB  then the below condition also will fail so whats the purpose of adding those condition in ON clause
  AND TRUNC (a.from_time) >= TRUNC(b.valid_from)
   AND TRUNC (a.from_time) <= TRUNC(b.valid_to)

the conditions for the (LEFT) JOIN, are evaluated for the JOIN only, so if you have:
SELECT ...
  FROM A
  LEFT JOIN B on <complex condition>

it will return all records from table A, even if there is no match on <complex condition> for any rows in table B
consider:
SELECT *
  FROM A
  LEFT JOIN B on 1=0

the condition will be always false. still all records for table A will be returned ... with all columns from table B being returned NULL.

IF you put that condition into WHERE, it's different, because WHERE conditions are evaluated AFTER all join conditions, aka on the "intermediate results"
consider:
SELECT *
  FROM A
  LEFT JOIN B on A.ID = B.ID
WHERE 1 = 0

here, the WHERE clause will make no records will ever be returned for this query, whatever you have in table A or B
I hope this clarifies
0
 
gajmpCommented:
angelIII:
     good explanation. As per you, filtering records at the time of joining is better than join and fetch million record and then filter.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
when you have INNER JOINs, it won't matter (neither output nor performance)
when you have OUTER JOIN, it does matter especially for output, but also for performance as you indicated.

I sometimes do write LEFT JOIN to get a "better" execution plan than with the INNER JOIN, if I know (as by the data logics) that the output will remain the same.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now