Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

a month function

700700Jun 24 2009 — edited Jun 24 2009
Hi,
I want a string 'N^N^N^Y^Y^Y^Y^Y^Y^Y^Y^Y' FOR THE FINANCIAL YEAR that is 01/03/2009-31/03/2010.
i.e if today is month June then the string should be 'N^N^N^Y^Y^Y^Y^Y^Y^Y^Y^Y'
month july then the string should be 'N^N^N^N^Y^Y^Y^Y^Y^Y^Y^Y'
month march 'Y^Y^Y^Y^Y^Y^Y^Y^Y^Y^Y^Y'

I HAVE MADE SOME CODE BUT COULD NOT FILL THE REQUIRED.
CREATE OR REPLACE FUNCTION YEAR_CALC
(
P_nFYS_PK IN NUMBER,
P_nMEM_PK IN NUMBER
) RETURN VARCHAR2
IS
V_cSTART VARCHAR2(10);
V_cEND VARCHAR2(10);
V_cSYS_DATE VARCHAR2(10);
V_cOUT VARCHAR2(25);
BEGIN
SELECT TO_CHAR(FYS_START_DATE,'DD/MM/RRRR'),TO_CHAR(FYS_END_DATE,'DD/MM/RRRR') INTO V_cSTART,V_cEND FROM FA_FIN_YEAR_STATUS WHERE FYS_PK = P_nFYS_PK AND FYS_MEMBER_PK = P_nMEM_PK;
SELECT TO_CHAR(SYSDATE,'DD/MM/RRRR') INTO V_cSYS_DATE FROM DUAL;
FOR I IN 1..12
LOOP
IF TO_DATE(V_cSTART,'DD/MM/RRRR') < TO_DATE(V_cSYS_DATE,'DD/MM/RRRR')
THEN
V_cOUT :=V_cOUT||'N'||'^';
SELECT ADD_MONTHS(TO_DATE(V_cSTART,'DD/MM/RRRR'),1) INTO V_cSTART FROM DUAL;
ELSE
DBMS_OUTPUT.PUT_LINE(V_cSTART);
V_cOUT :=V_cOUT||'Y'||'^';
SELECT ADD_MONTHS(TO_DATE(V_cSTART,'DD/MM/RRRR'),1) INTO V_cSTART FROM DUAL;
END IF;
END LOOP;
RETURN(TRIM(V_cOUT));
END YEAR_CALC;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 22 2009
Added on Jun 24 2009
3 comments
547 views