Home » SQL & PL/SQL » SQL & PL/SQL » how to write this mssql query in oracle (oracle 10g)
how to write this mssql query in oracle [message #415834] Wed, 29 July 2009 08:45 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Update emrappointmentdetails

Set bill_number = B.appointment_ids

from emrappointmentdetails A inner join emrtpbilleditems B

on A.appointment_id = B.appointment_id

go



Update emrappointmentdetailshistory

Set bill_number = B.appointment_ids

from emrappointmentdetailshistory A inner join emrtpbilleditems B

on A.appointment_id = B.appointment_id

go
Re: how to write this mssql query in oracle [message #415842 is a reply to message #415834] Wed, 29 July 2009 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#i2067715

Regards
Michel
Re: how to write this mssql query in oracle [message #415843 is a reply to message #415842] Wed, 29 July 2009 09:17 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
how to update comma incuded values to be comma seperated into other table in oracle
Re: how to write this mssql query in oracle [message #415844 is a reply to message #415843] Wed, 29 July 2009 09:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the relation between this question and the first one (but "update" word).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: how to write this mssql query in oracle [message #415845 is a reply to message #415834] Wed, 29 July 2009 09:20 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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.
Re: how to write this mssql query in oracle [message #415847 is a reply to message #415845] Wed, 29 July 2009 09:24 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
8012298
8012298
8012632,8012633,8012634
8012632,8012633,8012634
8012650,8012651,8012653
8012650,8012651,8012653
8012298
8012650,8012651,8012653
8012632,8012633,8012634
2070002338
2070002338
2070002338
2070002338
2070002338
2070002338
2070002338
2070002338
2070002338
2070002338
2070002338
2070002338
2070002338
2070002338
2050000312
2050000312
2050000316
2050000316
2050000316
2050000316
2050000316
2050000316
2050000311
2050000311
2050000311
2050000311
2050000311
2050000311
2050000311
2050000311
8021085
8021085
2050000312
2050000312
2050000312
2050000312
2050000312
2050000312
2070002355
2070002355
2050000311
2050000311
2050000311
2050000311
2050000311
2050000311
2070002355
2070002355
2070002355
2070002355
2070002355
2070002355
2070002355
2070002355
2070002355
2070002355
2070002355
2070002355
2070002374
2070002374
2070002374
2070002374
2070002374
2070002374
2070002374
2070002374
2070002374
2070002374
2070002374
2070002374
2070002374
2070002374
2070002374
2070002374
2070002374
2070002374
2070002374
2070002374
2050000327
2050000327
2050000327
2050000327
2050000327
2050000327
2050000327
2050000327
2050000327




i want my row result as comma seperated not with comma included

i have given my query like this

Update emrappointmentdetails set bill_number = ( select APPOINTMENT_IDS from emrtpbilleditems A

where A.appointment_id = emrappointmentdetails.appointment_id )

/

APPOINTMENT_IDS from emrtpbilleditems is comma included.
now i want to update bill_number from emrappointmentdetails table to be comma seperated
Re: how to write this mssql query in oracle [message #415867 is a reply to message #415847] Wed, 29 July 2009 10:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
To my knowledge, a comma separated string includes commas to separate the values.
Please explain the difference between comma separated and comma included.
Re: how to write this mssql query in oracle [message #415882 is a reply to message #415867] Wed, 29 July 2009 11:15 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi frank,
my requirement is like this

hi,
iam having two tables
a)emrappointmentdetails b)emrtpbilleditems

emrtpbilleditems

billnum ap_ids ap_id--columns
TNOO 80150,80152 12
TN11 80152,80156,80159 11

emrappointmentdetails

ap_id billnum ---columns
80150 TNOO
80152 TNOO
80152 TN11
80156 TN11
80159 TN11

BOTH TABLES ARE HAVING COMMON ap_id as common

now i have to migrate the script like this,
can anyone help me out in this to update emrtpbilleditems to emrappointmentdetailas as the above ones



but initially i used the query like this

Update emrappointmentdetails set bill_number = ( select APPOINTMENT_IDS from emrtpbilleditems A

where A.appointment_id = emrappointmentdetails.appointment_id )

/
but not getting desired reuslts
Re: how to write this mssql query in oracle [message #415888 is a reply to message #415882] Wed, 29 July 2009 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 29 July 2009 16:17
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#i2067715

Regards
Michel


Re: how to write this mssql query in oracle [message #415891 is a reply to message #415888] Wed, 29 July 2009 11:38 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Michel,
can you please provide me an example in such scenarios.
Re: how to write this mssql query in oracle [message #415892 is a reply to message #415891] Wed, 29 July 2009 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#BABGGJCE
I think you are able to search as well as me in:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: how to write this mssql query in oracle [message #415894 is a reply to message #415834] Wed, 29 July 2009 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>but not getting desired reuslts
What exactly are the desired results?
Re: how to write this mssql query in oracle [message #415895 is a reply to message #415891] Wed, 29 July 2009 11:50 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
You should follow the link and read the UPDATE command.

Here's a sample of an update like what you've posted:

CREATE TABLE a( x INT, y INT );

INSERT INTO a VALUES(1,2);
INSERT INTO a VALUES(2,4);

CREATE TABLE b ( x INT, y INT );

INSERT INTO b VALUES( 1, 3 );
INSERT INTO b VALUES(2, 5 );

UPDATE a
SET y = ( SELECT y FROM b WHERE a.x = b.x );


Also post what you want to achieve or what you've tried on the oracle db, any errors you got?
Re: how to write this mssql query in oracle [message #415896 is a reply to message #415894] Wed, 29 July 2009 11:50 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
emrtpbilleditems

billnum ap_ids ap_id--columns
TNOO 80150,80152 12
TN11 80152,80156,80159 11

emrappointmentdetails

ap_id billnum ---columns
80150 TNOO
80152 TNOO
80152 TN11
80156 TN11
80159 TN11


this is thw way iam not getting
Re: how to write this mssql query in oracle [message #415897 is a reply to message #415896] Wed, 29 July 2009 12:30 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
./fa/1600/0/

So you are either after some way to do a pivot or a way to do an update. I still can't figure out what it is.
Re: how to write this mssql query in oracle [message #415898 is a reply to message #415834] Wed, 29 July 2009 12:31 Go to previous message
BlackSwan
Messages: 25036
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.
Previous Topic: how to replace string against some key_value table?
Next Topic: Convert to ANSI from non-standard SQL (merged 7) 10g
Goto Forum:
  


Current Time: Sun Dec 04 00:13:30 CST 2016

Total time taken to generate the page: 0.12243 seconds