Home » SQL & PL/SQL » SQL & PL/SQL » stored procedure (oracle 10g)
stored procedure [message #405223] Wed, 27 May 2009 01:54 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi i am having some tables like emrscreenimmunlkup where screen-immun_id is one column statring from 1501
select * from EMRSCREENIMMUNLKUP where group_id = 0 order by screen_immun_id

having from 1501 to 1568 will be made as 01 to 68

select * from EMRSCREENIMMUNLKUP where group_id > 0 order by screen_immun_id

having from 1591,1592,1595,1600,1604---- 1800 will be made as 10,000,10,001----------------upto maximum.

one table i am having like
select * from emrids where property_name = 'SCREEN_IMMUN_ID'
where value earlier is 1729 now should be updated as maximum from
emrscreenimmunlkup table.how can i do this with a procedure.help me out in this.
Re: stored procedure [message #405227 is a reply to message #405223] Wed, 27 May 2009 02:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please reread your post.
Reread it again.

Does it still make sense?
242 posts should have taught you better how to post.
Re: stored procedure [message #405228 is a reply to message #405223] Wed, 27 May 2009 02:10 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@rajasekhar857,

It is decided "Unless and Untill you send the DDL ,DML , What have you tried so far with Formatting", nobody will ever give u a solution.
Re: stored procedure [message #405231 is a reply to message #405228] Wed, 27 May 2009 02:19 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
select * from emrscreenimmunlkup
select * from EMRSCREENIMMUNLKUP where group_id = 0 order by screen_immun_id

1501	S	1	0	Prostrate Health	=	40	Y						0					0	1		CDC	
1502	S	1	0	Colorectal	1	50	Y						0					0	1		CDC	
1503	S	1	0	Heart Health	=	20	Y						0					0	1		CDC	
1504	S	1	0	Diabetes	=	45	Y						0					0	1		CDC	
1505	S	1	0	Ear Health	1	18	Y				50	Y	0	10	Y			0	1		CDC	
1506	S	1	0	Eye Health	=	40	Y						0					0	1		CDC	
1507	I	1	0	Tetanus Diphtheria Booster	=	18	Y						1	10	Y			0	1	CDC		
1508	I	1	0	Pneumococcal	=	65	Y						0			1		0	1	CDC		
1509	I	1	0	Influenza	=	50	Y						1	1	Y			0	1	CDC		
1510	S	2	0	Thyroid	=	35	Y						1	5	Y			0	1		CDC	
1511	S	2	0	Heart Health	=	20	Y						0					0	1		CDC	
1512	S	2	0	Bone Health	=	40	Y						0					0	1		CDC	
1513	S	2	0	Diabetes	=	45	Y						1	3	Y			0	1		CDC	
1514	S	2	0	Reproductive Health	=	21	Y		8	Y	29	Y	1	1	Y		NORMAL	0	1		CDC	
1515	S	2	0	Reproductive Health	=	21	Y		8	Y	29	Y	1	2	Y		ABNORMAL	0	1		CDC	
1516	S	2	0	Reproductive Health	=	30	Y		40	Y	70	Y	1	3	Y		NORMAL	0	1		CDC	
1517	S	2	0	Reproductive Health	=	30	Y		40	Y	70	Y	1	2	Y		ABNORMAL	0	1		CDC	
1518	S	2	0	Reproductive Health	>	70	Y				100	Y	1	3	Y		NORMAL	0	1		CDC	
1519	S	2	0	Ear Health	=	18	Y				50	Y	1	10	Y			0	1		CDC	
1520	S	2	0	Eye Health	=	40	Y						0					0	1		CDC	
1521	I	2	0	Tetanus Diphtheria Booster	=	18	Y						1	10	Y			0	1	CDC		
1522	I	2	0	Pneumococcal	=	65	Y						0			1		0	1	CDC		
1523	I	2	0	Influenza	=	50	Y						1	1	Y			0	1	CDC		
1524	I	2	0	HPV	=	18	Y				64	Y	0					0	1	CDC		
1525	I	2	0	Meningococcal	=	18	Y				39	Y	0					0	1	CDC		
1526	I	0	0	Hepatitis B 1st Dose	=	1	D						0					0	1	CDC		
1527	I	0	0	Hepatitis B 2nd Dose	=	1	M				2	M	0					0	1	CDC		
1528	I	0	0	Hepatitis B 3rd Dose	=	4	M				5	M	0					0	1	CDC		
1529	I	0	0	Hepatitis B 4th Dose	=	6	M				18	M	0				MONOVALENT	0	1	CDC		
1530	I	0	0	Rotavirus 1st Dose	=	42	D				84	D	0					0	1	CDC		
1531	I	0	0	Rotavirus 2nd Dose	=	91	D				224	D	0					0	1	CDC		
1532	I	0	0	Haemophilus Influenzae Type B 1st Dose	=	2	M				3	M	0					0	1	CDC		
1533	I	0	0	Pneumococcal 1st Dose	=	2	M				3	M	0					0	1	CDC		
1534	I	0	0	Influenza	=	6	M						1	12	M			0	1	CDC		
1535	I	0	0	MMR 1st Dose	=	12	M				15	M	0					0	1	CDC		
1536	I	0	0	MMR 2nd Dose	=	48	M				72	M	0					0	1	CDC		
1537	I	0	0	Varicella Vaccine 1st Dose	=	12	M				15	M	0					0	1	CDC		
1538	I	0	0	Varicella Vaccine 2nd Dose	=	48	M				72	M	0					0	1	CDC		
1539	I	0	0	Hepatitis A 1st Dose	=	12	M				17	M	0					0	1	CDC		
1540	I	0	0	Hepatitis A 2nd Dose	>	18	M				24	M	0					0	1	CDC		
1541	I	0	0	Meningococcal	=	24	M						0					0	1	CDC		
1542	I	0	0	Tdap	=	10	Y				12	Y	0				DTaP COMPLETED	0	1	CDC		
1543	I	0	1	HPV 1st Dose	=	9	Y						0					0	1	CDC		
1544	I	0	1	HPV 2nd Dose	=	9	Y		2	M			0					0	1	CDC		
1545	I	0	1	HPV 3rd Dose	=	9	Y		6	M			0					0	1	CDC		
1546	I	0	2	HPV 1st Dose	=	11	Y						0					0	1	CDC		
1547	I	0	2	HPV 2nd Dose	=	11	Y		2	M			0					0	1	CDC		
1548	I	0	2	HPV 3rd Dose	=	11	Y		6	M			0					0	1	CDC		
1549	I	0	0	DTaP 1st Dose	=	2	M				3	M	0					0	1	CDC		
1550	I	0	0	DTaP 2nd Dose	=	4	M				5	M	0					0	1	CDC		
1551	I	0	0	DTaP 3rd Dose	=	6	M				7	M	0					0	1	CDC		
1552	I	0	0	DTaP 4th Dose	=	15	M				18	M	0					0	1	CDC		
1553	I	0	0	DTaP 5th Dose	=	48	M				72	M	0					0	1	CDC		
1554	I	0	0	Haemophilus Influenzae Type B 2nd Dose	=	4	M				5	M	0					0	1	CDC		
1555	I	0	0	Haemophilus Influenzae Type B 3rd Dose	=	6	M				11	M	0					0	1	CDC		
1556	I	0	0	Haemophilus Influenzae Type B 4th Dose	=	12	M				15	M	0					0	1	CDC		
1557	I	0	0	Haemophilus Influenzae Type B 5th Dose	=	18	M				60	M	0					0	1	CDC		
1558	I	0	0	Pneumococcal 2nd Dose	=	4	M				5	M	0					0	1	CDC		
1559	I	0	0	Pneumococcal 3rd Dose	=	6	M				11	M	0					0	1	CDC		
1560	I	0	0	Pneumococcal 4th Dose	=	12	M				15	M	0					0	1	CDC		
1561	I	0	0	Pneumococcal 5th Dose	=	24	M				72	M	0					0	1	CDC		
1562	I	0	0	Meningococcal	=	11	Y				12	Y	0					0	1	CDC		
1563	S	2	0	Breast Health	=	40	Y						1	1	Y		ABNORMAL	0	1		CDC	
1564	S	2	0	Breast Health	=	40	Y						1	2	Y		NORMAL	0	1		CDC	
1565	I	0	0	IPV 1st Dose	1	2	M				3	M	0					0	1	CDC		
1566	I	0	0	IPV 2nd Dose	1	4	M				5	M	0					0	1	CDC		
1567	I	0	0	IPV 3rd Dose	=	6	M				18	M	0					0	1	CDC		
1568	I	0	0	IPV 4th Dose	=	48	M				72	M	0					0	1	CDC		


select * from EMRSCREENIMMUNLKUP where group_id > 0 order by screen_immun_id

1591	S	0	0	test	>=	0			0		0		0					1502	1			
1592	S	0	0	testings	>=	0			0		0		0					1502	1			
1593	I	0	0	HPV 1st Dose	>=	0			0		0		0					1501	1			
1595	I	0	1	Asha	0	41	Y	12	12	Y	324	Y	1	45	Y	54	45	1515	1			
1596	I	0	2	Anilkumar	0	45	Y	68	32	Y	343	M	1	12	Y	14	32	1515	1			
1597	S	0	1	A	1	5435	Y	3453	4353	M	3453	M	1	3453	Y	5345	34534	1515	1			
1598	S	0	0	bn	>=	0			0		0		0					1502	1			
1599	I	0	0	hh	>=	0			0		0		0					1502	1			
1600	I	0	0	gfhgfh	>=	0			0		0		0					1502	1			
1601	I	0	0	hhhhhhhhhhhh	>=	0			0		0		0					1502	1			
1602	I	0	0	Rotavirus 2nd Dosehghgfhgfh	>=	0			0		0		0					1502	1			
1603	I	0	0	yyy	>=	0			0		0		0					1502	1			
1604	I	0	0	nnn	>=	0			0		0		0					1502	1			
1605	S	0	0	Heart Health	>=	0			0		0		0					1515	1			
1606	S	0	0	x	>=	0			0		0		0					1515	1			
1607	S	0	0	Eye Health	>=	0			0		0		0					1515	1			
1608	S	0	0	Vision Screenin	>=	0			0		0		0					1501	1			
1609	I	0	0	ghgfhgf	>=	0			0		0		0					1502	1			
1610	I	0	0	s	>=	0			0		0		0					1515	1			
1611	S	0	0	dssd	>=	0			0		0		0					1515	1			
1612	I	0	0	asd	>=	0			0		0		0					1515	1			
1613	S	0	0	asd	>=	0			0		0		0					1515	1			
1614	S	0	0	as	>=	0			0		0		0					1515	1			
1615	S	0	0	s	>=	0			0		0		0					1515	1			
1616	S	0	0	Ear Health	>=	0			0		0		0					1515	1			
1617	S	0	0	Diabetes	>=	0			0		0		0					1515	1			
1618	I	0	0	Tetanus Diphtheria Booster	>=	0			0		0		0					1515	1			
1619	I	0	0	HPV 3rd Dose	>=	0			0		0		0					1501	1			
1620	S	0	0	ytryyr	>=	0			0		0		0					1502	1			
1621	S	0	0	hgjgh	>=	0			0		0		0					1502	1			
1622	S	1	1	Aaaaaaa	0	23	Y	5	23	Y	56	Y	0	3	M	4	1	1511	1			
1623	I	1	1	Aaaaaaaa	0	45	M	3	25	M	56	Y	0	2	Y	43	6	1511	1			
1624	I	0	0	HPV 2nd Dose	>=	0			0		0		0					1501	1			
1625	S	0	0	retre	>=	0			0		0		0					1502	1			
1626	S	0	0	ScreeningType1	>=	0			0		0		0					1508	1			
1627	S	0	0	sdfds	>=	0			0		0		0					1508	1			
1628	S	0	0	Screening4	>=	0			0		0		0					1508	1			
1629	S	0	0	Screening1	>=	0			0		0		0					1508	1			
1630	S	0	0	Reproductive Health	>=	0			0		0		0					1517	1			
1631	I	0	0	test rama	>=	0			0		0		0					1502	1			
1632	S	0	0	aaaaaaaaaa	>=	0			0		0		0					1530	1			
1633	S	0	0	aaaaaaaaaaffffffffff	>=	0			0		0		0					1530	1			
1634	S	0	0	sssssssssss	>=	0			0		0		0					1530	1			
1635	S	0	0	ccccccc	>=	0			0		0		0					1530	1			
1636	S	0	0	aaaaaaaaccccccccc	>=	0			0		0		0					1530	1			
1637	S	0	0	trtetr	>=	0			0		0		0					1502	1			
1638	S	0	0	ytu	>=	0			0		0		0					1502	1			
1639	S	0	0	m	>=	0			0		0		0					1502	1			
1640	S	0	0	kl	>=	0			0		0		0					1502	1			
1641	S	0	0	reter	>=	0			0		0		0					1502	1			
1642	S	0	0	testf	>=	0			0		0		0					1502	1			
1643	I	0	1	A	0	2	Y	2	2	Y	2	Y	0	1	Y	1	1	1504	1			
1644	S	0	1	A	0	2	Y	2	2	Y	2	Y	1	2	Y	2	2	1504	1		sada	
1645	I	0	1	A New	0	2	Y						0					1504	1			
1646	I	0	1	A 1	0	3	Y	3		M		Y	0					1504	1			
1647	I	1	2	A2	0	3	Y	3		M		Y	0	5	M	3	3	1504	1			
1648	S	0	1	Aa	0	5	M	5	5	Y	5	Y	0					1504	1			
1649	I	0	1	B New	0	2	Y	2	2	M	3	M	0					1504	1			
1650	I	1	2	B New2	0	7	Y	7	8	M	6	M	0	6	Y	8	8	1504	1			
1651	S	0	1	F Screen	0	3	Y	3	3	Y	3	Y	0					1504	1			
1652	S	0	1	F ScreenNew	1	6	Y	6	6	Y	6	Y	0	6	Y	6	6	1504	1			
1653	S	1	1	A  NEw	0	3	M						0					1504	1			
1654	I	0	1	A3	0	3	Y	6		Y		Y	0	7	Y	7	7	1504	1			
1655	S	0	2	A New New	0	3	Y	5	6	Y	6	Y	1	4	M	5	3	1504	1		asdas	
1656	S	0	0	lipid alert	>=	0			0		0		0					1510	1			
1657	S	0	0	abc	>=	0			0		0		0					1510	1			
1658	S	0	0	lipid screening	>=	0			0		0		0					1510	1			
1659	S	0	0	lipid testing	>=	0			0		0		0					1510	1			
1660	S	0	0	lipid testing2	>=	0			0		0		0					1510	1			
1661	I	0	0	B New2	>=	0			0		0		0					1504	1			
1662	S	0	0	F ScreenNew	>=	0			0		0		0					1504	1			
1663	S	0	0	A  NEw	2	3	Y						0					1504	1			
1664	S	0	0	A	>=	0			0		0		0					1504	1			
1665	S	0	0	Screening by PMH	>=	0			0		0		0					1504	1			
1666	I	0	0	Immun BY PMH	>=	0			0		0		0					1504	1			
1667	S	0	0	Screening by Main	>=	0			0		0		0					1504	1			
1668	S	0	0	Screening by Main1	>=	0			0		0		0					1504	1			
1669	I	0	0	Immun BY Main	>=	0			0		0		0					1504	1			
1670	I	0	0	ipv	>=	0			0		0		0					1501	1			
1701	S	0	0	Lipid Screening Test	>=	0			0		0		0					1501	1			
1702	S	0	0	influenza	>=	0			0		0		0					1509	1			
1703	I	0	0	ggdfgfg	>=	0			0		0		0					1502	1			
1704	I	0	0	gfdgdf	>=	0			0		0		0					1502	1			
1705	I	0	0	fdgfd	>=	0			0		0		0					1502	1			
1706	I	0	0	o	>=	0			0		0		0					1502	1			
1707	I	0	0	hg	>=	0			0		0		0					1502	1			
1708	I	0	0	fg	>=	0			0		0		0					1502	1			
1709	I	0	0	gfdg	>=	0			0		0		0					1502	1			
1710	I	0	0	ggfdgdfg	>=	0			0		0		0					1502	1			
1711	I	0	0	ggg	>=	0			0		0		0					1502	1			
1712	I	0	0	fdg	>=	0			0		0		0					1502	1			
1713	I	0	0	Pneumococcal 7th Dose	>=	0			0		0		0					1502	1			
1714	I	0	0	Immunizations	>=	0			0		0		0					1502	1			
1715	I	0	0	fdsfs	>=	0			0		0		0					1502	1			
1716	I	0	0	ffff	>=	0			0		0		0					1502	1			
1717	I	0	0	uuu	>=	0			0		0		0					1502	1			
1718	S	0	0	A New Screening	0	1	Y						0					1504	1			
1719	S	0	0	Akshtsha	0	2	Y						0					1504	1		2323	
1720	S	0	0	Akshatha	0	2	Y						0					1504	1			
1721	S	0	0	Aks	0	2	M						0					1504	1			
1722	I	0	0	A New Immun	0	3	M						0					1504	1			
1723	S	0	0	New Screening	>=	0			0		0		0					1504	1			
1724	I	0	0	new Immun	>=	0			0		0		0					1504	1			
1725	S	0	0	A New New	>=	0			0		0		0					1504	1			
1726	S	0	0	ScreeningNew	0	2	Y						0					1504	1		J Comments	
1727	I	0	0	ret	>=	0			0		0		0					1502	1			
1728	I	0	0	gfhghfg	>=	0			0		0		0					1502	1			
1729	S	0	0	fhdhh	>=	0			0		0		0					1502	1	

the first column ids to be changed for 1 to 68 for ist requirement and 10,000 to--- for second 

and the third one		
Re: stored procedure [message #405234 is a reply to message #405223] Wed, 27 May 2009 02:21 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

The Post that u posted just now, Does it have any of the following

Quote:
DDL ,DML , What have you tried so far with Formatting


Its just a copy + paste from somewhere !!
Re: stored procedure [message #405236 is a reply to message #405234] Wed, 27 May 2009 02:35 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
ok can you give suggestion like the earleier column i wants to change as 1 from onwards and 10,000 from for second i want a procedure to do so.explain me how can i do so?
Re: stored procedure [message #405238 is a reply to message #405236] Wed, 27 May 2009 02:48 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
rajasekhar857 wrote on Wed, 27 May 2009 09:35
the earleier column i wants to change as 1 from onwards and 10,000 from for second


What does that even mean? It makes no sense at all to me.

Re: stored procedure [message #405317 is a reply to message #405238] Wed, 27 May 2009 06:22 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
 hi iam having table EMRSCREENIMMUNLKUP  where screen_immun_id is one column having values from 1501 to 1700
1) select * from EMRSCREENIMMUNLKUP where group_id = 0 order by screen_immun_id
values from 1501 to 1568 have to be made as 1 to 68
2)select * from EMRSCREENIMMUNLKUP where group_id > 0 order by screen_immun_id
values from 1591 to 1700 have to be made as 10000 to -----
there are four more tables like
EMRPATIENTSCREENINGAUDIT
EMRPATIENTSCREENINGDETAILS
EMRPATIENTIMMUNIZATIONAUDIT
EMRPATIENTIMMUNIZATIONDETAILS

where screening_reference_id which is same as that of column in table EMRSCREENIMMUNLKUP which is having values from 1501 to 1700
same thing wih the way we are doing for above table.
now i want to have a procedure.can you give any example so that it can be helpfull to me.


 
Re: stored procedure [message #405318 is a reply to message #405317] Wed, 27 May 2009 06:23 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You still refuse to post what has been requested of you.
Re: stored procedure [message #405321 is a reply to message #405317] Wed, 27 May 2009 06:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
My best guess about what the OP wants is to map the values of Screen_immum_id to a different range of values, either for display purposes, or for an update.

@rajasekhar857:

Try this:
SELECT screen_immum_id
      ,CASE when screen_immum_id between 1501 and 1568
              then screen_immum_id - 1500
            when screen_immum_id between 1591 and 1700
              then screen_immum_id +10000 - 1591
            else screen_immum_id
            end new_screen_immum_id
FROM   EMRSCREENIMMUNLKUP

Re: stored procedure [message #405326 is a reply to message #405321] Wed, 27 May 2009 06:48 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
yes exactly but i want a procedure to do so for updating not only for EMRSCREENIMMUNLKUP table but also for
EMRPATIENTSCREENINGAUDIT
EMRPATIENTSCREENINGDETAILS
EMRPATIENTIMMUNIZATIONAUDIT
EMRPATIENTIMMUNIZATIONDETAILS tables where column screen-reference_id will be identical for scren_immun_id as that of
EMRSCREENIMMUNLKUP table as i am much aware with procedures in doing so
Re: stored procedure [message #405328 is a reply to message #405326] Wed, 27 May 2009 06:52 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Get right on trhat will you JRowbottom. ASAP (it's probably urgent)
Re: stored procedure [message #405334 is a reply to message #405326] Wed, 27 May 2009 07:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why would you want a procedure to do it?

To update the tables, simply do:
UPDATE <table>
SET sereen_immum_id = CASE when screen_immum_id between 1501 and 1568
                             then screen_immum_id - 1500
                           when screen_immum_id between 1591 and 1700
                             then screen_immum_id +10000 - 1591
                           else screen_immum_id
                           end;


You'll have a barrel of laughs if you've got FK constraints on those columns, but as the table structure is among the many things you've been asked to provide and have decided we don't need, I'm not going to loose any sleep over it.
Re: stored procedure [message #405349 is a reply to message #405334] Wed, 27 May 2009 07:20 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
CAN I WRITE LIKE THIS
CREATE OR REPLACE PROCEDURE VWGroup_Add AS
Begin
        BEGIN
	UPDATE EMRSCREENIMMUNLKUP
        SET sereen_immun_id = CASE when screen_immun_id between 1501 and 1568
                             then screen_immun_id - 1500
                           when screen_immun_id between 1591 and 1700
                             then screen_immun_id +10000 - 1591
                           else screen_immun_id;
			   END;
	UPDATE EMRIDS SET EZEMRXID = COALESCE((SELECT MAX(SCREEN_IMMUN_ID) FROM EMRSCREENIMMUNLKUP),0) WHERE PROPERTY_NAME = 'SCREEN_IMMUN_ID';
  UPDATE EMRPATIENTSCREENINGAUDIT SET SCREENING_REFERENCE_ID =(SELECT screen_immun_id FROM EMRSCREENIMMUNLKUP);
	UPDATE EMRPATIENTSCREENINGDETAILS SET SCREENING_REFERENCE_ID=(SELECT screen_immun_id FROM EMRSCREENIMMUNLKUP);
	UPDATE EMRPATIENTIMMUNIZATIONAUDIT SET IMMUNIZATION_REFERENCE_ID =(SELECT screen_immun_id FROM EMRSCREENIMMUNLKUP);
	UPDATE EMRPATIENTIMMUNIZATIONDETAILS SET IMMUNIZATION_REFERENCE_ID  =(SELECT screen_immun_id FROM EMRSCREENIMMUNLKUP);
	END;
/
Re: stored procedure [message #405354 is a reply to message #405223] Wed, 27 May 2009 07:26 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
I suspect you're missing a lot of where clauses.
Re: stored procedure [message #405358 is a reply to message #405349] Wed, 27 May 2009 07:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could create it as a procedure, but unless you're going to run it more than once, why would you?
Re: stored procedure [message #405363 is a reply to message #405358] Wed, 27 May 2009 07:35 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
is it not possible in a stored procedure really.my superiors are bringing pressure on me to give in aprocedure
Re: stored procedure [message #405366 is a reply to message #405363] Wed, 27 May 2009 07:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
You could create it as a procedure, but unless you're going to run it more than once, why would you?


The code you post is going to fail, but the approach will work.
What you need to do is to ask why they want a procedure? It adds nothing to the process that you couldn't get with an anonymous block.

[typo]

[Updated on: Wed, 27 May 2009 07:40]

Report message to a moderator

Re: stored procedure [message #405367 is a reply to message #405223] Wed, 27 May 2009 07:40 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Putting the code in a stored procedure is easy.
You need to worry about whether or not the updates are actually correct because I doubt they are (lack of where clauses).
Re: stored procedure [message #405369 is a reply to message #405363] Wed, 27 May 2009 07:43 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
rajasekhar857 wrote on Wed, 27 May 2009 08:35
is it not possible in a stored procedure really.my superiors are bringing pressure on me to give in aprocedure


Now go and tell your superiors how to do their job and see how they react.
Re: stored procedure [message #405373 is a reply to message #405369] Wed, 27 May 2009 08:07 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
can you give procedure with cursors for above thing as i am not getting results with my procedure
Re: stored procedure [message #405375 is a reply to message #405373] Wed, 27 May 2009 08:15 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
No, since you still haven't told us what exactly you want, and you still refuse to answer any of our questions for clarification.
Re: stored procedure [message #405376 is a reply to message #405373] Wed, 27 May 2009 08:21 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
rajasekhar857 wrote on Wed, 27 May 2009 14:07
can you give procedure with cursors for above thing as i am not getting results with my procedure

Yes you are. It is all happening correctly. Unless of course you can prove that this is not the case...
Re: stored procedure [message #405377 is a reply to message #405373] Wed, 27 May 2009 08:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Post the code for the procedure that you are using.

'Getting no results' isn't an error message that Oracle generates, so it would be useful if you would cut and paste the screen from SQL*Plus where you run your procedure.
Re: stored procedure [message #405378 is a reply to message #405375] Wed, 27 May 2009 08:22 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Thomas,
i have mentioned everything what i want i can't explain clearly as i did. i want my columns to be updated with 1 to 68 where ever group_id=0 and from 10,000 onward for group_id>0 and the column
screen-immun_id in EMRSCREENIMMUNLKUP is similar to rest of the four tables reference_ids.

thank you
Re: stored procedure [message #405389 is a reply to message #405378] Wed, 27 May 2009 09:05 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That can be done with JRowbottoms query.

[Updated on: Wed, 27 May 2009 09:06]

Report message to a moderator

Re: stored procedure [message #405391 is a reply to message #405389] Wed, 27 May 2009 09:10 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
but what they asre telling like upper limit we don't know some times it won't happen so we need using cursors in procedures only.
Re: stored procedure [message #405399 is a reply to message #405391] Wed, 27 May 2009 09:41 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Still do DDL, still no DML, still no test case, still no clear requirement.

Go to your supervisors and tell them you can't do it. That seems to be the only solution.
Previous Topic: Select * taking lot of time
Next Topic: Exists condition
Goto Forum:
  


Current Time: Sun Dec 04 10:34:50 CST 2016

Total time taken to generate the page: 0.11540 seconds