Home » SQL & PL/SQL » SQL & PL/SQL » sql server query to oracle 10g query (oracle 10g)
icon4.gif  sql server query to oracle 10g query [message #332409] Tue, 08 July 2008 08:20 Go to next message
acarella
Messages: 21
Registered: July 2008
Location: Latham, NY
Junior Member
I have three sql server insert queries that need to be written for oracle 10g. Can anyone help? Thank you.

INSERT INTO T_COMP_DATA
(F_Cas_Number, F_Component_Id, F_Data_Code, F_DATA)
SELECT F_Cas_Number, F_Component_Id, 'EUCALL' AS Expr1, F_DATA
FROM T_COMP_DATA
WHERE (F_Data_Code = 'OSCALL') AND (F_DATA = '0.1')

INSERT INTO T_COMP_DATA
(F_Cas_Number, F_Component_Id, F_Data_Code, F_DATA)
SELECT F_Cas_Number, F_Component_Id, 'EUCALL' AS Expr1, F_DATA
FROM T_COMP_DATA
WHERE (F_Data_Code = 'OSCALL') AND (F_DATA = '1')

INSERT INTO T_COMP_DATA
(F_Cas_Number, F_Component_Id, F_Data_Code, F_DATA)
SELECT F_Cas_Number, F_Component_Id, 'EUHAZ' AS Expr1, F_DATA
FROM T_COMP_DATA
WHERE (F_Data_Code = 'NAHAZ') AND (F_DATA = '1')
Re: sql server query to oracle 10g query [message #332411 is a reply to message #332409] Tue, 08 July 2008 08:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Re: sql server query to oracle 10g query [message #332412 is a reply to message #332409] Tue, 08 July 2008 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
They work as it (I think).

Regards
Michel

[Updated on: Tue, 08 July 2008 08:27]

Report message to a moderator

Re: sql server query to oracle 10g query [message #332429 is a reply to message #332412] Tue, 08 July 2008 09:07 Go to previous messageGo to next message
acarella
Messages: 21
Registered: July 2008
Location: Latham, NY
Junior Member
This is actually a move statement:

need to move the component data from OSCALL to EUCALL and NAZHAZ to EUHAZ.

These are the three sql statements which needs to be changed for an oracle 10g client

INSERT INTO T_COMP_DATA
(F_Cas_Number, F_Component_Id, F_Data_Code, F_DATA)
SELECT F_Cas_Number, F_Component_Id, 'EUCALL' AS Expr1, F_DATA
FROM T_COMP_DATA
WHERE (F_Data_Code = 'OSCALL') AND (F_DATA = '0.1')

INSERT INTO T_COMP_DATA
(F_Cas_Number, F_Component_Id, F_Data_Code, F_DATA)
SELECT F_Cas_Number, F_Component_Id, 'EUCALL' AS Expr1, F_DATA
FROM T_COMP_DATA
WHERE (F_Data_Code = 'OSCALL') AND (F_DATA = '1')

INSERT INTO T_COMP_DATA
(F_Cas_Number, F_Component_Id, F_Data_Code, F_DATA)
SELECT F_Cas_Number, F_Component_Id, 'EUHAZ' AS Expr1, F_DATA
FROM T_COMP_DATA
WHERE (F_Data_Code = 'NAHAZ') AND (F_DATA = '1')

Re: sql server query to oracle 10g query [message #332435 is a reply to message #332429] Tue, 08 July 2008 09:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
They look ok - are you getting errors when you run them?
Re: sql server query to oracle 10g query [message #332440 is a reply to message #332435] Tue, 08 July 2008 09:20 Go to previous messageGo to next message
acarella
Messages: 21
Registered: July 2008
Location: Latham, NY
Junior Member
yes, it was my dsn connection.
our oracle dba is out of the office
I am new and am not set up and don't have the client development database to try this on.
will these sql statements copy over the components from the f_data_code that is = to EUCALL. I am not familiar with AS Expr1. Is is written correctly. I cannot test it without a connection.
thank you for your help.
Re: sql server query to oracle 10g query [message #332494 is a reply to message #332440] Tue, 08 July 2008 12:58 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
They look ok as Michel said. The as expr1 is unecessary in these cases. You would need it in a CREATE TABLE clause, but not an INSERT.
Re: sql server query to oracle 10g query [message #332511 is a reply to message #332494] Tue, 08 July 2008 15:20 Go to previous messageGo to next message
acarella
Messages: 21
Registered: July 2008
Location: Latham, NY
Junior Member
I found out more information.
The client wants to copy the data in from EUCALL (f_data_code) to OSCALL (f_data_code) and NAZHAZ (f_data_code) to EUHAZ (f_data_code).

so my question is if I do not use the AS Expr1, will the insert statement still copy over the data from EUCALL to OSCALL or does the field name need to be in the statement somewhere f_data_code.
Thank you both for your help.

INSERT INTO T_COMP_DATA
(F_Cas_Number, F_Component_Id, F_Data_Code, F_DATA)
SELECT F_Cas_Number, F_Component_Id, 'EUCALL' AS Expr1, F_DATA
FROM T_COMP_DATA
WHERE (F_Data_Code = 'OSCALL') AND (F_DATA = '0.1')

INSERT INTO T_COMP_DATA
(F_Cas_Number, F_Component_Id, F_Data_Code, F_DATA)
SELECT F_Cas_Number, F_Component_Id, 'EUCALL' AS Expr1, F_DATA
FROM T_COMP_DATA
WHERE (F_Data_Code = 'OSCALL') AND (F_DATA = '1')

INSERT INTO T_COMP_DATA
(F_Cas_Number, F_Component_Id, F_Data_Code, F_DATA)
SELECT F_Cas_Number, F_Component_Id, 'EUHAZ' AS Expr1, F_DATA
FROM T_COMP_DATA
WHERE (F_Data_Code = 'NAHAZ') AND (F_DATA = '1')
Re: sql server query to oracle 10g query [message #332586 is a reply to message #332511] Wed, 09 July 2008 01:38 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure I understand what you are saying nor can see (at first sight) any difference between 3 x 3 INSERT INTO's you've posted, but
Quote:
The client wants to copy the data in from EUCALL (f_data_code) to OSCALL (f_data_code) and NAZHAZ (f_data_code) to EUHAZ (f_data_code).

sounds as if you do NOT want to use INSERT, but UPDATE. Something like this:
UPDATE t_comp_data SET
  f_data_code = 'OSCALL'
  WHERE f_data_code = 'EUCALL';
The same goes for NAZHAZ -> EUHAZ.
Re: sql server query to oracle 10g query [message #332742 is a reply to message #332586] Wed, 09 July 2008 09:38 Go to previous messageGo to next message
acarella
Messages: 21
Registered: July 2008
Location: Latham, NY
Junior Member
I certainly am glad you are digging deep.
I just started this job and the oracle dba is on vacation and I am going to school for oracle and am not clear on all concepts.

The three insert sql server statements are from one of our customer reps. I was merely seeing if they worked in oracle.

However, it is like you said. The customer merely wants to copy data from EUCALL to OSCALL and NAZHAZ to EUHAZ.

Thank you very much.
Re: sql server query to oracle 10g query [message #332745 is a reply to message #332586] Wed, 09 July 2008 10:01 Go to previous message
acarella
Messages: 21
Registered: July 2008
Location: Latham, NY
Junior Member
The difference between the three inserts is that the first two for OSCALL have a f_data = 0.1 and the second 1 and the third is for NAZHAZ f_data = 1

However, when I try to run the update statement
WHERE (f_data_code = 'EUCALL') AND (f_data = '0.1')
it returns a integrity violation constraint:
unique constraint (wercs.t_comp_data_key) violated.

However, the insert statement runs without the violation constraint.

Will the insert statement work and copy/insert the selected fields/data from one subsection to the other subsection?

[Updated on: Wed, 09 July 2008 10:07]

Report message to a moderator

Previous Topic: filter invalid amount records
Next Topic: Manual table join error
Goto Forum:
  


Current Time: Sat Dec 10 20:24:48 CST 2016

Total time taken to generate the page: 0.08197 seconds