|
If one has to select about 30-100 different rows from a table based on their ids...
which one is best approach:
1.
select * from table where id1=3 or id1=9 or id1=200 or id1=205 ...
or
2. using a loop... eg
foreach(string id in ids)
{
stmt="select * from table where id3="+id
}
thankyou for guidance...
|
|
|
|
|
Generally speaking, the fewest trips between the database client and the server, the better the application will perform. This is especially true for queries that return small amounts of data. For this reason, option #1 is more likely to perform better.
Adam Maras | Software Developer
Microsoft Certified Professional Developer
|
|
|
|
|
|
Does MySql not have an IN operation, I hate using OR in a where clause.
Select * from Table where ID in(1,23,4,56,700)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Based on the 2 options that you gave, the first option is better.
|
|
|
|
|
So I am working on this SP for my DB at work:
CREATE PROCEDURE dbo.sp_CreateChecklistForPackage
(
@PACKAGE varchar(255)
)
AS
INSERT INTO TBL_SWBOM_CHECKLISTS
(
PACKAGE,
TEMPLATEOWNER,
STEPNUMBER,
STEPNAME,
DESCRIPTION,
LASTCHANGE,
LASTCHANGEBY
)
select
@PACKAGE,
TEMPLATEOWNER,
STEPNUMBER,
STEPNAME,
DESCRIPTION,
LASTCHANGE,
LASTCHANGEBY
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
GO
What I need it to do is find the newest entry for each of the three users (specific names that I can test against, like "TEMPLATEOWNER='Joe Smith') and put only that template into the SWBOM_CHECKLISTS table only. So the TBL_SWBOM_CHECKLISTS table should have one entry per package made up of three.
So basically I need it to find the newest entry from each of the three user's separate entries and then put that into the other table. I think it's fairly simple and doable, just not 100% sure.
|
|
|
|
|
Well, it is easy enough to find the newest entry for a given owner in your setup:
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE LASTCHANGEBY = 'Joe Smith'
ORDER BY LASTCHANGE DESC
Wrap something like that in your INSERT and you should be good to go, at least for the first of your three records.
|
|
|
|
|
I have created an SSIS package that works if Windows authentication is
selected, but fails upon using SQL Server Authentication.
Basically, Its not saving the password for SQLServer Authentication.
Can anyone please help me on this ?
|
|
|
|
|
Are you able to loging into SQL Server using the same User ID or password ? Does that user having permission to access the Data?
You can check it from Security Permission. Check the User, which you are using having access permission on that particular database or not.
Steps :
1. Goto SQL Server Object Explorer > Security > Logins
2. Double Click on the particular user, that you need
3. Goto User Mapping Tab
4. Check Which Datbases are selected and what role is given to user.
5. Update
That's All 
Thanks.
|
|
|
|
|
1. No cross-posting
2. Don't use urgent. This is run by volunteers so your cries for time constraints make us want to avoid you.
|
|
|
|
|
For database connections or any other configurable objects, in your SSIS Package use Configuration File as the value might be changing with environments.
In the target envieonment, you just need to update the configuration file and it would work fine.
|
|
|
|
|
Hi all,
here is my SP
create FUNCTION fn_Ceiling
(
Source IN DECIMAL
)
RETURN DECIMAL
as
DecDiff DECIMAL(16,3);
Ceiling DECIMAL(16,3);
Source1 DECIMAL;
BEGIN
SELECT NVL(Source, 0) INTO Source1 FROM DUAL;
SELECT Source1 - FLOOR(Source1) INTO DecDiff FROM DUAL;
SELECT FLOOR(Source) +
case
WHEN DecDiff between 0.000 and 0.250 THEN 0.000
WHEN DecDiff between 0.250 and 0.750 THEN 0.500
WHEN DecDiff between 0.750 and 1.000 THEN 1.000
else DecDiff
end INTO Ceiling FROM DUAL;
return Ceiling;
END;
/
It is created...
but if i execute the function am not getting the correct value
ex:-
select fn_ceiling(1.75) from dual;
am getting the result as 1
(actaul result should be 1.500)
Dnt know what i did wrong?
|
|
|
|
|
Not sure with Oracle but I would look at Source1, no presision is declared.
Also your case logic is going to cause a problem with 25 and 75. It meets 2 conditions is should be
WHEN DecDiff between 0.000 and 0.250 THEN 0.000
WHEN DecDiff between 0.251 and 0.750 THEN 0.500
WHEN DecDiff between 0.751 and 1.000 THEN 1.000
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If i add the presision also the same result..
|
|
|
|
|
hi,
Below is my table.
Parent ID.......ID.......Name
NULL..........10001.....Region1
NULL..........10002.....Region2
NULL..........10003.....Region3
10001.........501.......Test1
10001.........502.......Test2
10002.........503.......Test3
10002.........504.......Test4
10003.........505.......Test5
10003.........506.......Test6
501...........507.......Test7
501...........508.......Test8
502...........509.......Test9
502...........510.......Test10
I want the output to be the following.
ParentID.......ID.......Name
10001..........501......Test1
10001..........502......Test2
501............512......Armenia
501............536......Brunei
NULL...........10001....Region1
Based on where clause for ID = '10001'
Pls help. I can't figure out on how to add the last entry of "NULL......10001....Region1"
Thx.
|
|
|
|
|
The query logic is not coming out clear from your posting, please elaborate of the selection criteria...
|
|
|
|
|
well actually i need it to be that output for my asp.net treeview control.
if you look the 10001 is the main ParentID and the ChildID is based on ParentID. in the ID = 501 is also fall inside 10001 ID group, and there is a SubChild for the ChildID = 501
I hope u understand.....i donno how to say...hehe
its a TREEVIEW control.
Thx.
|
|
|
|
|
Not sure if I understood your question correctly. Try this:
select * from TableName where
(ParentID = '10001') or (ID='10001') or
(ParentID in (select ID from TableName where ParentID = '10001'))
May be this can help you.
|
|
|
|
|
Normally I arrane to do this by a simple sort on the parent and ID columns but your data is all over the place. Either fixt up your ids to sort the way you want them to come out or use a union query to split the sorting
Select * from table where parentid is not null order by parentid, id
union all
Select * from table where parentid is null order by id
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
What database are you using - SQL 2005 onwards provided the WITH; command whicah can be used for recursive tree patterns like this.
|
|
|
|
|
hi,
i have the following table.
Parent ID.......ID.......Name
NULL..........10001.....Region1
NULL..........10002.....Region2
NULL..........10003.....Region3
10001.........501.......Test1
10001.........502.......Test2
10002.........503.......Test3
10002.........504.......Test4
10003.........505.......Test5
10003.........506.......Test6
501...........507.......Test7
501...........508.......Test8
502...........509.......Test9
502...........510.......Test10
Actually im doing a Treeview control in my ASP.NET application.
I can see the Parent Name, and its child popping out, BUT how to see the ID (501, 502) is having child in ParentID??
My SQL in ASP.NET is "SELECT ID, ParentID, Name FROM Test"
In order to show Child in the Parent, the ParentID has to be NULL.
Any idea??
|
|
|
|
|
You can get a clue from a simple SQL query like
Select distinct ID, ParentID, Name from dbo.tblTest where ID In (Select ParentID from dbo.tblTest)
The output would be like
ID ParentID Name
----------- ----------- -------------
501 10001 Test1
502 10001 Test2
10001 NULL Region1
10002 NULL Region2
10003 NULL Region3
|
|
|
|
|
How can I set a password for DB file and just open it in C# ?
|
|
|
|
|
SajjadZare wrote: How can I set a password for DB file and just open it in C# ?
You want to set SQL Server Password using C# ? Or You want to open a database which is already password protected ?
If you want to open the database which has password, then you need to mention it in ConnectionString.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
