Home » SQL & PL/SQL » SQL & PL/SQL » CREATING FUNCTION FOR "CASE STATEMENT" (Oracle 10g, Win7)
CREATING FUNCTION FOR "CASE STATEMENT" [message #574199] Mon, 07 January 2013 20:27 Go to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
CREATE TABLE JAN07
      (
        COL1 VARCHAR2(50),
        COL2 VARCHAR2(50),
        COL3 VARCHAR2(50)
      )

      INSERT ALL
      INTO JAN07 VALUES ('100A','{XXX}','888')      
      INTO JAN07 VALUES ('ZZZ','ZZC}','00C')
      INTO JAN07 VALUES ('TRUE','ABJ','TRUE')
      SELECT * FROM DUAL;

I have to create a function for the below case. The "PARAM1" is the columns i.e. COL1,COL2,COL3.

CASE SUBSTR(PARAM1, -1)
		WHEN '{' THEN REPLACE(PARAM1, '{', '0')
		WHEN 'A' THEN REPLACE(PARAM1, 'A', '1')
		WHEN 'B' THEN REPLACE(PARAM1, 'B', '2')
		WHEN 'C' THEN REPLACE(PARAM1, 'C', '3')
		WHEN '}' THEN '-' || REPLACE(PARAM1, '}', '0')
		WHEN 'J' THEN '-' || REPLACE(PARAM1, 'J', '1')
		WHEN 'K' THEN '-' || REPLACE(PARAM1, 'K', '2')
	      ELSE
		PARAM1

When I pass the column name it should provide me the o/p as:
Quote:

1,-0,8
Z,-0,3
E,-1,E


How can I do it?
Re: CREATING FUNCTION FOR "CASE STATEMENT" [message #574200 is a reply to message #574199] Mon, 07 January 2013 20:43 Go to previous messageGo to next message
BlackSwan
Messages: 23035
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

I don't see any FUNCTION.
Is this function specific to table JAN07?
Data does NOT appear to conform to Third Normal Form.
what datatype is PARAM1?
Realize that SQL must be static & valid at compile time.
Re: CREATING FUNCTION FOR "CASE STATEMENT" [message #574201 is a reply to message #574200] Mon, 07 January 2013 20:51 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
@BLACKSWAN PARAM1 is varchar2. I want to implement the case statement in a "function".
Re: CREATING FUNCTION FOR "CASE STATEMENT" [message #574202 is a reply to message #574201] Mon, 07 January 2013 20:55 Go to previous messageGo to next message
BlackSwan
Messages: 23035
Registered: January 2009
Senior Member
http://www.oracle.com/pls/db112/search?remark=quick_search&word=function&partno=

the sooner you start writing your function, the sooner you might have what you desire
Re: CREATING FUNCTION FOR "CASE STATEMENT" [message #574203 is a reply to message #574202] Mon, 07 January 2013 21:21 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
I've created the function but I think the "SUBSTR" is not working in my function. I can not figure out the error.
 
CREATE OR REPLACE FUNCTION rep_string (param1 IN varchar2)
   RETURN VARCHAR2 IS
   BEGIN
        RETURN 
                CASE SUBSTR(PARAM1, -1)
		                  WHEN '{' THEN REPLACE(PARAM1, '{', '0')
		                  WHEN 'A' THEN REPLACE(PARAM1, 'A', '1')
		                  WHEN 'B' THEN REPLACE(PARAM1, 'B', '2')
		                  WHEN 'C' THEN REPLACE(PARAM1, 'C', '3')
		                  WHEN '}' THEN '-' || REPLACE(PARAM1, '}', '0')
		                  WHEN 'J' THEN '-' || REPLACE(PARAM1, 'J', '1')
		                  WHEN 'K' THEN '-' || REPLACE(PARAM1, 'K', '2')
	             ELSE
		                  PARAM1
               END;
    END;
    /
Re: CREATING FUNCTION FOR "CASE STATEMENT" [message #574204 is a reply to message #574203] Mon, 07 January 2013 22:05 Go to previous messageGo to next message
BlackSwan
Messages: 23035
Registered: January 2009
Senior Member
>I've created the function but I think the "SUBSTR" is not working in my function. I can not figure out the error.
ERROR?
What Error?
I don't see any error.


SQL> select rep_string('COL1') from dual;

REP_STRING('COL1')
--------------------------------------------------------------------------------
COL1




Why is it that NOTHING in the function ever references table JAN07?
What is the relationship between the function REP_STRING & table JAN07?

[Updated on: Mon, 07 January 2013 22:22]

Report message to a moderator

Re: CREATING FUNCTION FOR "CASE STATEMENT" [message #574209 is a reply to message #574204] Mon, 07 January 2013 23:53 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

I tried it's working fine.. i didn't find any issue in this function.

select rep_string(COL1) from jan07
1001
ZZZ
TRUE
Re: CREATING FUNCTION FOR "CASE STATEMENT" [message #574216 is a reply to message #574199] Tue, 08 January 2013 01:25 Go to previous message
Michel Cadot
Messages: 59754
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have to create a function for the below case. The "PARAM1" is the columns i.e. COL1,COL2,COL3.


This is silly and can just lead to poor performances.

Regards
Michel
Previous Topic: Oracle Partition - 11g
Next Topic: varchar2 data change 'mmyyyy' to 'yyyymm'
Goto Forum:
  


Current Time: Mon Nov 24 06:29:20 CST 2014

Total time taken to generate the page: 0.12209 seconds