Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CASE IN THE SELECT 1

Status
Not open for further replies.
cristi22

cristi22

MIS
Aug 17, 2005
155
0
0
US
Hello all -

How do I add the condition to look for Quarter 3 earn in case if the 4th Quarter earn is null (WORK_PERIOD is char 200501….).

Here is my select for Q4 only:

Code: 
SELECT 
	MEMBER_WORK_PERIOD.MEMBER_KEY 
	, MEMBER_WORK_PERIOD.EMPLOYER_NO 

	, SUM ( CASE WHEN WORK_PERIOD IN ( '200510','200511','200512')  
	 and funds_05.fund  NOT in 	('DFH','CAH','66LH','L108H','TOGH')

	THEN QUANTITY END) AS Q4_EARN  

  	, SUM ( CASE WHEN WORK_PERIOD IN ( '200510','200511','200512') and funds_05.fund in
	('DFH','CAH','66LH','L108H','TOGH') THEN (cast (RATE as float)) END ) AS Q4_FLAT_EARN   

	, MEMBER_WORK_PERIOD_FUND.APP 
	, MEMBER_WORK_PERIOD_FUND.FUND 

FROM 	
	MEMBER_WORK_PERIOD_FUND
	, MEMBER_WORK_PERIOD 
	, FUNDS_05 

	WHERE MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS 
	AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY 
	AND  
	MEMBER_WORK_PERIOD_FUND.FUND =   FUNDS_05.fund
	and fund_desc='H'
	and WORK_PERIOD ( '200510','200511','200512') 

[red]---?????( '200507','200508','200509') ???? [/red] 
        
	GROUP BY 
		  MEMBER_WORK_PERIOD.EMPLOYER_NO 
		, MEMBER_WORK_PERIOD.MEMBER_KEY 
		, MEMBER_WORK_PERIOD_FUND.APP 
		, MEMBER_WORK_PERIOD_FUND.FUND
Thanks,
Cristi.
 
Sort by date Sort by votes
Your where clause could contain 3rd an 4th quarter, and then you case statement could sort out the rest:
Code: 
Where ...
and work_period in ('200507','200508','200509','200510','200511','200512')
Code: 
select ...
    , SUM ( CASE WHEN WORK_PERIOD IN ( '200510','200511','200512')  
     and funds_05.fund  NOT in     ('DFH','CAH','66LH','L108H','TOGH')

    THEN QUANTITY END) AS Q4_EARN  

      , SUM ( CASE WHEN WORK_PERIOD IN ( '200510','200511','200512') and funds_05.fund in
    ('DFH','CAH','66LH','L108H','TOGH') THEN (cast (RATE as float)) END ) AS Q4_FLAT_EARN,
    , SUM ( CASE WHEN WORK_PERIOD IN ( '200507','200508','200509')  
     and funds_05.fund  NOT in     ('DFH','CAH','66LH','L108H','TOGH')

    THEN QUANTITY END) AS Q3_EARN  

      , SUM ( CASE WHEN WORK_PERIOD IN ( '200507','200508','200509') and funds_05.fund in
    ('DFH','CAH','66LH','L108H','TOGH') THEN (cast (RATE as float)) END ) AS Q3_FLAT_EARN
 
Upvote 0 Downvote
thanks for your time, but

i need to select all q4 & if there is nothing in q4 than select q3, the above logic will pick both qts...??

i need to incorporate something like this into the select:

when q4 is null than select ... * from .... where q4 is null & q3 is not null
 
Upvote 0 Downvote
Would the following variation do what you want?
Code: 
SELECT 
  MEMBER_KEY 
  , EMPLOYER_NO 
  , COALESCE(Q4_EARN,Q3_EARN)  
  , COALESCE(Q4_FLAT_EARN,Q3_FLAT_EARN)   
  , MEMBER_WORK_PERIOD_FUND.APP 
  , MEMBER_WORK_PERIOD_FUND.FUND 
