Home » SQL & PL/SQL » SQL & PL/SQL » need in oracle (oracle 10g)
need in oracle [message #425067] Wed, 07 October 2009 06:35 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
SET @CompList = ''
SELECT @CompList = @CompList + GENERIC_NAME + ' ' + STRENGTH + STRENGTHUNIT + ' '
FROM EMRCimsGenericLkup
WHERE EMRCimsGenericLkup.MEDICATIONS_ID = @medicationsID

needde that in oracle please

CREATE FUNCTION [dbo].[medCompositions] (@medicationsID NUMERIC(20, 0))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @CompList varchar(8000)
SET @CompList = ''
SELECT @CompList = @CompList + GENERIC_NAME + ' ' + STRENGTH + STRENGTHUNIT + ' '
FROM EMRCimsGenericLkup
WHERE EMRCimsGenericLkup.MEDICATIONS_ID = @medicationsID
RETURN @CompList               
END
GO



create or replace FUNCTION medCompositions (medicationsID NUMBER)
RETURN varchar2
AS
CompList varchar2(4000);
BEGIN
CompList := '';
SELECT  CompList || GENERIC_NAME || ' ' || STRENGTH || STRENGTHUNIT || ' ' INTO CompList
FROM EMRCimsGenericLkup
WHERE EMRCimsGenericLkup.MEDICATIONS_ID = medicationsID;
RETURN CompList;               
END;


getting oracle exception as
SQL Error: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "CLINICALDEV.MEDCOMPOSITIONS", line 7
01422. 00000 - "exact fetch returns more than requested number of rows"
*Cause: The number specified in exact fetch is less than the rows returned.
*Action: Rewrite the query or change number of rows requested

[Updated on: Wed, 07 October 2009 06:41]

Report message to a moderator

Re: need in oracle [message #425072 is a reply to message #425067] Wed, 07 October 2009 06:53 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

SQL Error: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "CLINICALDEV.MEDCOMPOSITIONS", line 7
01422. 00000 - "exact fetch returns more than requested number of rows"


Your SQL Query returing more than one row. Try to use CURSOR
Re: need in oracle [message #425073 is a reply to message #425072] Wed, 07 October 2009 06:55 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
how can i use that please help me in this regard
Re: need in oracle [message #425074 is a reply to message #425073] Wed, 07 October 2009 07:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
We've all been round this before, so why don't you TRY to help us to help you.

Tell us what this piece of code needs to do!
Re: need in oracle [message #425075 is a reply to message #425073] Wed, 07 October 2009 07:05 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:

exact fetch returns more than requested number of rows


Use Collection

[Updated on: Wed, 07 October 2009 07:08]

Report message to a moderator

Re: need in oracle [message #425079 is a reply to message #425075] Wed, 07 October 2009 07:19 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

http://www.orafaq.com/forum/t/150674/80704/

Okay. Refer this link. Here Mr Kevin writen function using cursor.

Check this code. This is help's you.

Re: need in oracle [message #425080 is a reply to message #425079] Wed, 07 October 2009 07:23 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
create or replace
FUNCTION medExtras RETURN VARCHAR2 AS
	V_MedList VARCHAR2(4000);
	V_MEDICATIONS_ID NUMBER(20, 0);
	V_MEDICATION VARCHAR2(500);
        CURSOR Med_Duplicates IS SELECT DISTINCT MEDICATION FROM EMRCimsMedicationsLkup WHERE PACKING_AVL = 3;
BEGIN
	V_MedList := '';
	 OPEN Med_Duplicates;
	   LOOP
	     FETCH  Med_Duplicates INTO V_MEDICATION;
	       EXIT WHEN Med_Duplicates%NOTFOUND;
		     V_MEDICATIONS_ID:= ('SELECT  CML.MEDICATIONS_ID FROM EMRCimsMedicationsLkup CML 
				    INNER JOIN EMRCimsProductDetails CPD ON CPD.CIMS_REF_ID = CML.CIMS_REF_ID
				    WHERE CML.PACKING_AVL = 3 AND MEDICATION = V_MEDICATION
				    ORDER BY MEDICATION ASC, 
				    TO_CHAR(NVL(SUBSTR(CPD.PRODUCT_CODE, 0, INSTR( CPD.PRODUCT_CODE,"~", 0)), 0) AS NUMBER) DESC, 
				    TO_CHAR(NVL(SUBSTR(CPD.PRODUCT_CODE, (INSTR( CPD.PRODUCT_CODE,"~",0)+1), LENGTH(CPD.PRODUCT_CODE)),0) AS NUMBER) DESC');
             
	         V_MedList := V_MedList || TO_CHAR(V_MEDICATIONS_ID ) || ',';
	   END LOOP;
	CLOSE Med_Duplicates;
	V_MedList := V_MedList || '0';
	RETURN V_MedList;               
END;





EXCEPTION AS
SQL Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "CLINICALDEV.MEDEXTRAS", line 12
Re: need in oracle [message #425083 is a reply to message #425080] Wed, 07 October 2009 07:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is just your previous thread come round for another pass - WHY won't you stick to one thread per problem.

You're trying to set the variable v_medications_id to a string value when it's defined as NUMBER(20,0)

I mentioned that you'd have this problem in the previous thread.
Re: need in oracle [message #425299 is a reply to message #425067] Thu, 08 October 2009 08:56 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also this:
INSTR( CPD.PRODUCT_CODE,"~", 0))

should be:
INSTR( CPD.PRODUCT_CODE,''~'', 0))


That's two single quotes next to each other
Re: need in oracle [message #425331 is a reply to message #425067] Thu, 08 October 2009 11:32 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
woohoo! I have been quoted!
Previous Topic: Oracle Package hangs while compiling (2)
Next Topic: ORDER BY & GROUP BY Clauses
Goto Forum:
  


Current Time: Thu Sep 29 12:25:24 CDT 2016

Total time taken to generate the page: 0.15719 seconds