Home » SQL & PL/SQL » SQL & PL/SQL » Group By Case
Group By Case [message #303058] Wed, 27 February 2008 18:25 Go to next message
TimJF
Messages: 11
Registered: August 2007
Junior Member
Hi,

I occasionally use statements that contain both CASE and GROUP BY, however, I'm never been able to GROUP BY a CASE statement that contains a subquery.

For the sake of an example, what might the GROUP BY look like for the following select?
select district_code
      ,pos_desc
      ,t3.employee_number
      ,first_name
      ,second_name
      ,surname 
      ,occup_group_desc
      ,CASE WHEN EXISTS(
select 'x' 
from cyborg.x_pay_suspend_lar ps 
where ps.control_1 = t3.control_1
 and ps.control_2 = t3.control_2
 and ps.employee_number = t3.employee_number
 and xpr_susp_strt_dte < sysdate) 
       THEN 'True'
       ELSE 'False'  
       END AS PAY_SUSPEND

Tim.

[Updated on: Wed, 27 February 2008 23:54] by Moderator

Report message to a moderator

Re: Group By Case [message #303099 is a reply to message #303058] Thu, 28 February 2008 00:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use NVL or NVL2 instead of CASE WHEN EXISTS ELSE.

Regards
Michel

[Updated on: Thu, 28 February 2008 00:26]

Report message to a moderator

Re: Group By Case [message #303104 is a reply to message #303058] Thu, 28 February 2008 00:17 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

\First of all the syntax you are using is incorrect.

I suggest you do use

Correlated subquery + NVL Combination.

 Select x,
         Y,
         Z,
         NVL((select 'TRUE'
              From  Tab2 t2
              Where t2.x=t1.x),'FALSE) FLAG
  from tab1 t1;


Thumbs Up
Rajuvan.
Re: Group By Case [message #303110 is a reply to message #303104] Thu, 28 February 2008 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Back to spoonfeeding, doesn't it? ./fa/3943/0/

Regards
Michel
Re: Group By Case [message #303117 is a reply to message #303058] Thu, 28 February 2008 00:58 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Not exactly, I have pasted some Psudocode kind stuff to make OP to know what he need to know . I felt OP is really confused on the Concept.

Hmhm .. Not proper smiley
In smiley , It Spoonfeeds itself.
Am I spoonfeeding myself. ? Smile

Thumbs Up
Rajuvan.
Re: Group By Case [message #303302 is a reply to message #303058] Thu, 28 February 2008 16:18 Go to previous message
TimJF
Messages: 11
Registered: August 2007
Junior Member
Thanks for the replies guys! All help is appreciated.

Previous Topic: Spliting string to column!
Next Topic: Foreign key constraint
Goto Forum:
  


Current Time: Mon Dec 05 11:19:32 CST 2016

Total time taken to generate the page: 0.14786 seconds