FROM (
SELECT 
    MEMBER_WORK_PERIOD.MEMBER_KEY 
    , MEMBER_WORK_PERIOD.EMPLOYER_NO 
    , SUM(CASE WHEN WORK_PERIOD IN ( '200510','200511','200512')  
        and funds_05.fund  NOT in ('DFH','CAH','66LH','L108H','TOGH')
        THEN QUANTITY END) AS Q4_EARN,  
    , SUM(CASE WHEN WORK_PERIOD IN ( '200510','200511','200512') and funds_05.fund in
      ('DFH','CAH','66LH','L108H','TOGH') 
      THEN (cast (RATE as float)) END ) AS Q4_FLAT_EARN   
    , SUM(CASE WHEN WORK_PERIOD IN ( '200507','200508','200509')  
        and funds_05.fund  NOT in ('DFH','CAH','66LH','L108H','TOGH')
        THEN QUANTITY END) AS Q3_EARN,  
    , SUM(CASE WHEN WORK_PERIOD IN ( '200507','200508','200509') and funds_05.fund in
      ('DFH','CAH','66LH','L108H','TOGH') 
      THEN (cast (RATE as float)) END ) AS Q3_FLAT_EARN   
    , MEMBER_WORK_PERIOD_FUND.APP 
    , MEMBER_WORK_PERIOD_FUND.FUND 
FROM     
    MEMBER_WORK_PERIOD_FUND
    , MEMBER_WORK_PERIOD 
    , FUNDS_05 
WHERE MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS 
    AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY 
    AND  
    MEMBER_WORK_PERIOD_FUND.FUND =   FUNDS_05.fund
    and fund_desc='H'
    and WORK_PERIOD in ( '200507','200508','200509','200510','200511','200512') 
GROUP BY 
    MEMBER_WORK_PERIOD.EMPLOYER_NO 
  , MEMBER_WORK_PERIOD.MEMBER_KEY 
  , MEMBER_WORK_PERIOD_FUND.APP 
  , MEMBER_WORK_PERIOD_FUND.FUND) a
 
Upvote 0 Downvote
no, i need something like this incorporated in the select
Code: 
SELECT a.member_key, a.q4_earn,
(case when a.q4_earn  is null then a.Q3_EARN else a.q4_earn end ) as q4
 FROM HEALTH_GRP a left outer join HEALTH_GRP b
on b.q4_earn is null and b.Q3_EARN is not null
and a.q4_earn is not null 
where a.fund not in ('DFH','CAH','66LH','L108H','TOGH')

union

SELECT a.member_key, a.q4_flat_earn
,(case when a.q4_flat_earn  is null then a.Q3_flat_EARN else a.q4_earn end ) as q4_flat
 FROM HEALTH_GRP a left outer join HEALTH_GRP b
on b.q4_flat_earn is null and b.Q3_flat_EARN is not null
and a.q4_flat_earn is not null 
where a.fund  in ('DFH','CAH','66LH','L108H','TOGH')
 
Upvote 0 Downvote
Alternatively you could use a union and a not exists clause:
Code: 
select ...
from ...
Where ...
  and work_period in ('200510','200511','200512')

Union

select ...
from ...
Where ...
  and work_period in ('200507','200508,'200509;)
  and not exists (
    select 1 
    from ...
    where ...
      and work_period in ('200510','200511','200512'))
The from clause and where clause in the "not exists" sub-query should match the from and where clause from the first query in the union.
 
Upvote 0 Downvote
I posted my last suggestion before I saw your response. A timing thing.
 
Upvote 0 Downvote
THANKS FOR YOUR help, looks a bit complex i'll try
 
Upvote 0 Downvote
i don't think i've got it right.....
Code: 
SELECT 
MEMBER_WORK_PERIOD.MEMBER_KEY 
, MEMBER_WORK_PERIOD.EMPLOYER_NO 

, SUM ( CASE WHEN WORK_PERIOD IN ( '200510','200511','200512')   and funds_05.fund  NOT in  
('DFH','CAH','66LH','L108H','TOGH','LWH') THEN QUANTITY END ) AS Q4_EARN  

, SUM ( CASE WHEN WORK_PERIOD IN ( '200510','200511','200512') and funds_05.fund in
('DFH','CAH','66LH','L108H','TOGH','LWH') THEN cast (RATE as float) END ) AS Q4_FLAT_EARN   
	, MEMBER_WORK_PERIOD_FUND.APP 
	, MEMBER_WORK_PERIOD_FUND.FUND 

