InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 18:30:05 GMT20http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxYou welcome.Happy to help.Mon, 01 Jun 2009 09:05:27 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxHi, I've just tried computed columns and it works as well.yes, you are right it is much better.Thank you.Mon, 01 Jun 2009 02:50:32 GMTpeter478http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxNot yet , but I will test itThu, 28 May 2009 13:24:42 GMTpeter478http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspx[quote][b]peter478 (5/27/2009)[/b][hr]oh yes Sergiy, thank you , btw the procedure works properly.[/quote]You're welcome.Did you try computed columns?Much less hassle.To give it a try you don't need to delete existing ones, just add 2 and see if it has what you'd expect.Wed, 27 May 2009 15:35:37 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxoh yes Sergiy, thank you , btw the procedure works properly.Wed, 27 May 2009 15:25:33 GMTpeter478http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspx[quote][b]peter478 (5/25/2009)[/b][hr]what does it mean BOL?[/quote]Books On Line, "Help" for SQL Server, press F1 to open.Mon, 25 May 2009 06:15:46 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxThank you, Sergiy I will try it, what does it mean BOL?Mon, 25 May 2009 05:43:38 GMTpeter478http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxMon, 25 May 2009 05:39:50 GMTpeter478http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxFirst of all you don't need 2 triggers.As well as correlated subqueries.[code]ALTER TRIGGER Cis_OP_No_TriggerON dbo.Cis_OP_NoFOR INSERT, UPDATEASIF (UPDATE (N_Vyrobok) or UPDATE (N_Kplan))update p set N_Vyrobok2 = i.N_Vyrobok + '/' +i.N_KPlan from Cis_OP_No p inner join inserted i on p.N_ID = i.N_IDIF UPDATE (N_Cas100ks) update p set N_KS_Hod = 450/i.N_Cas100ks*100/8 from Cis_OP_No p inner join inserted i on p.N_ID = i.N_IDGO[/code]Second, you don't need triggers at all.Use computed columns instead:[code]CREATE TABLE Table Cis_OP_No (N_ID int,N_Cas100ks float,N_Vyrobok nvarchar,N_Kplan nvarchar,N_KS_Hod AS 450/N_Cas100ks*100/8 ,N_vyrobok2 AS N_Vyrobok + '/' + N_KPlan, N_rucne int)[/code]Ths way you'll always have you calculations done instantly and correctly.P.S. Did not test the code, there may be some syntax errors. If you find ones refer to BOL , topic "CREATE TABLE".Mon, 25 May 2009 04:10:49 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxThere is your problem. I bet you that when you run[code]select (((450/i.N_Cas100ks)*100)/8) from Cis_OP_No WHERE N_ID = @e_Nplan[/code]you will get more than one row returned. As the error message tells you "This is not premitted when the subquery follows =, =! , ...."As a matter of interest, why do you have two INSERT/UPDATE triggers, one for handling of updates to N_Vyrobok and one for N_Cas100ks? If I were you I would combine them into one.Mon, 25 May 2009 03:02:34 GMTJan Van der Eeckenhttp://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxHere are 2 triggers which are applied on table.ALTER TRIGGER Cis_OP_No_Trigger1ON dbo.Cis_OP_NoFOR INSERT, UPDATEASIF (UPDATE (N_Vyrobok) or UPDATE (N_Kplan))update Cis_OP_No set N_Vyrobok2 = (select ( i.N_Vyrobok + '/' +i.N_KPlan) from Cis_OP_No p inner join inserted i on p.N_ID = i.N_ID)from Cis_OP_No p inner join inserted i on p.N_ID = i.N_IDALTER TRIGGER Cis_OP_No_Trigger2ON dbo.Cis_OP_NoFOR INSERT, UPDATEASIF UPDATE (N_Cas100ks) update Cis_OP_No set N_KS_Hod = (select (((450/i.N_Cas100ks)*100)/8) from Cis_OP_No p inner join inserted i on p.N_ID = i.N_ID)from Cis_OP_No p inner join inserted i on p.N_ID = i.N_IDTable Cis_OP_No:N_ID intN_Cas100ks floatN_Vyrobok nvarcharN_Kplan nvarcharN_KS_Hod intN_vyrobok2 nvarcharN_rucne intN_ID N_Cas100ks N_vyrobok N_kplan N_KS_Hod N_Vyrobok2 N_rucne1----------5.73----------456000------001------982-------------456000/001---02----------5.73----------123000------001------982-------------123000/001---03----------7.71----------888000------003b-----730-------------888000/003b--0ALTER Procedure dbo.UPdata ( @e_Nplan char(20), @e_Ncas char(20) )AsUpdate Cis_OP_NOSET N_Cas100ks = convert(float,replace(@e_Ncas,',','.'))Where N_Kplan = @e_NplanreturnError message:Subquery returned more than 1 value. This is not premitted when the subquery follows =, =! , ....Thank youMon, 25 May 2009 02:04:35 GMTpeter478http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspx[quote][b]peter478 (5/24/2009)[/b][hr]so what would you suggest ? How to solve this problem, because I need these triggers...[/quote]You may replace badly written triggers with better written ones.Sun, 24 May 2009 17:10:20 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxYou might need to rewrite the trigger. That error cannot magically go away, you need to write sub-queries that do not return more than one value. If you need help, you need to post code.Sun, 24 May 2009 16:38:58 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxso what would you suggest ? How to solve this problem, because I need these triggers...Sun, 24 May 2009 14:50:53 GMTpeter478http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspx[quote][b]peter478 (5/23/2009)[/b][hr]Hi , On this table are some triggers but I think that it should not be the problem.[/quote]That's exactly the problem.Badly written triggers with subqueries built with an assumption that only 1 row is updated always cause this kind of problems.Sat, 23 May 2009 16:46:03 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxHi , There is the only stored procedure which is called through MS access.On this table are some triggers but I think that it should not be the problem.Triggers provides updates of values when someone change a specific field of table.If I update row (id = 3) from the table there is not problem ...proc works.but when I want to update more then one row with same value (N_kplan = 112a) then appiers error.Tbale Cis_OP_No was created manualy via MS AccessCis_OP_No:------------id intN_Kplan varcharN_Cas100ks float etc...id N_kplan N_Cas100ks----------------------------------1 112a 15.782 112a 78.56 3 456b 784 741C 30.24Sat, 23 May 2009 14:04:41 GMTpeter478http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspx[quote][b]peter478 (5/21/2009)[/b][hr]Hi ,one more thing:ALTER Procedure dbo.UPdata ( @e_Nplan char(20), @e_Ncas char(20) )AsUpdate Cis_OP_NOSET N_Cas100ks = convert(float,replace(@e_Ncas,',','.'))Where N_Kplan = @e_Nplan returnError message:Subquery returned more than 1 value. This is not premitted when the subquery follows =, =! , ....Could you give some suggests how to solve it?Thanks[/quote]Peter,The code you posted above can't be the entire stored proc code, since the error message talks about a subquery. Please post the entire procedure as well as a script to create the table Cis_OP_NO and to populate it with some test data. Makes everyone's life easier and will get you proper answers much quicker.Regards,JanSat, 23 May 2009 07:22:24 GMTJan Van der Eeckenhttp://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxHi ,one more thing:ALTER Procedure dbo.UPdata ( @e_Nplan char(20), @e_Ncas char(20) )AsUpdate Cis_OP_NOSET N_Cas100ks = convert(float,replace(@e_Ncas,',','.'))Where N_Kplan = @e_Nplan returnError message:Subquery returned more than 1 value. This is not premitted when the subquery follows =, =! , ....Could you give some suggests how to solve it?ThanksThu, 21 May 2009 05:40:54 GMTpeter478http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxYes John, this is it, what I was looking for :)Thank you very much for your help!PeterTue, 19 May 2009 09:29:01 GMTpeter478http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxI was just gonna ask if your numbers were always formatted like '15,28' with commas then you could use declare @string1 char(20), @string2 floatset @string1 = '152,23'print @string1set @string2 = convert(float,replace(@string1,',','.'))print @string2Tue, 19 May 2009 09:19:17 GMTJohn-150025http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxThank you John ,now it works .... so I will have to modify my source code to substitute " , " to " . "Tue, 19 May 2009 09:14:50 GMTpeter478http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxHi Sergiy,I have just tested your code but the same error message appiers.When I use whole number e.g. 15 there is not problem, but if I use e.g. 15,28 appiers error message.ALTER Procedure dbo.UpdateTB @e_Nplan char(20), /* e.g. 112e */ @e_Ncas char(20) /* e.g. 15,28 */ As Update Cis_OP_NOSET N_Cas100ks = CONVERT(float(2) , LTRIM(RTRIM(@e_Ncas)))Where N_Kplan = @e_Nplan return ThanksTue, 19 May 2009 09:02:16 GMTpeter478http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxconverting a char to a float will only work if the char string is in fact a valid number.declare @string1 char(20), @string2 floatset @string1 = '15,76' -- THIS WILL FAIL--set @string1 = '15.76' -- THIS WILL WORKprint @string1set @string2 = convert(float,@string1)print @string2Tue, 19 May 2009 09:01:20 GMTJohn-150025http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxHi Chris,The reason, why I need to use type Char is that MS access doesnt work with Type float.For that reason I use Char.My entry for @e_Ncas is a number with comma. ( e.g. 15,79)Of course when I change it to float it works but It doesnt solve my problem.I only need to know how to convert char to float under SQL.Thats all.Thank youPeterTue, 19 May 2009 08:52:23 GMTpeter478http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspx[quote][b]peter478 (5/19/2009)[/b][hr]ALTER Procedure dbo.UPdata ( @e_Nplan char(20), @e_Ncas char(20) )AsDECLARE @Nplan as char(20)DECLARE @Ncas as float(2)SET @Nplan = @e_NplanSET @Ncas = CONVERT(float(2) , @e_Ncas)Update Cis_OP_NO SET N_Cas100ks = @Ncas where N_Kplan =@Nplanreturn[/quote]To much typing.Keep it short, do only what needs to be done.[code]ALTER Procedure dbo.UPdata @e_Nplan char(20), @e_Ncas char(20)AsUpdate Cis_OP_NO SET N_Cas100ks = @e_Nplan -- I don't see any point of passing a char(20) value from one var to another char(20) onewhere N_Kplan = CONVERT(float(2) , LTRIM(RTRIM(@Nplan))) -- extra spaces in char variables may cause conversion to fail GO[/code]Tue, 19 May 2009 03:52:24 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxOk you need to make some var changes.You are using a CHar(20) which has a set lenght and SQL is struggling to convert that.Have you tried changed @e_Ncas to a Float in the Procedure declaration?I can't see any reason why it is a char to begin with...ThanksChrisTue, 19 May 2009 02:34:41 GMTChristopher Stobbshttp://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxALTER Procedure dbo.UPdata ( @e_Nplan char(20), @e_Ncas char(20) )AsDECLARE @Nplan as char(20)DECLARE @Ncas as float(2)SET @Nplan = @e_NplanSET @Ncas = CONVERT(float(2) , @e_Ncas)Update Cis_OP_NO SET N_Cas100ks = @Ncas where N_Kplan =@NplanreturnTue, 19 May 2009 02:15:31 GMTpeter478http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxHi, can you post the stored procedure code?Mon, 18 May 2009 07:29:07 GMTJohn-150025http://www.sqlservercentral.com/Forums/Topic719013-8-1.aspxHi I am testing one stored procedure.I have this Test_table :id intname varchardata float I would like to update all records by condidtion:Update Test_table set data = '27.55' where name = 'Peter'When I tested it and appiers error with converting nvarchar to float .Could you help me please.ThanksMon, 18 May 2009 07:00:37 GMTpeter478