OraFAQ Forum: SQL & PL/SQL » update required

Home » SQL & PL/SQL » SQL & PL/SQL » update required (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

update required [message #416165]

Thu, 30 July 2009 23:38

rajasekhar857
Messages: 500
Registered: December 2008

Senior Member

hi iam two tables emrtpbilleditems and emrappointmentdetails

1)tp_bill_number and appointment_ids are the columns in emrtpbilleditems table.

2)bill_number and appointment_id are the columns available in
emrappointmentdetails table

now i have to update the tp_bill_number values into bill_number column

but appointmnet_ids column values are like 201,202,203 and is having relationship to column appointment_id in emrappointmentdetails table.

we need to fetch and parse based on the appointment_ids values and have to update the bill_bumber column.can you help me out in this

i need output to be

appointment_id bill_number
201 12
202 12
203 12.

[Updated on: Thu, 30 July 2009 23:56] by Moderator

Report message to a moderator

Re: update required [message #416166 is a reply to message #416165]

Thu, 30 July 2009 23:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Report message to a moderator

Re: update required [message #416169 is a reply to message #416166]

Thu, 30 July 2009 23:51

rajasekhar857
Messages: 500
Registered: December 2008

Senior Member

my data in both tables is like this

select * from emrappointmentdetails


appointment_id patient_id bill_number
8021343 805800 NULL
8021372 805802 NULL
8021377 805793 NULL
8021378 805793 NULL
8021379 805793 NULL
8021381 805792 2050000333
8021382 805792 2050000333
8021383 805792 2050000333
8021384 805792 2050000333
8021385 805792 2050000333

select * from emrtpbilleditems

tp_id app_id appointment_ids tp_bill_number
803937 805794 NULL 2010000770
803938 805795 NULL 2010000770
803939 805796 NULL 2010000770
803940 NULL 805834,805835,805836 2070000167
803941 805800 NULL 2070000168
803942 NULL 805803,805804,805805,805806 2010000772
803943 805811 NULL 2070000169
803944 805812 NULL 2070000169
803945 805813 NULL 2070000169
803946 804302 NULL 2070000170
803947 805819 NULL 2070000171
803948 805817 NULL 2010000773
803949 805802 NULL 2010000774
803950 805797 NULL 2010000775
803951 805798 NULL 2010000776
803952 805801 NULL 2010000777
803953 805799 NULL 2010000778
803954 NULL 805841,805842,805843,805844,805845,805846,805847,805848,805849,805850,805851 2070000172

Report message to a moderator

Re: update required [message #416170 is a reply to message #416165]

Thu, 30 July 2009 23:57

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.

Regards
Michel

Report message to a moderator

Re: update required [message #416179 is a reply to message #416170]

Fri, 31 July 2009 00:28

rajasekhar857
Messages: 500
Registered: December 2008

Senior Member

hi iam not having data as any how i managed to some extent

CREATE TABLE  EMRAppointmentDetails  (
	 APPOINTMENT_ID			NUMBER(20, 0)	NOT NULL ,
	 PATIENT_ID			VARCHAR2(20)	NOT NULL ,
	 BILL_NUMBER                    VARCHAR(50)      NULL
)   
/

CREATE TABLE EMRTPBilledItems (
	BILL_ITEM_ID		NUMBER(20, 0) NOT NULL,	
	APPOINTMENT_ID		NUMBER(20, 0),	
	APPOINTMENT_IDS         VARCHAR(100),
	TP_BILL_NUMBER          VARCHAR(50)

)
/

INSERT INTO EMRAppointmentDetails VALUES(2363,'3145',NULL)
/
INSERT INTO EMRAppointmentDetails VALUES(2404,'3145',NULL)
/
INSERT INTO EMRAppointmentDetails VALUES(8021493,'805754','2050000315')
/
INSERT INTO EMRAppointmentDetails VALUES(8021494,'805754','2050000315')
/

INSERT INTO EMRTPBilledItems VALUES (801501,NULL,NULL,'2030000004')
/
INSERT INTO EMRTPBilledItems VALUES (803954,NULL,'805841,805842,805843,805844,805845,805846,805847,805848,805849,805850,805851','2070000172')
/

Report message to a moderator

Re: update required [message #416183 is a reply to message #416179]

Fri, 31 July 2009 00:45

bonker
Messages: 402
Registered: July 2005

Senior Member

You already had received an answer for a similar type of question of yours here http://www.orafaq.com/forum/t/148454/0/

I am just giving you initial query to help you get started.


SQL>select * from t_dumm;

        ID APPOINTMENTS
---------- ------------------------------
         1 10,20,40,50
         2 100,200
         3 990

3 rows selected.

SQL>

SQL>SELECT distinct id , token
  2  FROM (SELECT Trim(Substr(txt,Instr(txt,',',1,LEVEL) + 1,
  3     Instr(txt,',',1,LEVEL + 1) - Instr(txt,',',1,LEVEL) - 1)) AS token ,
  4     id
  5  FROM (SELECT ','
  6  ||in_string
  7  ||',' AS txt,id
  8  FROM dual, (select appointments in_string,id from t_dumm))
  9  CONNECT BY LEVEL <= Length(txt) - Length(Replace(txt,',','')) - 1)
 10  order by id
 11  /

        ID TOKEN
---------- ------------------------------
         1 10
         1 20
         1 40
         1 50
         2 100
         2 200
         3 990

7 rows selected.

SQL>

Hope this helps.

Report message to a moderator

Previous Topic:	Need Help for better Performance of the Package
Next Topic:	Archiving of Quote Data

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Feb 07 09:36:44 CST 2025