FROM 	
	MEMBER_WORK_PERIOD_FUND
	, MEMBER_WORK_PERIOD 
	, FUNDS_05 

	WHERE MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS 
	AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY 
	AND  
	MEMBER_WORK_PERIOD_FUND.FUND =   FUNDS_05.fund
	and fund_desc='H'
	and  WORK_PERIOD  in ('200510','200511','200512') 
		
 AND MEMBER_WORK_PERIOD.MEMBER_KEY NOT IN (select SSN from TEMP_MEM_STS_DE)

        
	GROUP BY 
		  MEMBER_WORK_PERIOD.EMPLOYER_NO 
		, MEMBER_WORK_PERIOD.MEMBER_KEY 
		, MEMBER_WORK_PERIOD_FUND.APP 
		, MEMBER_WORK_PERIOD_FUND.FUND 

UNION 

SELECT 
	MEMBER_WORK_PERIOD.MEMBER_KEY 
	, MEMBER_WORK_PERIOD.EMPLOYER_NO 


, SUM ( CASE WHEN WORK_PERIOD IN ( '200507','200508','200509')   and funds_05.fund  NOT in  
('DFH','CAH','66LH','L108H','TOGH') THEN QUANTITY END) AS Q3_EARN  

, SUM ( CASE WHEN WORK_PERIOD IN ('200507','200508','200509') and funds_05.fund in
('DFH','CAH','66LH','L108H','TOGH','LWH') THEN cast (RATE as float) END ) AS Q3_FLAT_EARN   


	, MEMBER_WORK_PERIOD_FUND.APP 
	, MEMBER_WORK_PERIOD_FUND.FUND 

FROM 	
	MEMBER_WORK_PERIOD_FUND
	, MEMBER_WORK_PERIOD 
	, FUNDS_05 

	WHERE MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS 
	AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY 
	AND  
	MEMBER_WORK_PERIOD_FUND.FUND =   FUNDS_05.fund
	and fund_desc='H'
	and  WORK_PERIOD  in ('200507','200508','200509' )
	 AND MEMBER_WORK_PERIOD.MEMBER_KEY NOT IN (select SSN from TEMP_MEM_STS_DE)

