|
I have two tables with the following structure.
Table 1
Name Field1
n1 a
n2 b
n2 c
n3 d
Table 2
ID Name Field1
1 n1 NULL
2 n2 NULL
3 n3 NULL
Usually, Table1.Name and Table1.Field1 have a 1..1 relationship. however in very-rare circumstances they do tend to have 1..n
I need to update Table2.Field1 with Table1.Field1; In SQL Server we could write the following update statement to satisfy our business conditions.
UPDATE Table2 SET Table2.Field1 = T1.Field1<br />
FROM Table1 t1, Table2 t2<br />
WHERE t1.Name = t2.Name;<br />
In oracle, we were having the below statement.
UPDATE Table2 SET Field2 = (SELECT t1.FIELD1 FROM Table1 t1, Table2 t2<br />
WHERE t1.Name = t2.Name);
This statement works for Table1 having 1..1 relationship between Table1.Name and Table1.Field1
For 1..n relationship the ORACLE version fails with the following error - ORA-01427: single-row subquery returns more than one row
The SQLServer sql works for both the cases 1..1 and 1..n; given the nature of the join.
Could someone guide me with appropriate sql for the above issue?
I am looking for a bulk update statement rather than using PL/SQL to go through each recrod in Table2.
Thanks for your help.
Kalyan
modified on Monday, June 7, 2010 10:55 AM
|
|
|
|
|
Fairly obvious, your sub select statement is returning 2 rows for n2, this is illegal. try putting DISTINCT in there
UPDATE Table2 SET Field2 = (SELECT DISTINCT t1.FIELD1 FROM Table1 t1, Table2 t2
WHERE t1.Name = t2.Name);
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your reply.
I do know that multiple values is what is causing this issue.
Using DISTINCT would not work as the values being returned are distinct already. It would still return 2 rows for n2
This issue is due to ORACLE not supporting the same syntax as SQLSERVER. (as far as i know)
I am more comfortable with SQLServer than ORACLE and hence was checking if anyone else came across this issue.
Kalyan
|
|
|
|
|
Sorry, looking at it from the wrong end.
What do you expect the table2 field2 (missing from design given) to contain b or c and what does it contian when you use SQL Server, I'm betting tt has c.
Does Oracle not support update with a join, try using INNER JOIN style instead of the tables in the where clause (horrible format)
No matter how you work it your data structue sucks. the relationship is screwed and I consider Oracle correct in spitting the dummy.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
you are right - (for now) its not important as to which value gets updated. The SQLServer version of the script simply updates the t2.Field with the last found record. So for 'n2' t2.Field1 will always end-up with value 'c'
I was looking for an equivalent in ORACLE so that it will always update with the last row found.
The actual issue is much bigger and i was looking for a quick-fix without introducing a major change.
Looks like we need to address the issue with the current design before spending any further time on this issue,
Thanks for your inputs.
Cheers
Kalyan
|
|
|
|
|
I'm confused. Given the example data you have given above, what value would you expect to see in Table2.Field1 for row id 2? It could be either b or c. Which one do you want and how would you distinguish between them?
If you don't care, then you could just say SELECT MAX(t1.FIELD1). That would give you one value.
As far as I can see with your current SQL Server statement you have no control over which value goes in there, so I'm guessing it's not important to you.
|
|
|
|
|
you are right - (for now) its not important as to which value gets updated. The SQLServer version of the script simply updates the t2.Field with the last found record. So for 'n2' t2.Field1 will always end-up with value 'c'
I was looking for an equivalent in ORACLE so that it will always update with the last row found.
The actual issue is much bigger and i was looking for a quick-fix without introducing a major change.
Looks like we need to address the issue with the current design before spending any further time on this
Thanks for your inputs.
Cheers
Kalyan
|
|
|
|
|
I have a RTF field in my SQL 2005 table, I need to convert it to Text and display it. After a quick research I got the following method... create function dbo.RTF2TXT(@in varchar(8000)) RETURNS varchar(8000) AS BEGIN
DECLARE @object int DECLARE @hr int DECLARE @out varchar(8000)
-- Create an object that points to the SQL Server EXEC @hr = sp_OACreate 'RICHTEXT.RichtextCtrl', @object OUT EXEC @hr = sp_OASetProperty @object, 'TextRTF', @in EXEC @hr = sp_OAGetProperty @object, 'Text', @out OUT EXEC @hr = sp_OADestroy @object return @out
END GO
select dbo.RTF2TXT('{\rtf1\ansi\ansicpg1252\uc1 aaa}')
But Here I am getting only NULL as result... What could be the issue, please suggest
Thanks
|
|
|
|
|
Well if the line wrap has not deceived me then the function does nothing because it is commented out.
You seem to have 4 EXEC strung together in a single line that is commented out!
Do you have any idea what you are doing, sp_OACreate is an OLE Automation procedure. I can't use it because of security permissions so I would not be surprised if that will also kill this.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your response.
I don't think, the line wrap will create issues. I have removed those comments & tried it.
Actually I was trying to convert the Rich text type string to plain text while
fetching records.
Can you suggest any better approach to convert a string from rich text to plain text in sql server.
Thanks once again
Sebastian
|
|
|
|
|
Hello, One more thing
can't we use following code to allow execution of sp_OACreate
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
Thanks
Sebastian
|
|
|
|
|
sp_OACreate is the devil.
This is NOT something that a database should be doing - load the RTF data to your business layer and convert it there.
|
|
|
|
|
Thanks for your reply.
This method also I have tried.
What I have intended to do is to create a class library , add the following code;
public String ConvertRtftoText(RichTextBox rtb)
{
String strText = "";
try
{
strText = rtb.Text;
}
catch (Exception Ex)
{
}
return strText;
}
& call this as a CLR stored procedure.
But the problem which I faced is , when I tried to execute the below code
CREATE ASSEMBLY getTextfromRTF
from '\\ustech39\Shared\BackendRTFHandler.dll'
WITH PERMISSION_SET = UNSAFE
I got another error
Warning: The Microsoft .Net frameworks assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 10301, Level 16, State 1, Line 1
Assembly 'BackendRTFHandler' references assembly 'accessibility, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.
modified on Monday, June 7, 2010 9:19 AM
|
|
|
|
|
So do as Jamie suggested, move the conversion to your business layer where it belongs and get it out of the database.
Why do you want to inflict another layer of abstraction between you nd your data, inplementing the CLR for this is just wrong.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Mycroft Holmes
My intension was to avoid looping through the records in front end.
thanks for the suggestion
Sebastian
|
|
|
|
|
IMHO you have your priorities completely wrong, to save looping the records you are willing to saddle your self with OLE or the CLR, a cursor would be better than that and I hate cursors.
You could convert the content to varchar and then check each character for a-z and punctuation. Nasty but should be doable.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Sebastian T Xavier wrote: My intension was to avoid looping through the records in front end.
Why not do the conversion on the fly, just before the data is required in the BL? Does the data "have" to be re-stored in the database as plain text?
If the answer is yes, consider writing a specialized application to do the conversion once. Another alternative would be a CLR-UDF in Sql Server; you'd load the RTF text into a RichTextBox and return it's plain-text contents.
I are Troll 
|
|
|
|
|
Hello Eddy,
please see an earlier post in this chain..
Click
Will this same issue happen to CLR-UDF?
please advise
Thanks for your thoughts
Sebastian
|
|
|
|
|
Sebastian T Xavier wrote: Will this same issue happen to CLR-UDF?
Aw, yes; the WinForms assembly isn't considered "safe" in Sql Server. Installing the CLR-UDF (when using WinForms components) is described here[^], along with some other viable solutions.
I are Troll
|
|
|
|
|
Good information....
Thank you
Sebastian
|
|
|
|
|
You're welcome 
|
|
|
|
|
Has anyone run SQL Server 2000 Developer's Edition on Windows 2008? If yes, any problem?
|
|
|
|
|
Hi all,
I have a problem in getting the one below to work. I think i have used the right syntax. But keeps coming with error on EXEC Lline...
CREATE FUNCTION Summary (@Month varchar(30),@Year varchar(30),@ShowType varchar(30))
RETURNS TABLE
AS
RETURN
With
EXECUTE C_WEB_sp_RevenueSummaryByShow 'April', '2010', '3'
Go
Thansk for your time
|
|
|
|
|
It_tech wrote: But keeps coming with error on EXEC Lline...
Do you think the error message might help us debug your problem?
|
|
|
|
|
Many thanks for your help.
Here is the error message:
Msg 156, Level 15, State 1, Procedure Summary, Line 6
Incorrect syntax near the keyword 'EXECUTE'.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
