|
Thanks for your help.
I have solved it by creating a view of all 'Not NULL' (top most) rows . Then created a stored proc to loop through the view and update the TID. A long procedure though but it did work.
Cheers !
|
|
|
|
|
I think you want to do something like this ...
update table A
set tid =
(select max(tid)
from TableA
Group By mid)
Check these for references:
http://msdn.microsoft.com/en-us/library/ms177523.aspx
http://data.bangtech.com/sql/sql_update_with_correlated_subquery.htm
|
|
|
|
|
Hi thanks for your solution and links,
But your query returned an error.
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
|
|
|
|
|
That's because he forgot a small but important condition.
Try this:
update table A as a1
set tid =
(select max(tid)
from TableA as A2
Where A1.mid = A2.mid
Group By mid)
|
|
|
|
|
Thanks for the refinement to my SQL. Sometimes when you don't have the actual data to test with, you forget some stuff.
|
|
|
|
|
David Mujica wrote: Sometimes when you don't have the actual data to test with, you forget some
stuff.
Tell me about it. That's almost something I can put in my CV.
|
|
|
|
|
Hi everybody,
I have some numbers. Numbers are
(
0
234,9
-27
0
-55,9
0
0
-59
0
100,72
-26
0
-25,72
0
0
0
-82
0
0
0
0
0
0
-134)
I want to this.
SELECT SUM(FIELDX) FROM TBLX
but result is returning (-2,8421709430404007E-14)
actually resutl is 0 (Zero)
How can i solve this problem?
Thanks.
Başak Öner
|
|
|
|
|
Basak Oner wrote: actually resutl is 0 (Zero)
Not if you're a computer, then it's "almost 0". Try rounding the result.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
sorry I don't understand your message.
I need your detailed explanation.
Thanks.
|
|
|
|
|
Basak Oner wrote: sorry I don't understand your message.
Try the ROUND function. This is due to how computers work and how they calculate. You know what a fractional number is, but they do not exist within a computer.
Basak Oner wrote: I need your detailed explanation.
You're not in school anymore. There's an explanation here[^], good luck.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks for your free time.
|
|
|
|
|
You actually *read* it?
Ehr.. you're welcome, and yes, well done
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
You can try to change the datatype of the field.
Check if SELECT SUM(cast(FIELDX as numeric(5,2)) FROM TBLX works.
In your real data you naturally need to adjust the precision and scale of the numeric field to fitting values.
|
|
|
|
|
Hi Experts
I have a query which needs to return the second in Day, Hour, Minute, Second format.
The below code works fine when its less than a day, but does not work, when the value in second is greater than a day
PRINT Convert(VarChar, DateAdd(S, 86400, 0), 108)
86400 is exactly a day and it returns 00:00:00
Can someone modify it and show me the result something like this
1:00:00:00.
Thanks
|
|
|
|
|
Take a look at MODULO from BOL!
SELECT 38 / 5 AS Integer, 38 % 5 AS Remainder ;<br />
Use a case statement to detect if it is over 86399 and apply the appropriate formula. If you use it in multiple places then create your own UDF.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try the option, CONVERT(VARCHAR(10),'fieldname',112)
|
|
|
|
|
Try replying to the op next time, he will not get notification when you reply to me!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
try this :
DECLARE @DurationSeconds INT
DECLARE @DurationDays INT
SET @DurationSeconds = 62110
SET @DurationDays = @DurationSeconds /86400
Select CASE WHEN @DurationDays > 0 THEN
Convert(VarChar,@DurationDays)+ ':'+
Convert(VarChar, DateAdd(S, (@DurationSeconds-(@DurationDays*86400)),0), 108)
ELSE
Convert(VarChar, DateAdd(S, @DurationSeconds, 0), 108)
END
|
|
|
|
|
Using Sql 2000 and C#.
I have a stored procedure where I pass in my identifier and use it to select a set of rows from several tables. When I execute this SP in query analyzer it works great, returning only the rows that do not exist in @Data. When executed in C# it is returning the entire set of data relevant to that identifier.
I'm at a loss on this. I've spent hours changing the SP around trying to get different results. I've tried both temp tables and var tables with the same results. Again, it always works in QA but never in C#.
Any thoughts? I've shortened some of the repetitive data from this query to save on screen space (and your eyes) but let me know if there is anything else i can show that could help.
Also, here's the weird part. I have a near exact duplicate of this query that works in both C# and QA. The only difference is that #Chas is populated via a select statement that returns a range of values instead of just being populated by the passed in value. I know i don't have to insert this value into #Chas but instead could just compare directly against it. It was like this just to make it as identical to the original as possible since the original works.
USE [B_D_DashPanel]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_BD_PermConflicts_Chassis_Only]
@Chassis_Search nvarchar(50)
AS
Declare @Data TABLE(
Chassis nvarchar(50),
ItemC nvarchar(50),
Location nvarchar(50),
Panel nvarchar(13),
Part nvarchar(50)
)
Create TABLE #Chas (
Chassis_No nvarchar(50)
)
set nocount on
insert into #Chas (Chassis_No) values (@Chassis_Search)
insert into @Data (Chassis, ItemC, Location, Panel, Part)
select distinct WorkPlacementorder.salesorderno, workplacementorder.itemcode, workplacementorder.location, workplacementorder.panel,
itembompn.partnumber1
from #Chas, workplacementorder
join itembompn
on workplacementorder.itemcode = itembompn.itemcode
inner join chassisdb.dbo.tblopenjit
on workplacementorder.salesorderno = chassisdb.dbo.tblopenjit.chassisnbr
where chassisdb.dbo.tblopenjit.chassisnbr like #Chas.Chassis_No and chassisdb.dbo.tblopenjit.itemid = itembompn.partnumber1
and WorkPlacementorder.salesorderno like #Chas.Chassis_No
SELECT b_d_Dashpanel.dbo.workplacementorder.ItemCode, b_d_Dashpanel.dbo.items.itemdescription
FROM #Chas, B_D_DashPanel.dbo.WorkPlacementOrder join b_d_Dashpanel.dbo.items on b_d_Dashpanel.dbo.workplacementorder.itemcode = b_d_Dashpanel.dbo.items.itemcode
where salesorderno = Chassis_No and b_d_DashPanel.dbo.workplacementorder.itemcode not in (select itemc from @Data)
drop table #Chas
|
|
|
|
|
Logic dictates that there is a difference between @Chassis_Search passed in as a nvarchar and the format stored in the table. Can you do a select from itembompn where field = @Chassis_Search. It may be a character set incompatibility.
Try changing the field to varchar!
Also never use sp_ to prefix your procedures, this forces SQL Server to search all the system procedures as well as your own - it can be a real performance hit.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I would go one step further, don't prefix.
|
|
|
|
|
I still prefix views and functions I gave up on procs many versions ago.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Remove [#Chas] from the entire query. Change your where statements to:
chassisdb.dbo.tblopenjit.chassisnbr LIKE @Chassis_Search
WorkPlacementorder.salesorderno LIKE @Chassis_Search
salesorderno = @Chassis_Search
I question the last where clause - why isn't it also a LIKE? Seems like it would fail when wildcards are used.
|
|
|
|
|
I've tried it both ways, with @chassis_search and using #Chas. The tried it because the other SP selects a large range of values into it and then runs the exact same from the first insert on out. It works, this one doesn't.
I block wildcards on the software side so I'm not to concerned about wildcard entries. I'm used to visual studio using = when a parameter is required.
|
|
|
|
|
I am getting following server error in asp.net application, even i mentioned Max Pool Size="100" in web.config file
Error : "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may occurred because all pooled connections were in use and max pool size was reached."
|
|
|
|