Home » SQL & PL/SQL » SQL & PL/SQL » Decode with conditions (Oracle)
Decode with conditions [message #330891] Tue, 01 July 2008 12:42 Go to next message
rachana
Messages: 27
Registered: August 2003
Junior Member
Hi,
I need to write a decode-

my data looks like-
Doc Group_Desc Mod_Date Group_id
212 Approval 2/1/2008 10:11AM 0
212 Approval 2/4/2008 2:00 PM 0
212 Amendment 2/10/2008 4:00AM 1
212 Closeout 3/1/2008 3:00AM 1
114 Approval 2/13/2008 1:00PM 0
114 Cancellation 2/14/2008 2:00AM 1

I want to find the max(Mod_Date) when the Group_desc=Approval, and call it the DT_date.
But, if the Doc also has a Group_desc = Cancellation (eg, 114), I want the DT_date as a blank.

How can I do this in a decode?
Thanks.

[Updated on: Tue, 01 July 2008 12:44]

Report message to a moderator

Re: Decode with conditions [message #330893 is a reply to message #330891] Tue, 01 July 2008 12:44 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Decode with conditions [message #330894 is a reply to message #330891] Tue, 01 July 2008 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Decode with conditions [message #330905 is a reply to message #330891] Tue, 01 July 2008 13:55 Go to previous messageGo to next message
rachana
Messages: 27
Registered: August 2003
Junior Member
The test data is as follows-

CREATE TABLE Doc_jn (
  Doc_Id     NUMBER(4) NOT NULL,
  Group_Id   NUMBER(1),
  Group_desc VARCHAR2(50),
  mod_Date   DATE,
  mod_By     VARCHAR2(50))


INSERT INTO TABLE Doc_jn (
  Doc_Id,  Group_Id , Group_desc,  mod_Date ,  mod_By  
values
'212', '0', 'Approval', '2/1/2008 10:11 AM', 'abc')
INSERT INTO TABLE Doc_jn (
  Doc_Id,  Group_Id , Group_desc,  mod_Date ,  mod_By  
values
'212', '0', 'Approval', '2/4/2008 2:11 AM', 'xyz')
INSERT INTO TABLE Doc_jn (
  Doc_Id,  Group_Id , Group_desc,  mod_Date ,  mod_By  
values
'212', '1', 'Amendment', '2/10/2008 4:11 AM', 'ubc')
INSERT INTO TABLE Doc_jn (
  Doc_Id,  Group_Id , Group_desc,  mod_Date ,  mod_By  
values
'212', '1', 'Closeout', '3/1/2008 3:11 PM', 'abc')
INSERT INTO TABLE Doc_jn (
  Doc_Id,  Group_Id , Group_desc,  mod_Date ,  mod_By  
values
'114', '0', 'Approval', '2/13/2008 1:00 PM', 'xyz')
INSERT INTO TABLE Doc_jn (
  Doc_Id,  Group_Id , Group_desc,  mod_Date ,  mod_By  
values
'114', '1', 'Cancellation', '2/14/2008 2:00 AM', 'xyz')


I want to write a decode that take the max of the mod_Date where the Group_Desc = Approval and call it the DT_Date.
 max(decode(a.Group_Desc, "Approval", a.mod_Date, '1-Jan-1800')) as DT_Date 

Now, if the Doc_Id has a Group_Desc of "Approval" as well as "Cancellation, eg. Doc_Id 114, then I want the DT_Date to be blank. How do I achieve this within a decode?
Re: Decode with conditions [message #330909 is a reply to message #330905] Tue, 01 July 2008 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How do I achieve this within a decode?

The opposite you did it.
In DECODE is Group_Desc is the value you want to ignore then give a lower artificial date, if it is then give the actual date.
In the end, use nullif function to convert the artificial date to null.

Regards
Michel
Re: Decode with conditions [message #330912 is a reply to message #330909] Tue, 01 July 2008 14:13 Go to previous messageGo to next message
rachana
Messages: 27
Registered: August 2003
Junior Member
Michel,
I didnt understand how to look for Group_Desc in the case of Doc_ID 114, where it is both "Approval" and "Cancellation" in the 2 rows. How to derive the DT_Date in this case?

[Updated on: Tue, 01 July 2008 14:13]

Report message to a moderator

Re: Decode with conditions [message #330914 is a reply to message #330912] Tue, 01 July 2008 14:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the result you want with your test case and why.
I don't understand your requirements.
And what happens if there is another row for 114?

Regards
Michel
Re: Decode with conditions [message #330915 is a reply to message #330914] Tue, 01 July 2008 14:42 Go to previous messageGo to next message
rachana
Messages: 27
Registered: August 2003
Junior Member
For Doc_Id 212, the document was first approved. Then it was amended, and finally when work was completed, it was closed (Closeout). So, if I want to find the date on which it was Approved, it is the max(Mod_Date) when the Group_Desc became "Approval". I have this scenario working in my decode statement in the earlier post.

My Problem is with Doc_ID 114. This document was first Approved. But then it was realized that the document should not have been there in the first place. SO, it was cancelled (Cancellation). I do not want to show the Mod_Date/Approved Date if the document has been cancelled.

I am struggling with the decode here, because this Doc_id 114, does have both an approval and a cancellation group_Desc. How do I change the decode to work as intended here?
Re: Decode with conditions [message #330916 is a reply to message #330915] Tue, 01 July 2008 14:51 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select doc_id, 
  2         decode(count(distinct decode(GROUP_DESC,'Approval','A','Cancellation','C')),
  3                2, null,
  4                max(decode(GROUP_DESC,'Approval',mod_date)))
  5  from Doc_jn 
  6  group by doc_id
  7  order by doc_id
  8  /
DOC_ID DECODE(COUN
------ -----------
   114
   212 04-FEB-2008

2 rows selected.

Regards
Michel
Previous Topic: multiple values into one row
Next Topic: how to get unupdated records?
Goto Forum:
  


Current Time: Fri Dec 09 23:34:47 CST 2016

Total time taken to generate the page: 0.06040 seconds