Click here to Skip to main content
15,896,154 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
how can i use multiple case when conditions in oracle?
Posted
Comments
John C Rayan 11-Jul-15 7:26am    
what you got so far? can you show your code?
rohith kallingal 13-Jul-15 1:21am    
sql = "with t as\
( SELECT cwd_group_par.id as project\
, cwd_group_par.group_name as project_name\
, case \
when TO_CHAR(chgroup.created,'YYYY-MM-DD') >= '" + str(lastmonth_firstdate) + "' AND TO_CHAR(chgroup.created,'YYYY-MM-DD') <= '" + str(lastmonth_lastdate) + "' then 'LASTMONTH_COUNT'\
when TO_CHAR(chgroup.created,'YYYY-MM-DD') >= '" + str(currentweek_startdate) + "'AND TO_CHAR(chgroup.created,'YYYY-MM-DD') <= '" + str(currentweek_enddate) + "' then 'CURRENTWEEK_COUNT'\
\
end as tm\
, count(distinct i.id) cnt\
from jira.jiraissue i\
join jira.cwd_membership cwd_mem on i.assignee = cwd_mem.lower_child_name\
join jira.cwd_group cwd_group_par on cwd_group_par.lower_group_name = cwd_mem.lower_parent_name\
join jira.issuestatus on i.issuestatus = jira.issuestatus.id\
JOIN jira.PROJECT ON i.PROJECT = jira.PROJECT.ID \
join jira.changegroup chgroup on i.id = chgroup.issueid\
join jira.changeitem chitem on chgroup.id = chitem.groupid \
where cwd_group_par.id in("+str(group_details)+"\
)\
and issuestatus in (6 \
)\
AND jira.PROJECT.PKEY = 'ISD'\
AND chitem.field = 'status' and to_char(chitem.newstring) in ('Closed')\
\
group by cwd_group_par.id, cwd_group_par.group_name\
, case \
when TO_CHAR(chgroup.created,'YYYY-MM-DD') >= '" + str(lastmonth_firstdate) + "' AND TO_CHAR(chgroup.created,'YYYY-MM-DD') <= '" + str(lastmonth_lastdate) + "' then 'LASTMONTH_COUNT'\
when TO_CHAR(chgroup.created,'YYYY-MM-DD') >= '" + str(currentweek_startdate) + "'AND TO_CHAR(chgroup.created,'YYYY-MM-DD') <= '" + str(currentweek_enddate) + "' then 'CURRENTWEEK_COUNT'\
\
end \
)\
select * from t\
pivot\
( sum(cnt) as wk\
for tm in ('LASTMONTH_COUNT','CURRENTWEEK_COUNT')\
)\
order by 1;"
John C Rayan 13-Jul-15 4:30am    
The problem seems to be in your date comparison. You should compare them as dates not as strings.

Change the following as dates and compare then see.
when TO_CHAR(chgroup.created,'YYYY-MM-DD') >= '" + str(lastmonth_firstdate) + "' AND TO_CHAR(chgroup.created,'YYYY-MM-DD') <= '" + str(lastmonth_lastdate) + "' then 'LASTMONTH_COUNT'\
when TO_CHAR(chgroup.created,'YYYY-MM-DD') >= '" + str(currentweek_startdate) + "'AND TO_CHAR(chgroup.created,'YYYY-MM-DD') <= '" + str(currentweek_enddate) + "' then 'CURRENTWEEK_COUNT'\
rohith kallingal 13-Jul-15 5:43am    
yaa i tried it also.. but i think the issue is current week dates are included in lastmonth dates.how can i write current week condition in separate case?
John C Rayan 13-Jul-15 8:01am    
I am not quite sure that I understand you but you want to get week from the dates?
If it is then use 'WW' in to_char. To simplify things can you write the SQL you wanted and let me know what you want to do.

1 solution

did some searching and this is what i found -
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/case_statement.htm[^]

please use the same..
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900