gkorea_at_verizon.net schreef:
> Hi everyone, I am having trouble with an sql I am writing -- I have 2
> tables that I want to get info from, in some cases there will be data
> in one but not in the other but I still want the query to work -- that
> is an outer join but whichever way I try I can't get any data to
> return -- can anyone help maybe I am just missing something -- I am on
> AIX running oracle 9.2 -- I have listed the sql below
>
>
> Thanks in advance
>
> Jerry
>
> SELECT
> LAWSON.QUARTDED.EMPLOYEE,LAWSON.QUARTDED.DED_CODE,
> LAWSON.QUARTDED.DED_AMT,
> LAWSON.PRWEEKS.NBR_WEEKS
> FROM
> LAWSON.QUARTDED left join LAWSON.PRWEEKS on lawson.quartded.employee =
> lawson.pr
> weeks.employee
> WHERE
> LAWSON.QUARTDED.DED_CODE in ('PAUC') AND
> LAWSON.QUARTDED.PAYROLL_YEAR = '2006' and
> LAWSON.QUARTDED.QUARTER = '4' and
> LAWSON.PRWEEKS.QUARTER = '4' and
> LAWSON.PRWEEKS.PAYROLL_YEAR = '2006' and
> LAWSON.QUARTDED.EMPLOYEE = '160006'
>
> By the way -- data will exist in the quartded file but not in prweeks
> -- I want prweeks to return null
>
This is not an outer join.
Try this
WHERE
LAWSON.QUARTDED.DED_CODE = 'PAUC' AND
LAWSON.QUARTDED.PAYROLL_YEAR = '2006' and
LAWSON.QUARTDED.QUARTER = '4' and
LAWSON.PRWEEKS.QUARTER (+) = LAWSON.QUARTDED.QUARTER and
LAWSON.PRWEEKS.PAYROLL_YEAR (+) = LAWSON.QUARTDED.PAYROLL_YEAR and
LAWSON.QUARTDED.EMPLOYEE = '160006'
Suspecting prweeks.payroll_year to be NULL as well.
Of course, a table can only return NULL if it's allowed to
return NULL. If prweeks.quarter and prweeks.payroll_year
are defined as NOT NULL, this is not going to work!
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Received on Mon Mar 26 2007 - 13:11:28 CDT
