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