Home » SQL & PL/SQL » SQL & PL/SQL » I need help regarding the CASE statement
I need help regarding the CASE statement [message #295865] Wed, 23 January 2008 15:12 Go to next message
kam123
Messages: 13
Registered: January 2008
Junior Member
Hello Everybody,

Please help me out, I am still learning SQL. I am asked to make changes to the following command which is developed by another developer.

I have to change the last second line of the below command as: If the report is run in Jan 2008 or Feb 2008 or Mar 2008 then it should take the "next_perf_review"(database field) date as Mar 31,2008 and if the report is run after Mar 2008 then it should go take the "next_perf_review"(database field) date as Mar 31,2009.

I have to make a change to this line of code:
AND v.next_perf_review <> TO_DATE ('03/31/'||(to_char(sysdate,'yyyy')+1), 'MM/DD/YYYY')


I greatly appreciate any help.

SQL Command is as follows:

WITH t1 AS
     (
SELECT v.company, v.process_level, v.plevelname,
v.department, v.employee, v.last_name, v.first_name, v.middle_init, v.job_code,
v.job_class,
v.job_code_description as job_code_title,v.position_title,v.eeo_cat, v.sup_lname, 
v.sup_fname, v.sup_middle_init, v.next_perf_review, v.date_hired, v.contract_manager, 
v.group_name
FROM lrsuser.v_hr_employee v
WHERE v.eeo_cat in (10, 12, 14)
AND v.group_name in ('G:ACTIVE','G:INACTIVE')
AND v.job_class <> '50'
AND v.next_perf_review <> TO_DATE ('03/31/'||(to_char(sysdate,'yyyy')+1), 'MM/DD/YYYY')
AND v.date_hired < TO_DATE ('09/01/'||(to_char(sysdate,'yyyy')), 'MM/DD/YYYY') 
AND v.fte_total > 0),


Thanks a lot!!

[MERGED and RENAMED by LF]

[Updated on: Thu, 24 January 2008 01:02] by Moderator

Report message to a moderator

Re: SQL Command Help [message #295868 is a reply to message #295865] Wed, 23 January 2008 15:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
to_char(mydate,'Q') gives the quarter of mydate: 1 for Jan, Feb or Mar.
extract(year for mydate) gives year of mydate.

Regards
Michel
Re: SQL Command Help [message #295877 is a reply to message #295868] Wed, 23 January 2008 15:55 Go to previous messageGo to next message
kam123
Messages: 13
Registered: January 2008
Junior Member
Hi there,

I tried the below code, I am getting an error saying missing keyword. please suggest.

Case 
   When to_char(sysdate,'Q')=1 then  v.next_perf_review='03/31/2008'
else
   v.next_perf_review='03/31/2009'
end


Thank You
Re: SQL Command Help [message #295891 is a reply to message #295868] Wed, 23 January 2008 17:21 Go to previous messageGo to next message
kam123
Messages: 13
Registered: January 2008
Junior Member
Hi there,

I tried the below code and am getting error "missing keyword". Please suggest, I am new to SQL world.

CASE
   WHEN to_char(sysdate,'Q') = 1 THEN  v.next_perf_review = TO_DATE('03/31/2008','MM/DD/YYYY')
ELSE
   v.next_perf_review = TO_DATE('03/31/2009','MM/DD/YYYY')
END)


I greatly appreciate your help.

Thanks!
Case statement not working [message #295898 is a reply to message #295865] Wed, 23 January 2008 20:38 Go to previous messageGo to next message
kam123
Messages: 13
Registered: January 2008
Junior Member
Hi All,

I added the following code to my existing sql command, and I am getting an error as "missing keyword". Please suggest.

AND (CASE
   WHEN to_char(sysdate,'Q') = 1 THEN  v.next_perf_review = TO_DATE('03/31/2008','MM/DD/YYYY')
ELSE
   v.next_perf_review = TO_DATE('03/31/2009','MM/DD/YYYY')
END)

   I greatly appreciate any help.



Thank you
Re: Case statement not working [message #295917 is a reply to message #295898] Wed, 23 January 2008 22:27 Go to previous messageGo to next message
kkinapps
Messages: 29
Registered: November 2006
Location: Hyderabad
Junior Member

Hi,

Use the following case statement instead


AND v.next_perf_review =
          (CASE
              WHEN TO_CHAR (SYSDATE, 'Q') = 1
                 THEN TO_DATE ('03/31/2008', 'MM/DD/YYYY')
              ELSE TO_DATE ('03/31/2009', 'MM/DD/YYYY')
           END
          )



Regards
KK
Re: Case statement not working [message #295924 is a reply to message #295898] Wed, 23 January 2008 23:13 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
SQL> ed
Wrote file afiedt.buf

  1   declare
  2   next_perf_review date;
  3   begin
  4   next_perf_review :=
  5  CASE
  6  to_char(sysdate,'Q')
  7      WHEN 1 then
  8   TO_DATE('03/31/2008','MM/DD/YYYY')
  9   ELSE
 10    TO_DATE('03/31/2009','MM/DD/YYYY')
 11    END;
 12  dbms_output.put_line(next_perf_review);
 13*  end;
SQL> /
31-MAR-08

PL/SQL procedure successfully completed.
Re: Case statement not working [message #295964 is a reply to message #295898] Thu, 24 January 2008 00:58 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is v. in
v.next_perf_review = ...
What are you doing? Updating a 'next_perf_review' column in a 'v' table?


Please, do not open a new topic about the same problem; continue discussion in existing one. I've found out what 'v.' is after merging your topics.

[Updated on: Thu, 24 January 2008 01:04]

Report message to a moderator

Re: Case statement not working [message #296094 is a reply to message #295917] Thu, 24 January 2008 10:22 Go to previous messageGo to next message
kam123
Messages: 13
Registered: January 2008
Junior Member
Hi there,

Thank you so much for your help. My manager want if the report is run in Jan,Feb,or Mar 2008 then it should exclude v.next_perf_review date for 03/31/2008 or else exclude 03/31/2009.

But it should work for any year, I mean she don't want to hardcode it she want it dynamic. I am new to SQL,please help.

Here is my code:

AND v.next_perf_review <>
          (CASE
              WHEN TO_CHAR (SYSDATE, 'Q') = 1
                 THEN TO_DATE ('03/31/2008', 'MM/DD/YYYY')
              ELSE TO_DATE ('03/31/2009', 'MM/DD/YYYY')
           END
          )


Any help is greatly appreciated.

Thanks a lot!
Re: Case statement not working [message #296097 is a reply to message #296094] Thu, 24 January 2008 10:27 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read again my first post.

Regards
Michel
Previous Topic: find views using particular column
Next Topic: Data Migration - Commit Frequency
Goto Forum:
  


Current Time: Tue Dec 06 04:17:19 CST 2016

Total time taken to generate the page: 0.09869 seconds