Home » SQL & PL/SQL » SQL & PL/SQL » Using CASE in WHERE Clause (10g win2003 Server)
Using CASE in WHERE Clause [message #309095] Wed, 26 March 2008 07:26 Go to next message
deay
Messages: 51
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
deay
Messages: 51
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 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Sum recursively values inside same column
Next Topic: Getting ORA-00604 followed by ORA-1003
Goto Forum:
  


Current Time: Fri Dec 09 04:21:25 CST 2016

Total time taken to generate the page: 0.19255 seconds