Monday, February 20, 2012

join on key SOMETIMES.....?

I need to join a table on a field with conditions.
----------------

--current join
LEFT OUTER JOIN GL on
(
(LINKS.accthigh >= GL.acctnu)
and
(LINKS.acctLow <= GL.acctnu)
)
and LINKS.dept = GL.dept and
and LINKS.fund = GL.fund

--needed something like this...but I am sure not correct syntax
LEFT OUTER JOIN GL on
(
(LINKS.accthigh >= GL.acct)
and
(LINKS.acctLow <= GL.acct)
)
if links.deptnu <> -1
LINKS.dept = GL.deptnu and
if links.fundnu <> -1
LINKS.fund = GL.fundNu

----------------
The field LINKS.fund or LINKS.dept can have a value of -1 which means to join beyond the dept or fund boundaries (ie any fund or any dept).

Is this possible or do I need to looking for a new approach?

Thanks for your help!
-Rtry this:LEFT OUTER
JOIN GL
on LINKS.accthigh >= GL.acct
and LINKS.acctLow <= GL.acct
and LINKS.dept =
( case when links.deptnu <> -1
then GL.deptnu
else LINKS.dept end )
and LINKS.fund =
( case when links.fundnu <> -1
then GL.fundNu
else LINKS.fund end )|||Thats exactly what I was looking for!

Thanks again for the help!

GBY,
-R

No comments:

Post a Comment