Home » SQL & PL/SQL » SQL & PL/SQL » problem with Decode cross tab
problem with Decode cross tab [message #220139] Mon, 19 February 2007 01:42 Go to next message
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
Re: problem with Decode cross tab [message #220155 is a reply to message #220139] Mon, 19 February 2007 04:29 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
I hope you have heard about CASE and DECODE.

By
Vamsi
Re: problem with Decode cross tab [message #220159 is a reply to message #220139] Mon, 19 February 2007 04:44 Go to previous message
yogeshse
Messages: 11
Registered: December 2005
Location: Chennai
Junior Member
you can apply Decode func again on result of SUM function in the query.
Previous Topic: how to retrive password of "system" user
Next Topic: How to get a maximum value from multiple columns
Goto Forum:
  


Current Time: Tue Dec 03 11:40:38 CST 2024