and not not exists (
    select 1 
	FROM 	
		MEMBER_WORK_PERIOD_FUND
		, MEMBER_WORK_PERIOD 
		, FUNDS_05 

	WHERE MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS 
	AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY 
	AND  
	MEMBER_WORK_PERIOD_FUND.FUND =   FUNDS_05.fund
	and fund_desc='H'
	and  WORK_PERIOD  in ('200510','200511','200512') 

        
	GROUP BY 
		  MEMBER_WORK_PERIOD.EMPLOYER_NO 
		, MEMBER_WORK_PERIOD.MEMBER_KEY 
		, MEMBER_WORK_PERIOD_FUND.APP 
		, MEMBER_WORK_PERIOD_FUND.FUND
 
Upvote 0 Downvote
That looks like what I had in mind. What happens when you try it?
 
Upvote 0 Downvote
Expected tokens may include: "BETWEEN". SQLSTATE=42601

SQL0104N An unexpected token "exists" was found following "_STS_DE) and not not". Expected tokens may include: "BETWEEN ".
....
 
Upvote 0 Downvote
replace "and not not exists (" with "and not exists". You had an extra not.
 
Upvote 0 Downvote
ah, thanks a lot, but still not running it
Code: 
SELECT 
MEMBER_WORK_PERIOD.MEMBER_KEY 
, MEMBER_WORK_PERIOD.EMPLOYER_NO 

, SUM ( CASE WHEN WORK_PERIOD IN ( '200510','200511','200512')   and funds_05.fund  NOT in  
('DFH','CAH','66LH','L108H','TOGH','LWH') THEN QUANTITY END ) AS Q4_EARN  

, SUM ( CASE WHEN WORK_PERIOD IN ( '200510','200511','200512') and funds_05.fund in
('DFH','CAH','66LH','L108H','TOGH','LWH') THEN cast (RATE as float) END ) AS Q4_FLAT_EARN   
    , MEMBER_WORK_PERIOD_FUND.APP 
    , MEMBER_WORK_PERIOD_FUND.FUND 

FROM     
    MEMBER_WORK_PERIOD_FUND
    , MEMBER_WORK_PERIOD 
    , FUNDS_05 

    WHERE MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS 
    AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY 
    AND  
    MEMBER_WORK_PERIOD_FUND.FUND =   FUNDS_05.fund
    and fund_desc='H'
    and  WORK_PERIOD  in ('200510','200511','200512') 
        
 AND MEMBER_WORK_PERIOD.MEMBER_KEY NOT IN (select SSN from TEMP_MEM_STS_DE)

        
    GROUP BY 
          MEMBER_WORK_PERIOD.EMPLOYER_NO 
        , MEMBER_WORK_PERIOD.MEMBER_KEY 
        , MEMBER_WORK_PERIOD_FUND.APP 
        , MEMBER_WORK_PERIOD_FUND.FUND 

UNION 

SELECT 
    MEMBER_WORK_PERIOD.MEMBER_KEY 
    , MEMBER_WORK_PERIOD.EMPLOYER_NO 


, SUM ( CASE WHEN WORK_PERIOD IN ( '200507','200508','200509')   and funds_05.fund  NOT in  
('DFH','CAH','66LH','L108H','TOGH') THEN QUANTITY END) AS Q3_EARN  

, SUM ( CASE WHEN WORK_PERIOD IN ('200507','200508','200509') and funds_05.fund in
('DFH','CAH','66LH','L108H','TOGH','LWH') THEN cast (RATE as float) END ) AS Q3_FLAT_EARN   


    , MEMBER_WORK_PERIOD_FUND.APP 
    , MEMBER_WORK_PERIOD_FUND.FUND 

FROM     
    MEMBER_WORK_PERIOD_FUND
    , MEMBER_WORK_PERIOD 
    , FUNDS_05 

    WHERE MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS 
    AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY 
    AND  
    MEMBER_WORK_PERIOD_FUND.FUND =   FUNDS_05.fund
    and fund_desc='H'
    and  WORK_PERIOD  in ('200507','200508','200509' )
     AND MEMBER_WORK_PERIOD.MEMBER_KEY NOT IN (select SSN from TEMP_MEM_STS_DE)

and not  exists (
    select 1 
    FROM     
        MEMBER_WORK_PERIOD_FUND
        , MEMBER_WORK_PERIOD 
        , FUNDS_05 

    WHERE MEMBER_WORK_PERIOD_FUND.MVPOS=MEMBER_WORK_PERIOD.MVPOS 
    AND MEMBER_WORK_PERIOD.MEMBER_KEY=MEMBER_WORK_PERIOD_FUND.MEMBER_KEY 
    AND  
    MEMBER_WORK_PERIOD_FUND.FUND =   FUNDS_05.fund
    and fund_desc='H'
    and  WORK_PERIOD  in ('200510','200511','200512') 

        
    GROUP BY 
          MEMBER_WORK_PERIOD.EMPLOYER_NO 
        , MEMBER_WORK_PERIOD.MEMBER_KEY 
        , MEMBER_WORK_PERIOD_FUND.APP 
        , MEMBER_WORK_PERIOD_FUND.FUND

An unexpected token "END-OF-STATEMENT" was found following
"ORK_PERIOD_FUND.FUND". Expected tokens may include: ")". SQLSTATE=42601
 
Upvote 0 Downvote
You are also missing a 'close bracket' at the end of your last not exists.

and WORK_PERIOD in ('200510','200511','200512'))
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gdg1976
  • Locked
  • Question
db2 sql extract double rows (different in case sensitive) 1
Replies
6
Views
95
mikrom
mikrom
cloudtwter
  • Locked
  • Question
CASE DATE
Replies
5
Views
55
cloudtwter
cloudtwter
pratimsingha
  • Locked
  • Question
Alternative of Select count(*) from TABLE
Replies
0
Views
45
pratimsingha
pratimsingha
gdg1976
  • Locked
  • Question
extract rows with characters in char field
Replies
6
Views
102
gdg1976
gdg1976
gdg1976
  • Locked
  • Question
hi all, input TAB1 COL1
Replies
0
Views
46
gdg1976
gdg1976

Part and Inventory Search

Sponsor

Back
Top