OraFAQ Forum: SQL & PL/SQL » problem with Decode cross tab

Home » SQL & PL/SQL » SQL & PL/SQL » problem with Decode cross tab

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem with Decode cross tab [message #220139]

Mon, 19 February 2007 01:42

gsanthosh12
Messages: 4
Registered: February 2007

Junior Member

Hi Guys,

I m having a problem with cross tab function.
the code as follows...

CREATE OR REPLACE FUNCTION ARC3(FRMDATE DATE,TODATE DATE) RETURN VARCHAR2 AS
v_sql VARCHAR2(32767);
BEGIN
v_sql := 'select A.FE_NAME NAME';
FOR rec IN (SELECT DISTINCT TRUNC(ARC_FE_ATT_DATE) ARC_FE_ATT_DATE FROM ARC_FE_TRANSACTION WHERE ARC_FE_ATT_DATE BETWEEN FRMDATE AND TODATE) LOOP
v_sql := v_sql
|| ',sum((decode(ARC_FE_ATT_DATE,to_date('''
|| TO_CHAR (rec.ARC_FE_ATT_DATE,'dd-mon-rr')
|| ''', ''dd/mm/rr''),ARC_FE_STATUS))) "'
|| TO_CHAR (rec.ARC_FE_ATT_DATE, 'dd-mon-rr')||'"';
END LOOP;
v_sql := v_sql || ' from ARC_FE_MASTER A,ARC_FE_TRANSACTION B WHERE A.FE_id=B.ARC_FE_NAME GROUP BY A.FE_NAME ORDER BY A.FE_NAME';

RETURN (v_sql);
END ARC3;
/

in the ARC_FE_STATUS column name it returns 0 or 1.i want to decode it to "PRESENT" if val return1.if it it "0" the value is "ABSENT",if it returns null the value must be set to "NA"
can u please modify the code?

Thanks and Regards
Santhosh

Report message to a moderator