Home » SQL & PL/SQL » SQL & PL/SQL » CASE STATEMENT help
CASE STATEMENT help [message #292342] Tue, 08 January 2008 11:12 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I need help with a CASE STATEMENT or direction on how to achieve the desired result set.

Create table and insert data:
CREATE TABLE PR (
   NAME		VARCHAR(30),
   PID		NUMBER(4),
   PSEQ		NUMBER(3),
   BRANCH	VARCHAR(15),
   FAC		VARCHAR(3),
   LSEQ		NUMBER(3),
   ORD		NUMBER(1),
   MINORD	NUMBER(1),
   PDATE	DATE,
   MCD		DATE )

INSERT ALL
  INTO PR (NAME, PID, PSEQ, BRANCH, FAC, LSEQ, ORD, MINORD, PDATE, MCD)
    VALUES (SMITH JOHN, 2309, 880, GAINESVILLE, RMC, 845, 2, 2, TO_DATE('12/10/2007 13:00', 'MM/DD/YYYY HH24:MI'), TO_DATE('12/10/2007 15:43', 'MM/DD/YYYY HH24:MI'))
  INTO PR (NAME, PID, PSEQ, BRANCH, FAC, LSEQ, ORD, MINORD, PDATE, MCD)
    VALUES (SMITH JOHN, 2309, 880, GAINESVILLE, RMC, 845, 2, 2, TO_DATE('12/11/2007 14:43', 'MM/DD/YYYY HH24:MI'), TO_DATE('12/10/2007 15:43', 'MM/DD/YYYY HH24:MI'))
  INTO PR (NAME, PID, PSEQ, BRANCH, FAC, LSEQ, ORD, MINORD, PDATE, MCD)
    VALUES (SMITH JOHN, 2309, 880, CARE, RMC, 966, 7, 2, TO_DATE('12/12/2007 15:43', 'MM/DD/YYYY HH24:MI'), TO_DATE('12/10/2007 15:43', 'MM/DD/YYYY HH24:MI'))


If I perform the following:

SELECT * FROM PR

I get this result set


NAME		PID	PSEQ	BRANCH		FAC	LSEQ	ORD	MINORD	PDATE			MCD

SMITH JOHN	2309	880	GAINESVILLE	RMC	845	2	2	12/10/2007 13:00	12/12/2007 15:43
SMITH JOHN	2309	880	GAINESVILLE	RMC	845	2	2	12/11/2007 14:43	12/12/2007 15:43
SMITH JOHN	2309	880	CARE		RMC	966	7	2	12/12/2007 15:43	12/12/2007 15:43



When I add the CASE STATEMENT:

SELECT
   CASE
      WHEN (MIN(ORD) KEEP (DENSE_RANK FIRST ORDER BY ORD) OVER (PARTITION BY pseq) IN (1,2,3,4,5,6) AND pdate <= MCD) AND
           (MIN(ORD) KEEP (DENSE_RANK LAST ORDER BY ORD) OVER (PARTITION BY pseq) = 7  AND pdate = MCD)
      THEN '1'
   END res2,
   NAME,
   PID,
   PSEQ,
   BRANCH,
   FAC,
   LSEQ,
   ORD,
   MINORD,
   PDATE,
   MCD
FROM
   (SELECT
      *
   FROM
      PR)


I get this result set,

RES2	NAME		PID	PSEQ	BRANCH		FAC	LSEQ	ORD	MINORD	PDATE			MCD

NULL	SMITH JOHN	2309	880	GAINESVILLE	RMC	845	2	2	12/10/2007 13:00	12/12/2007 15:43
NULL	SMITH JOHN	2309	880	GAINESVILLE	RMC	845	2	2	12/11/2007 14:43	12/12/2007 15:43
1	SMITH JOHN	2309	880	CARE		RMC	966	7	2	12/12/2007 15:43	12/12/2007 15:43



but expected and would like the result set to be

RES2	NAME		PID	PSEQ	BRANCH		FAC	LSEQ	ORD	MINORD	PDATE			MCD

1	SMITH JOHN	2309	880	GAINESVILLE	RMC	845	2	2	12/10/2007 13:00	12/12/2007 15:43
1	SMITH JOHN	2309	880	GAINESVILLE	RMC	845	2	2	12/11/2007 14:43	12/12/2007 15:43
1	SMITH JOHN	2309	880	CARE		RMC	966	7	2	12/12/2007 15:43	12/12/2007 15:43



How can I achieve this and what is wrong with my CASE STATEMENT?

Any help would be greatly appreciated.

Thanks,
Stan
Re: CASE STATEMENT help [message #292344 is a reply to message #292342] Tue, 08 January 2008 11:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't explain your CASE condition but I bet this an OR instead the AND in CASE.

Regadrs
Michel
Re: CASE STATEMENT help [message #292346 is a reply to message #292344] Tue, 08 January 2008 11:31 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I'm trying to exclude any record/records for the same pseq from the result set where:

(ORD = 7 AND perform_ddt = mcd for that record).

So for the example supplied, I'd like to have a 1 in the res2 column.

OR didn't make a difference.

Thanks,
Stan

[Updated on: Tue, 08 January 2008 11:35]

Report message to a moderator

Re: CASE STATEMENT help [message #292347 is a reply to message #292346] Tue, 08 January 2008 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain in words a clear specification of what you want.
Exclude means where clause not case in select.

By the way, your inline view in FROM clause is useless.

Regards
Michel

Re: CASE STATEMENT help [message #292350 is a reply to message #292347] Tue, 08 January 2008 11:42 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
In other words, if the greatest pdate is associated with ord = 7 then I don't want to report any of the records for that pseq.

I would really like to achieve this with a CASE statement. This is only one of about ten scenarios that I need to filter on.

How do I get the pdate associated with the ord number in
the CASE statement.

[Updated on: Tue, 08 January 2008 12:14]

Report message to a moderator

Re: CASE STATEMENT help [message #292352 is a reply to message #292350] Tue, 08 January 2008 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you can do it with CASE but this is in WHERE clause not in SELECT one whatever you want.

Regards
Michel
Re: CASE STATEMENT help [message #292366 is a reply to message #292352] Tue, 08 January 2008 15:12 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
How do I put a '1' in the RES2 column for all records within a pseq, if one of the records has ord = 7 and that records pdate = mcd.

Can I achieve this with a SELECT CASE statement?

[Updated on: Tue, 08 January 2008 16:54]

Report message to a moderator

Re: CASE STATEMENT help [message #292369 is a reply to message #292342] Tue, 08 January 2008 16:49 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Here's one way...

I added another row to verify.
INSERT INTO PR 
 ( NAME
 , PID
 , PSEQ
 , BRANCH
 , FAC
 , LSEQ
 , ORD
 , MINORD
 , PDATE
 , MCD )
VALUES 
 ('SMITH JANE'
 , 2309
 , 881
 , 'GAINESVILLE'
 , 'RMC'
 , 845
 , 2
 , 2
 , TO_DATE('12/10/2007 13:00', 'MM/DD/YYYY HH24:MI')
 , TO_DATE('12/10/2007 15:43', 'MM/DD/YYYY HH24:MI'));

SELECT name
     , CASE WHEN FIRST_VALUE( ord ) 
              OVER( PARTITION BY pseq 
                    ORDER BY DECODE( ord, 7, 1, 2 ) ) = 7
            THEN '1'
       END res2
FROM   PR;

NAME                           R
------------------------------ -
SMITH JOHN                     1
SMITH JOHN                     1
SMITH JOHN                     1
SMITH JANE

By the way, thanks for the INSERT statements, but they do not work. There were several changes that had to be made.

....OOOOPS, forgot to include the CASE....

[Updated on: Tue, 08 January 2008 16:53]

Report message to a moderator

Re: CASE STATEMENT help [message #292370 is a reply to message #292369] Tue, 08 January 2008 16:56 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
cmerry,

Please see my post before you're reply. I left out a key point....

How do I put a '1' in the RES2 column for all records within a pseq, if one of the records has ord = 7 and that records pdate = mcd.

Thanks,
Stan
Re: CASE STATEMENT help [message #292372 is a reply to message #292370] Tue, 08 January 2008 17:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
The following is what you asked for, but not necessarily what you need.

SCOTT@orcl_11g> CREATE TABLE PR (
  2  	NAME	     VARCHAR(30),
  3  	PID	     NUMBER(4),
  4  	PSEQ	     NUMBER(3),
  5  	BRANCH	     VARCHAR(15),
  6  	FAC	     VARCHAR(3),
  7  	LSEQ	     NUMBER(3),
  8  	ORD	     NUMBER(1),
  9  	MINORD	     NUMBER(1),
 10  	PDATE	     DATE,
 11  	MCD	     DATE )
 12  /

Table created.

SCOTT@orcl_11g> -- corrected insert statement (TEST BEFORE YOUR POST!):
SCOTT@orcl_11g> INSERT ALL
  2    INTO PR (NAME, PID, PSEQ, BRANCH, FAC, LSEQ, ORD, MINORD, PDATE, MCD)
  3  	 VALUES ('SMITH JOHN', 2309, 880, 'GAINESVILLE', 'RMC', 845, 2, 2, TO_DATE('12/10/2007 13:00', 'MM/DD/YYYY HH24:MI'), TO_DATE('12/10/2007 15:43', 'MM/DD/YYYY HH24:MI'))
  4    INTO PR (NAME, PID, PSEQ, BRANCH, FAC, LSEQ, ORD, MINORD, PDATE, MCD)
  5  	 VALUES ('SMITH JOHN', 2309, 880, 'GAINESVILLE', 'RMC', 845, 2, 2, TO_DATE('12/11/2007 14:43', 'MM/DD/YYYY HH24:MI'), TO_DATE('12/10/2007 15:43', 'MM/DD/YYYY HH24:MI'))
  6    INTO PR (NAME, PID, PSEQ, BRANCH, FAC, LSEQ, ORD, MINORD, PDATE, MCD)
  7  	 VALUES ('SMITH JOHN', 2309, 880, 'CARE', 'RMC', 966, 7, 2, TO_DATE('12/12/2007 15:43', 'MM/DD/YYYY HH24:MI'), TO_DATE('12/12/2007 15:43', 'MM/DD/YYYY HH24:MI'))
  8  SELECT * FROM DUAL
  9  /

3 rows created.

SCOTT@orcl_11g> COLUMN name   FORMAT A11
SCOTT@orcl_11g> COLUMN branch FORMAT A11
SCOTT@orcl_11g> SELECT * FROM pr
  2  /

NAME               PID       PSEQ BRANCH      FAC       LSEQ        ORD     MINORD PDATE            MCD
----------- ---------- ---------- ----------- --- ---------- ---------- ---------- ---------------- ----------------
SMITH JOHN        2309        880 GAINESVILLE RMC        845          2          2 12/10/2007 13:00 12/10/2007 15:43
SMITH JOHN        2309        880 GAINESVILLE RMC        845          2          2 12/11/2007 14:43 12/10/2007 15:43
SMITH JOHN        2309        880 CARE        RMC        966          7          2 12/12/2007 15:43 12/12/2007 15:43

SCOTT@orcl_11g> SELECT CASE
  2  	      WHEN
  3  		(SELECT COUNT (*)
  4  		 FROM	pr pr2
  5  		 WHERE	pr2.pseq = pr.pseq
  6  		 AND	pr2.ord = 7
  7  		 AND	pr2.pdate = pr2.mcd) > 0
  8  	      THEN 1
  9  	      ELSE 0 -- or whatever value you want
 10  	    END AS res2,
 11  	    pr.*
 12  FROM   pr
 13  /

      RES2 NAME               PID       PSEQ BRANCH      FAC       LSEQ        ORD     MINORD PDATE            MCD
---------- ----------- ---------- ---------- ----------- --- ---------- ---------- ---------- ---------------- ----------------
         1 SMITH JOHN        2309        880 GAINESVILLE RMC        845          2          2 12/10/2007 13:00 12/10/2007 15:43
         1 SMITH JOHN        2309        880 GAINESVILLE RMC        845          2          2 12/11/2007 14:43 12/10/2007 15:43
         1 SMITH JOHN        2309        880 CARE        RMC        966          7          2 12/12/2007 15:43 12/12/2007 15:43

SCOTT@orcl_11g> 
 

Re: CASE STATEMENT help [message #292375 is a reply to message #292372] Tue, 08 January 2008 17:59 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Barbara,

What do I need then if not what I asked for?

I'm really trying to learn hear.

Thanks,
Stan
Re: CASE STATEMENT help [message #292388 is a reply to message #292375] Tue, 08 January 2008 19:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
If the ultimate goal is to exclude those rows from the result set, why take the extra step of assigning 1 to res2, then presumably selecting those rows where res2 <> 1? Why not just use one of the methods below to select just the rows you want? If you need to add additional conditions you can just add AND clauses to the WHERE clause. Also, I am not sure what you went through to obtain the data with ord and minord and such. There may or may not be simpler ways to combine things.

SELECT pr.*
FROM   pr
WHERE  (SELECT COUNT (*)
        FROM   pr pr2
        WHERE  pr2.pseq = pr.pseq
        AND    pr2.ord = 7
        AND    pr2.pdate = pr2.mcd) = 0
/


SELECT pr.*
FROM   pr
WHERE  NOT EXISTS
       (SELECT * 
        FROM   pr pr2
        WHERE  pr2.pseq = pr.pseq
        AND    pr2.ord = 7
        AND    pr2.pdate = pr2.mcd) 
/


Re: CASE STATEMENT help [message #292389 is a reply to message #292388] Tue, 08 January 2008 20:08 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
There are multiple possibilities where a record within pseq has ord = 7 and I might want to keep a different record within the same pseq. For example, if ord = 7 and pdate < mcd then I'd want to keep the record where pdate = mcd no mater what the ord equals (as long as not 7).

I see your point, and I will explore accomplishing such with conditions in the WHERE clause. For now, I just can't wrap my brain around how.

Thanks for the suggestions.

Stan
Re: CASE STATEMENT help [message #292733 is a reply to message #292342] Wed, 09 January 2008 14:54 Go to previous message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
staann56 wrote on Tue, 08 January 2008 14:56

cmerry,

Please see my post before you're reply. I left out a key point....

How do I put a '1' in the RES2 column for all records within a pseq, if one of the records has ord = 7 and that records pdate = mcd.

Thanks,
Stan


SELECT name
     , CASE WHEN FIRST_VALUE( ord )
              OVER( PARTITION BY pseq
                    ORDER BY DECODE( ord, 7, 1, 2 ) ) = 7
             AND MIN( ABS( pdate - mcd ) )
                 OVER( PARTITION BY pseq ) = 0
            THEN '1'
       END res2
FROM   PR;

NAME            R
--------------- -
SMITH JOHN      1
SMITH JOHN      1
SMITH JOHN      1
SMITH JANE


This is one way if you want to return the result of the CASE statement in the output, but Barbara's suggestion of using the WHERE clause is best if you are trying to eliminate rows based on the CASE statement.

Previous Topic: List the flag change
Next Topic: How to do insert an xml with a XML reserved character
Goto Forum:
  


Current Time: Sat Dec 03 20:04:18 CST 2016

Total time taken to generate the page: 0.12303 seconds