Home » RDBMS Server » Server Administration » DECODE-How to include a virtual 'AND' or NOT EQUAL TO
DECODE-How to include a virtual 'AND' or NOT EQUAL TO [message #371087] Thu, 24 August 2000 13:28 Go to next message
Chris
Messages: 128
Registered: November 1998
Senior Member
Hi everyone,

Could someone tell me if it's possible to use the word 'AND' or <> or something like them in a DECODE statement.
Ex: DECODE(A.VALUE, 2 AND B.VALUE <> 3, C.VALUE, 2 'AND' B.VALUE = 3, D.VALUE, NULL) AS HOPE_THIS_WORKS

Here's the statement I'm dealing with now:

SUM(DECODE(A.TYPE, 2, (ROUND(SUM((A.INV_TOTAL-A.TAX_TOTAL)* .07),3)), NULL)) "BILLING_INVOICE(GST)",

Essentially I want to say if A.TYPE = 2 AND B.CODE <> 3 then calculate the GST, elseif A.TYPE = 2 AND B.CODE = 3 then NULL) AS HOPE_THIS_WORKS

Any help would be greatly appreciated.
Re: DECODE-How to include a virtual 'AND' or NOT EQUAL TO [message #371088 is a reply to message #371087] Thu, 24 August 2000 14:05 Go to previous messageGo to next message
JOHN
Messages: 182
Registered: April 1998
Senior Member
You have me a little confused - how many values are we checking a,b,c,d = 4? You can use nested decodes if value d depends on resultant values of c and c depends on resultant value of b which is based on the resultant value of a.

decode(a.type,2,decode(b.value,3,null,(ROUND(SUM((A.INV_TOTAL-A.TAX_TOTAL)* .07),3)),null)

I hope that I am not confusing you.
If you can give me more specifics, maybe I can do a better job of contructing the decodes for you.

John
Re: DECODE-How to include a virtual 'AND' or NOT EQUAL TO [message #371089 is a reply to message #371088] Thu, 24 August 2000 14:12 Go to previous messageGo to next message
Chris
Messages: 128
Registered: November 1998
Senior Member
Hi John,

Thanks for your reply. Trust me I have 'me' confused on this one too! Basically put here's the logic:

If A.VALUE = 2 AND B.VALUE <> 3 THEN (CALCULATE TAX)
else if
A.VALUE = 2 AND B.VALUE = 3 THEN (DON'T CALULATE TAX)
Re: DECODE-How to include a virtual 'AND' or NOT EQUAL TO [message #371104 is a reply to message #371089] Fri, 25 August 2000 16:08 Go to previous message
Bobby
Messages: 32
Registered: August 2000
Member
Hi Chris

As John suggested you need a nested Decode which will be something like
DECODE(a.value , 2, decode(b.value, 3,(DON'T CALCULATE TAX) ,(CALCULATE TAX)),NULL )
Previous Topic: Oracle Stored Procedures and Powerbuilder
Next Topic: Students in need;
Goto Forum:
  


Current Time: Wed Oct 21 11:52:52 CDT 2020