Using CASE in WHERE Clause [message #309095] |
Wed, 26 March 2008 07:26  |
deay
Messages: 62 Registered: August 2005
|
Member |
|
|
Hi
I am trying to link a table to another table with columns that may have nulls under certain scenarios.
sample data:
SQL> select premcode,ltransnum,paymentid from premium where batchno=50803190;
PREMCODE LTRANSNUM PAYMENTID
-------------------- ---------- ----------
SCA04 13746839
SCA04 13746841
FF08 13725675
FF08 13725676
FF08 13725677
FF08 13725678
FF08 13725679
FF08 13725680
FF08 13725681
FF08 13725682
FF08 13746871
FF08 13725684
FF08 13725685
FF08 13725686
FF08 13725687
FF08 13725688
FF08 13725690
FF08 13746872
FF08 13725692
FF08 13746873
FF08 13725694
FF08 13725695
is there a way to use a CASE statement in the WHERE clause that
states if pr.ltransnum is null then link to column pr.paymentid
else link to pr.ltransnum?
I keep getting the following error:
where (case when pr.ltransnum is null then and py.transnum = pr.paymentID(+) else and py.ltransnum = pr.ltransnum(+)) *
ERROR at line 65:
ORA-00936: missing expression
select..........
from
pay py
,batchpay pb
,pyack pyac
,prem pr
,items it
where (case when pr.ltransnum is null then and py.transnum = pr.paymentID(+) else and py.ltransnum = pr.ltransnum(+))
and pyac.idnumber = py.idnumber
and pyac.transnum = pb.transnum
and py.transnum=pyac.transnum
and py.batchtransnum=pb.bd_id
and py.idnumber = pr.idnumber(+)
and pr.premcode = it.itemcode(+);
thanks for helping.
|
|
|
Re: Using CASE in WHERE Clause [message #309108 is a reply to message #309095] |
Wed, 26 March 2008 07:52   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Hi,
CASE Statement can return only Expressions, not Conditions (to which is pointing the error message). You have to modify it to something like (untested) AND CASE WHEN pr.ltransnum IS NULL THEN py.transnum ELSE py.ltransnum END =
CASE WHEN pr.ltransnum IS NULL THEN pr.paymentID(+) ELSE pr.ltransnum(+) END (by the way, if the condition is correct, you have very strange design).
|
|
|
Re: Using CASE in WHERE Clause [message #309128 is a reply to message #309108] |
Wed, 26 March 2008 08:51   |
deay
Messages: 62 Registered: August 2005
|
Member |
|
|
hi flyboy and thanks.
I used your suggestion and tried many other variations attempting to get it to work. I agree about the design, we are using third party application software and I have to work with what I got.
I get the following error when I used your suggestion:
and CASE WHEN pr.ltransnum IS NULL THEN py.transnum ELSE py.ltransnum END =
*
ERROR at line 71:
ORA-01417: a table may be outer joined to at most one other table
from
pay py
,batchpay pb
,pyack pyac
,prem pr
,items it
where pyac.idnumber = py.idnumber
and pyac.transnum = pb.transnum
and py.transnum=pyac.transnum
and py.batchtransnum=pb.bd_id
and py.idnumber = pr.idnumber(+)
and pr.premcode = it.itemcode(+)
and CASE WHEN pr.ltransnum IS NULL THEN py.transnum ELSE py.ltransnum END =
CASE WHEN pr.ltransnum IS NULL THEN pr.paymentID(+) ELSE pr.ltransnum(+) END
|
|
|
Re: Using CASE in WHERE Clause [message #309129 is a reply to message #309128] |
Wed, 26 March 2008 08:58  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
ORA-01417: a table may be outer joined to at most one other table
*Cause: a.b (+) = b.b and a.c (+) = c.c is not allowed
*Action: Check that this is really what you want, then join b and c first
in a view.
Regards
Michel
|
|
|