Home » SQL & PL/SQL » SQL & PL/SQL » FUNCTION not returns desired result - ?
FUNCTION not returns desired result - ? [message #191688] Thu, 07 September 2006 14:38 Go to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hi guys,

I am trying to figured out how to get the
following output from a table.
I have tried to write a function
but could not get desired results.

Please help.


CREATE TABLE testtab
(SVCCODE              VARCHAR2(10),
  UOM                   VARCHAR2(15),
  HCPC             VARCHAR2(48) NOT NULL,
  MOD_1                 VARCHAR2(2),
  MOD_2                 VARCHAR2(2),
  MOD_3                 VARCHAR2(2),
  MOD_4                 VARCHAR2(2)
)
/



insert into testtab values ('1641','VI','A1111','AA','BB','CC','DD' );
insert into testtab values ('1641','EA','A1111','AA','BB','CC','FF' );
insert into testtab values ('1641','HR','A1111','AA','BB','CC','HH' );
insert into testtab values ('1641','PD','A1111','AA','BB','CC','JJ' );
insert into testtab values ('1642','VI','A1112','AA','BB','CC',null );
insert into testtab values ('1642','EA','A1112','AA','BB','DD',null );
insert into testtab values ('1642','HR','A1112','AA','BB','EE',null );
insert into testtab values ('1642','PD','A1112','AA','BB','FF',null );
insert into testtab values ('1643','VI','A1113','AA','BB',null,null );
insert into testtab values ('1643','EA','A1113','AA','CC',null,null );
insert into testtab values ('1643','HR','A1113','AA','DD',null,null );
insert into testtab values ('1643','PD','A1113','AA','EE',null,null );
insert into testtab values ('1644','VI','A1114','BB',null,null,null );
insert into testtab values ('1644','EA','A1114','CC',null,null,null );
insert into testtab values ('1644','HR','A1114','DD',null,null,null );
insert into testtab values ('1644','PD','A1114','EE',null,null,null );
insert into testtab values ('1645','VI','A1115',null,null,null,null );

commit;


now my data looks like this :


SvcCode	UOM	HCPC	MOD_1	MOD_2	MOD_3	MOD_4
1641	 VI	A1111	AA	BB	CC	DD
1641 	 EA	A1111	AA	BB	CC	FF
1641	 HR	A1111	AA	BB	CC	HH
1641 	 PD	A1111	AA	BB	CC	JJ
1642	 VI	A1112	AA	BB	CC	
1642	 EA	A1112	AA	BB	DD	
1642	 HR	A1112	AA	BB	EE	
1642	 PD	A1112	AA	BB	FF	
1643	 VI	A1113	AA	BB		
1643	 EA	A1113	AA	CC		
1643	 HR	A1113	AA	DD		
1643	 PD	A1113	AA	EE		
1644	 VI	A1114	BB			
1644	 EA	A1114	CC			
1644	 HR	A1114	DD			
1644	 PD	A1114	EE			
1645	 VI	A1115				


Desired Results :

I want to create a function where i will sent 
IN parameters (HCPC,MOD_1,MOD_2,MOD_3,MOD_4)
and get returned values ( svccode, uom)

AS IN FOLLOWING MANNER;


						
SENT (parameters)        		RETURNED

HCPC	Mod 1	Mod 2	Mod 3	Mod 4	SvcCode	UOM
A1111	AA	BB	CC	DD	1641	VI
A1111	AA	BB	CC		NULL	NULL
A1111	AA	BB			NULL	NULL
A1111	AA				NULL	NULL
A1111					NULL	NULL
A1112	AA	BB	CC	XX	1642	VI
A1112	AA	BB	CC		1642	VI
A1112	AA	BB			NULL	NULL
A1112	AA				NULL	NULL
A1112					NULL	NULL
A1113	AA	BB	XX	XX	1643	VI
A1113	AA	BB	XX		1643	VI
A1113	AA	BB			1643	VI
A1113	AA				NULL	NULL
A1113					NULL	NULL
A1114	AA	XX	XX	XX	1644	VI
A1114	AA	XX	XX		1644	VI
A1114	AA	XX			1644	VI
A1114	AA				1644	VI
A1114					NULL	NULL
A1115	XX	XX	XX	XX	1645	VI
A1115	XX	XX	XX		1645	VI
A1115	XX	XX			1645	VI
A1115	XX				1645	VI
A1115					1645	VI




Here is what i have tried but not getting the desired results 

 
create or replace function F_testtab
(p_HCPC_CODE IN VARCHAR2,
 p_Mod_1 IN VARCHAR2,
 p_Mod_2 IN VARCHAR2,
 p_Mod_3 IN VARCHAR2,
 p_Mod_4 IN VARCHAR2,
 p_SVCCODE OUT VARCHAR2,
 p_UOM OUT VARCHAR2)
RETURN NUMBER
IS
 v_Rc      NUMBER := 0;
 v_Cont    BOOLEAN;
BEGIN
 IF p_Mod_4 IS NULL THEN
  v_Cont := TRUE;
 ELSE
  BEGIN
   SELECT SvcCode, UOM
   INTO p_SVCCODE, p_UOM
   FROM testtab
   WHERE HCPC_CODE = p_HCPC_CODE
    AND Mod_1 = p_Mod_1
    AND Mod_2 = p_Mod_2
      AND Mod_3 = p_Mod_3
      AND Mod_4 = p_Mod_4;
   v_Cont := FALSE;
  EXCEPTION
   WHEN NO_DATA_FOUND THEN
    v_Cont := TRUE;
   WHEN OTHERS THEN
    v_Rc := SQLCODE;
      RETURN v_Rc;
  END;
 END IF;
 IF v_Cont = TRUE THEN
  IF p_Mod_3 IS NULL THEN
   v_Cont := TRUE;
  ELSE
   BEGIN
    SELECT SvcCode, UOM
    INTO p_SVCCODE, p_UOM
    FROM testtab
    WHERE HCPC_CODE = p_HCPC_CODE
     AND Mod_1 = p_Mod_1
     AND Mod_2 = p_Mod_2
       AND Mod_3 = p_Mod_3
       AND Mod_4 IS NULL;
    v_Cont := FALSE;
   EXCEPTION
    WHEN NO_DATA_FOUND THEN
     v_Cont := TRUE;
    WHEN OTHERS THEN
     v_Rc := SQLCODE;
       RETURN v_Rc;
   END;
  END IF;
 END IF;
 IF v_Cont = TRUE THEN
  IF p_Mod_2 IS NULL THEN
   v_Cont := TRUE;
  ELSE
   BEGIN
    SELECT SVCCODE, UOM
    INTO p_SVCCODE, p_UOM
    FROM testtab
    WHERE HCPC_CODE = p_HCPC_CODE
     AND Mod_1 = p_Mod_1
     AND Mod_2 = p_Mod_2
       AND Mod_3 IS NULL
       AND Mod_4 IS NULL;
    v_Cont := FALSE;
   EXCEPTION
    WHEN NO_DATA_FOUND THEN
     v_Cont := TRUE;
    WHEN OTHERS THEN
     v_Rc := SQLCODE;
       RETURN v_Rc;
   END;
  END IF;
 END IF;
 IF v_Cont = TRUE THEN
  IF p_Mod_1 IS NULL THEN
   v_Cont := TRUE;
  ELSE
   BEGIN
    SELECT SVCCODE, UOM
    INTO p_SVCCODE, p_UOM
    FROM testtab
    WHERE HCPC_CODE = p_HCPC_CODE
     AND Mod_1 = p_Mod_1
     AND Mod_2 IS NULL
       AND Mod_3 IS NULL
       AND Mod_4 IS NULL;
    v_Cont := FALSE;
   EXCEPTION
    WHEN NO_DATA_FOUND THEN
     v_Cont := TRUE;
    WHEN OTHERS THEN
     v_Rc := SQLCODE;
       RETURN v_Rc;
   END;
  END IF;
 END IF;
 IF v_Cont = TRUE THEN
  BEGIN
   SELECT SVCCODE, UOM
   INTO p_SVCCODE, p_UOM
   FROM testtab
   WHERE HCPC_CODE = p_HCPC_CODE
    AND Mod_1 IS NULL
    AND Mod_2 IS NULL
    AND Mod_3 IS NULL
    AND Mod_4 IS NULL;
   v_Cont := FALSE;
  EXCEPTION
   WHEN NO_DATA_FOUND THEN
    NULL;
   WHEN OTHERS THEN
    v_Rc := SQLCODE;
    RETURN v_Rc;
  END;
 END IF;
 RETURN v_Rc;
EXCEPTION
 WHEN OTHERS THEN
  v_Rc := SQLCODE;
  RETURN v_Rc;
END f_testtab;
/



Re: FUNCTION not returns desired result - ? [message #191804 is a reply to message #191688] Fri, 08 September 2006 03:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I thought I had it working but the output for HCPC 'A1114' seems not correct. Can you verify your data, especially about the parameters passed and the expected result? If it is correct I need a little bit more explanation.

MHE
Re: FUNCTION not returns desired result - ? [message #191889 is a reply to message #191804] Fri, 08 September 2006 08:36 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hey Maheer!

Thanks to see your reply

and Sorry Maheer! It was my bad.

Instead of putting This one in my desired output.


HCPC	Mod 1	Mod 2	Mod 3	Mod 4	SvcCode	UOM

A1114	BB	XX	XX	XX	1644	VI
A1114	CC	XX	XX		1644	VI
A1114	DD	XX			1644	VI
A1114	EE				1644	VI
A1114					NULL	NULL



I have put this one which is wrong


HCPC	Mod 1	Mod 2	Mod 3	Mod 4	SvcCode	UOM

A1114	AA	XX	XX	XX	1644	VI
A1114	AA	XX	XX		1644	VI
A1114	AA	XX			1644	VI
A1114	AA				1644	VI
A1114					NULL	NULL



Now since i have changed that and i am getting my
desired reuslts.

So i think i am fine with this function,

but i certainly would like to see your code and
would like to implement your approach to my system,
so please post your reply too.


Thanking you,

I have this test code to test the function


SQL>  declare
  2   i_hcpc_code VARCHAR2(48) := 'A1114';
  3   i_mod_1     VARCHAR2(2)  := 'BB';
  4   i_mod_2     VARCHAR2(2)  := 'XX';
  5   i_mod_3     VARCHAR2(2)  := 'XX';
  6   i_mod_4     VARCHAR2(2)  := 'XX';
  7   o_svc_code  VARCHAR2(10);
  8   o_uom       VARCHAR2(15);
  9   v_rc        NUMBER;
 10  begin
 11   v_rc := f_testtab(i_hcpc_code
 12                         , i_mod_1
 13                         , i_mod_2
 14                         , i_mod_3
 15                         , i_mod_4
 16                         , o_svc_code
 17                         , o_uom);
 18   if v_rc = 0 then
 19    dbms_output.put_line('Service Code: ' ||
 20                            o_svc_code    ||
 21                              ' -- UOM: ' || o_uom);
 22   else
 23    dbms_output.put_line('ERROR!  Return code: ' || v_rc);
 24   end if;
 25* end;
SQL> /
Service Code: 1644 -- UOM: VI

PL/SQL procedure successfully completed.

SQL>




Re: FUNCTION not returns desired result - ? [message #191898 is a reply to message #191889] Fri, 08 September 2006 09:20 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
For scvcode, I had this one in mind:
CREATE OR REPLACE FUNCTION show_svccode( p_hcpc  IN VARCHAR2
                                       , p_mod_1 IN VARCHAR2 DEFAULT NULL
                                       , p_mod_2 IN VARCHAR2 DEFAULT NULL
                                       , p_mod_3 IN VARCHAR2 DEFAULT NULL
                                       , p_mod_4 IN VARCHAR2 DEFAULT NULL
                                       )
RETURN NUMBER
IS
  v_return NUMBER;
BEGIN
  SELECT svccode
  INTO   v_return
  FROM   testtab
  WHERE  NVL(mod_1, '#') = NVL(DECODE(p_mod_1,'XX','#',p_mod_1),'#')
  AND    NVL(mod_2, '#') = NVL(DECODE(p_mod_2,'XX','#',p_mod_2),'#')
  AND    NVL(mod_3, '#') = NVL(DECODE(p_mod_3,'XX','#',p_mod_3),'#')
  AND    NVL(mod_4, '#') = NVL(DECODE(p_mod_4,'XX','#',p_mod_4),'#')
  AND    hcpc            = p_hcpc;
  
  RETURN v_return;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/


Should do the trick as far as I see it.

MHE
Re: FUNCTION not returns desired result - ? [message #191926 is a reply to message #191898] Fri, 08 September 2006 13:29 Go to previous message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Thanks for your code.
but it is not returning two values,
then i modified it to
 CREATE OR REPLACE FUNCTION show_svccode( p_hcpc_CODE  IN VARCHAR2
                                        , p_mod_1 IN VARCHAR2 DEFAULT NULL
                                        , p_mod_2 IN VARCHAR2 DEFAULT NULL
                                        , p_mod_3 IN VARCHAR2 DEFAULT NULL
                                        , p_mod_4 IN VARCHAR2 DEFAULT NULL
                                        , p_Svc_Code  OUT VARCHAR2
                                        , p_UOM       OUT VARCHAR2)

 RETURN NUMBER
 IS
   v_Rc      NUMBER := 0;
  v_Cont    BOOLEAN;

 BEGIN
  IF p_Mod_4 IS NULL THEN

       v_Cont := TRUE;

    ELSE

    BEGIN


   SELECT svccode,uom
   INTO   p_svc_code,p_uom
   FROM   testtab
   WHERE  NVL(mod_1, '#') = NVL(DECODE(p_mod_1,'','#',p_mod_1),'#')
   AND    NVL(mod_2, '#') = NVL(DECODE(p_mod_2,'','#',p_mod_2),'#')
   AND    NVL(mod_3, '#') = NVL(DECODE(p_mod_3,'','#',p_mod_3),'#')
   AND    NVL(mod_4, '#') = NVL(DECODE(p_mod_4,'','#',p_mod_4),'#')
   AND    hcpc_CODE            = p_hcpc_CODE;
   v_Cont := FALSE;

  EXCEPTION

   WHEN NO_DATA_FOUND THEN

    v_Cont := TRUE;

   WHEN OTHERS THEN

    v_Rc := SQLCODE;

      RETURN v_Rc;

  END;

 END IF;
 RETURN v_Rc;

EXCEPTION

 WHEN OTHERS THEN

  v_Rc := SQLCODE;

  RETURN v_Rc;

END show_svccode;
/
Function Created.

-----test it 

SQL>
SQL> ED
Wrote file afiedt.buf

  1      declare
  2       i_hcpc_code VARCHAR2(48) := 'A1111';
  3       i_mod_1     VARCHAR2(2)  := 'AA';
  4       i_mod_2     VARCHAR2(2)  := 'BB';
  5       i_mod_3     VARCHAR2(2)  := 'CC';
  6       i_mod_4     VARCHAR2(2)  := 'DD';
  7       o_svc_code  VARCHAR2(10);
  8       o_uom       VARCHAR2(15);
  9       v_rc        NUMBER;
 10      begin
 11       v_rc := SHOW_SVCCODE(i_hcpc_code
 12                             , i_mod_1
 13                             , i_mod_2
 14                             , i_mod_3
 15                             , i_mod_4
 16                            , o_svc_code
 17                            , o_uom
 18     );
 19       if v_rc = 0 then
 20        dbms_output.put_line('Service Code: ' ||
 21                                o_svc_code    ||
 22                                  ' -- UOM: ' || o_uom);
 23       else
 24        dbms_output.put_line('ERROR!  Return code: ' || v_rc);
 25       end if;
 26*     end;
SQL> /
Service Code: 1641 -- UOM: VI

PL/SQL procedure successfully completed.



It is giving some of my desired results
but my all of them as my original post
function is giving, i might have to add
other where conditions.

Anyway i got my way done through with
my first function.

I was just curious to know if any chance
we can reduce my original function / code size
and get it done the same results with some
kind of real compact code.

Thanks for your input.


[Updated on: Fri, 08 September 2006 14:10]

Report message to a moderator

Previous Topic: merge with concatenation update
Next Topic: Urgent Help needed.....
Goto Forum:
  


Current Time: Fri Dec 02 12:02:19 CST 2016

Total time taken to generate the page: 0.09098 seconds