|
Sorry I had to delete that mess. The 5 WHERE id compares and 5 SELECT IF() were the cause of the 25 returns - 5 x 5. The solution I came up with was to LEFT JOIN the series of required rows and remove the comparisons from the WHERE.
FROM locations,
loc_staticdata,
loc_staticdata_waters AS sw
LEFT JOIN textures AS d1 ON sw.surfaceTextureID = d1.id
LEFT JOIN textures AS d2 ON sw.shoreTextureID = d2.id
LEFT JOIN textures AS d3 ON sw.envMapOverTextureID = d3.id
LEFT JOIN textures AS d4 ON sw.envMapUnderTextureID = d4.id
LEFT JOIN textures AS d5 ON sw.specularMapTextureID = d5.id
WHERE locations.id = _locationID
AND locations.staticDataID = loc_staticdata.id
AND sw.staticDataID = loc_staticdata.id;
I am not sure if it is the best solution, but it works.
Thanks for the attempt. 
INTP
"Program testing can be used to show the presence of bugs, but never to show their absence."Edsger Dijkstra
|
|
|
|
|
I am glad that u found solution, the most important is to work your query, the performance gonna be second with which we have to lookup...
Happy query-ing... 
I Love T-SQL
|
|
|
|
|
hi!
I am having problem with my logic. i have set of records like below
id name num
100 A 1
100 A 2
100 A 3
100 A 4
100 A 46
100 A 47
200 A 3
200 A 4
200 A 5
300 A 1
the data is already ordered by id, name and num. I need to find min(num) and max(num) and group the records for serialised num's for example above record set should be transformed to 4 records as
id name num_min num_max
100 A 1 4
100 A 46 47
200 A 3 5
300 A 1 1
I am having really difficult time as in how my while/if else should work.
I am trying to solve using temp tables/while loop/if else statement. I am not allowed to use cursors. could someone help me how should i do it.
I put all the data in a temp table #tempsrc
set rowcount 1
get all 4 values in a variable
@id, @name, @num
delete the record
if @num+1<> select num from #tempsrc
begin
delete record
end
else
begin
end
while....
if
here is where my problems are could someone point me to right direction
many thanks in forward.
|
|
|
|
|
It doesn't matter what order they are in as long as you are not worried about holes in the order. This is a simple GROUP BY query.
SELECT
id,
name,
MIN(num) AS firstNum,
MAX(num) AS lastNum
FROM
MyTable
GROUP BY
id,
name
|
|
|
|
|
holes in the order is what i am worried about. if its
100 A 1
100 A 2
100 A 3
100 A 4
100 A 46
100 A 47
as mentioned the min and max shouldn't be 1 and 47
it should produce two record records with min max 1,4 and 46,47 respectively.
i thought it was clear what i wanted from my earlier post.
thanks
|
|
|
|
|
if your data looks like this?
100 A 1<br />
100 A 2<br />
100 A 3<br />
100 A 4<br />
100 A 45<br />
100 A 46<br />
100 A 47
then your result have to be like this?
100 A 1<br />
100 A 4<br />
100 A 45<br />
100 A 47
I Love T-SQL
|
|
|
|
|
no i have to have seperate column so its
100 A 1 4
100 A 45 47
for
100 A 1
100 A 4
100 A 45
100 A 47
|
|
|
|
|
If you are worried about the holes then just use correlated subqueries to find the ranges. A WHERE clause would be a pain since you would end up with a tremendous set of loopped queries if the data was of any real size. See if you can grasp this nasty beast:
SELECT
ID,
Name,
num AS StartNumber,
(SELECT
min(num)
FROM
(SELECT
ID,
name,
num
FROM
TestTable b
WHERE
NOT EXISTS (SELECT
*
FROM
TestTable
WHERE
ID = a.ID AND
Name = a.Name AND
num = b.num+1)) tmp
WHERE
num >= a.num) AS EndNumber
FROM
TestTable a
WHERE
NOT EXISTS (SELECT
*
FROM
TestTable
WHERE
ID = a.ID AND
Name = a.Name AND
num = a.num-1)
I tested it with your data set so it works with SQL 2000. Be sure to change the table name (TestTable) and the column names if your database is set up as case sensitive.
The concept comes from finding the start numbers:
SELECT
ID,
name,
num
FROM
TestTable a
WHERE
NOT EXISTS(SELECT * FROM TestTable WHERE
ID = a.ID AND Name = a.Name AND
num = a.num-1)
and the last numbers:
SELECT
ID,
name,
num
FROM
TestTable b
WHERE
NOT EXISTS(SELECT * FROM TestTable WHERE
ID = b.ID AND Name = b.Name AND
num = b.num+1)
You just have to find a way to put the two queries together in a neat correlated package.
|
|
|
|
|
thank you so much for your response. i tried to use this concept in my query at the end i do a group by id,name and num but it takes really long time to do an insert to a table. could you please tell me why its taking such a long time than normal group by query.
thanks
|
|
|
|
|
If you are running this on a large table, you will force a table scan for each portion of the query. Highly inefficient! If this query is run each time you insert a record, you will need to support it with an index.
CREATE INDEX MyIndexName
ON MyTableName
(
id,
name,
num
)
This will slightly slow down a direct INSERT/UPDATE/DELETE but, it should greatly speed up the query to find the holes. This will net you a significant gain if you are running the query on every INSERT.
|
|
|
|
|
Hi, thanks for looking
Im developing a website
On a page i have a listing of products
Products are returned by a really anidated query:
1. I have a view that returns products (Sorted by name)
2. A function then takes this results and makes some calculations and returns a table again (This function is called by many stored procedures and this is the cause of anidated query, not that i like to anidate)
3. The table is then filtered with where clauses by a stored procedure wich is the one that return results to a sqldatasource
My db has a collation of Traditional_Spanish_CI_AS
I added asp.net membership wich has a collation of Modern_Spanish_CI_AS
When i generate the scripts and try to execute them on the remote server, i get an error that says collation conflict, So i tried removing all collations and i executed the scripts succesfully
But now, i have that sorting problem
What could be the cause of this?
Thanks in advance
Alexei Rodriguez
modified on Thursday, April 17, 2008 8:37 PM
|
|
|
|
|
hi paul...
thank u for ur response .but i found answer for every formate....
but i need to convert dates like o4 th july 2005 or
3rd august 1997
31st december 2000
22nd july 1997....i.e th,nd,rd,....
to default formate..... 04/07/2005,
03/08/1997
helpe with a function.....
i am trying this ,but it convert without th,rd,nd,...
alter FUNCTION [dbo].[GetPhoto](@Photo nvarchar(max),@UploadedDate DATETIME)
RETURNS DATETIME AS
BEGIN
DECLARE @GetDate DATETIME
DECLARE @strDate varchar(24)
SET @GetDate = @UploadedDate
if NOT (@Photo IS NULL)
BEGIN
--RETURN @GetDate
DECLARE @Startposition int
SET @Startposition= patindex('%Date:%', IsNull(@Photo, '') )+5
SET @strDate = SUBSTRING(@Photo,@Startposition,len(@Photo)-@Startposition)
if (len(@strDate ) < 24)
BEGIN
RETURN @GetDate
END
if not (@strDate IS NULL)
BEGIN
SET @strDate = LEFT(@strDate ,patindex('%|%',@strPhotoDate )-1)
SET @strDate = replace(@strDate ,'th','')
SET @strDate = replace(@strDate ,'nd','')
SET @strDate = replace(@strDate ,'rd','')
SET @strDate = replace(@strDate ,'st','')
SET @strDate = replace(@strDate ,',','')
set @GetDate = cast(@strDate as DATETIME)
-- set @GetPhotoDate = convert(char,Convert(DateTime,@strPhotoDate,101))
-- set @GetPhotoDate = cast(CONVERT(datetime,@strPhotoDate)-convert(datetime,substring(@strPhotoDate,3,3)) as datetime)
--cast(@GetPhotoDate as DATETIME)
END
END
RETURN @GetPhotoDate
END
like that...
it will convert the 4/12/2008,4-july-2007....but not converting the 4th july 2008
22nd june 2008.
thanks in advance
Thanks
Subbu.
|
|
|
|
|
select convert(varchar(20),convert(datetime,'3 august 1997'),103) will give you this result 03/08/1997
hope it will help...
I Love T-SQL
modified on Thursday, April 17, 2008 10:12 PM
|
|
|
|
|
Hi ,
Thanks for ur response....
select convert(varchar(20),convert(datetime,'3 august 1997'),103)querry works fine .
but i need to convert the date...'3th august 1997' .
if u find any solution then pls....... send me
Thanks in advance
Thanks
Subbu.
|
|
|
|
|
I have an insert trigger on a table that checks a certain set of conditions, and in certain cases, i want it to raise an error and stop the insert from happening.
I've used RAISEERROR to, not surprisingly, raise my error, but if i don't specify a severity of 20 or greater, then the insert just goes ahead anyway and the error gets reported back. I can't specify 20 or greater, as the user that creates the trigger then needs to be a system admin and that's not an option for me.
Has anyone any better idea of how to do this?
|
|
|
|
|
Did you add a 'Return' after the error has been raised?
Example:
IF x<br />
BEGIN<br />
RAISERROR x <br />
RETURN<br />
END
A programmer's life is good... or is it?? Ek dink nie so nie!
|
|
|
|
|
If it's SQL Server, use the instead of trigger.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi Everyone,
I'm application is throwing this exception, and I cant really determine the cause:
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.OleDb.OleDbException: Unspecified error
at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()
I don't get it all the time, it's actually very rare. It's happened about 2 times. It seems to me like the application has a problem establishing a connection with the database ... but I don't get why this would only happen sometimes.
My application uses an Access database that is constantly being accessed while using the application.
Anybody know how I can remedy this?
Thanks,
Tommy
|
|
|
|
|
That's a pretty vague error message. If I were you, I'd put a bunch of try catch blocks in there and try to get more information from the exception by popping up message boxes or printing the exception information into a log file or something. If the exception gives you a line number reference to your code at least that would be a place to start. Without more info...who knows what the underlying cause is.
Hope this helps.
|
|
|
|
|
ALTER procedure [dbo].[sp_BankInsert]
(
@bankid varchar(100),
@bankname varchar(100),
@address1 varchar(100),
@address2 varchar(100),
@city varchar(50),
@state varchar(100),
@country varchar(50),
@telephone1 varchar(20),
@telephone2 varchar(20),
@fax varchar(20),
@email varchar(50),
@contactperson varchar(100),
@cphone varchar(20)
)
begin
insert into tblBankMaster(bankid,bankname,address1,address2,city,state,
country,telephone1,telephone2,fax,email,contactperson,cphone)values('@bankid',
'@bankname','@address1','@address2','@city','@state','@country','@telephone1',
'@telephone2','@fax','@email','@contactperson','@cphone');
end
smar
|
|
|
|
|
Remove the ': should look like:
values(@bankid,
etc.
|
|
|
|
|
How to copy database design of a table into another table without copying its values
|
|
|
|
|
.NET- India wrote: How to copy database design of a table into another table without copying its values
If you want to do this manually, you can do this through Management Studio by right-clicking a table and selecting 'Script Table As... -> CREATE TO'.
If you want do do it in code, you could use SMO[^] to obtain metadata for a particular table and create another table based on this metadata.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
You could also
Select * into [NewTable] from [ExistingTable] where 1=0
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
hello all,
while i am trying to connect my windows application to mysql database, I am facing problem in opening the connection.
The error msg I am getting is "The 'System.Data.Odbc' provider is not registered on the local machine".
OleDbConnection con = new OleDbConnection("provider=System.Data.Odbc;Data Source=localhost; Initial Catalog=GKBM;Persist Security Info=True");
ANYBODY KNOWS THE SOLUTION TO THIS PROBLEM PLEASE REPLY ME AS SOON AS POSSIBLE.
THANKS & REGARDS
SMAR.
SMAR
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
