|
When you have case to use agregate functions then better use GROUP BY but still you can use DISTINC and with subquery you can get SUM or any else agregate function.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Ok, thanks. It's good to know 
|
|
|
|
|
In my Application I use the stored procedure sp_helpsubscriberinfo. On a Microsoft SQL Server 2000 this works as it should. But on a SQL Server 2008 it returns me an empty table. This happens also through the SQL Server Management Studio, so it is no error in my Application.
Has anything chaged in accessing/using this stored procedure?
[Update]
The return code of sp_helpsubscriberinfo is 0, means no error.
The stored procedure sp_helpmergesubscription returns a non-empty table with the same user account.
[/Update]
Thanks for your help!
Roland
|
|
|
|
|
Hi to all,
IIf(IsDBNull(reader.Item("Amount")), 0, Convert.ToDouble(reader("Amount")))
Can anyone tell me what is the problem in this line if any? Please its urgent.
Waiting for your earliest response.
cheers,
sneha
|
|
|
|
|
Problem.... hmmm... do you get any error? What makes you think there's a problem? Only thing I can see is that you're not assigning the result to any variable... could that be it?
|
|
|
|
|
A description of the problem you're getting would help. Also the VB forum would be more appropriate.
What SQL type is Amount? I'm not that well up in VB but in C# you'd get a compiler error due to the difference in type of the last two parameters in your iif.
Regards,
Rob Philpott.
|
|
|
|
|
Rob Philpott wrote: you'd get a compiler error due to the difference in type of the last two parameters in your iif
I thought of that, but something tells me VB will be alot more foregiving. It will no doubt choose the right datatype depending on the phase of the moon raised to the power of the current millisecond, but only on tuesdays.
|
|
|
|
|
It also works of Thursday providing that it is the last Thursday in the month or the first if the number of days in the month is equal to that of the previous month... it's a lot more complex than you initially thought I'm afraid 
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
Hi frd's
Our client needs that his data base should be encrypted. Here i want to encrypt data base in such manner that it should work normally from .net web application but when some one tries to open it in SQL server IDE it should not show raw data. Even password protection is also OK for me for .MDF and .LDF files.
Help me please...
Thanks & Regards,
Anil Chelasani
|
|
|
|
|
Here is some help[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i have to display leave allotted record of a single employee in gridview but because i have stored three records of permanent employee in generalinfo table its displaying three times my query is as follows
SELECT LeaveMaster.LeaveType, LeaveAllotted.NoOfDays FROM LeaveMaster INNER JOIN LeaveAllotted ON LeaveMaster.LeaveTypeID = LeaveAllotted.LeaveTypeID INNER JOIN EmpCategType ON LeaveAllotted.EmpCategID = EmpCategType.EmpCategID INNER JOIN GeneralInfo ON EmpCategType.EmpCategID = GeneralInfo.EmpCategID
what should i do to rectify this problem
a help from somebody will be deeply appreciated.
|
|
|
|
|
Have you tried the distinct keyword ?
SELECT distinct LeaveMaster.LeaveType, LeaveAllotted.NoOfDays FROM LeaveMaster INNER JOIN LeaveAllotted ON LeaveMaster.LeaveTypeID = LeaveAllotted.LeaveTypeID INNER JOIN EmpCategType ON LeaveAllotted.EmpCategID = EmpCategType.EmpCategID INNER JOIN GeneralInfo ON EmpCategType.EmpCategID = GeneralInfo.EmpCategID
_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.
|
|
|
|
|
Thank you smcnulty
i forgot to do that now its working
once again thankyou very much
|
|
|
|
|
Yes I fully agree with Scott M
Use distinct clause
Niladri Biswas
|
|
|
|
|
Team,
I have a web Windows server 2003 64-bit and installed Sql server analysis services 2000 along with SP4, the problem is that: our application is ASP classic and has the connection string:
"Provider=MSOLAP.2;Timeout=60;Data Source=localhost;Initial Catalog=myCube;Client Cache Size=25"
I can not find a provider for MSOLAP.2, I have copied most of the dlls from the legacy server (32-bit) and registered them, but still cannot connect to that cube.
when I check with a .udl file I don't see any provider for OLAP.
What do I need to setup? Any idea? What is wrong here?
Note: I can open analysis services manager and connect to my cubes successfully.
please advice.
Thanks in advance.
ThaScorpion
|
|
|
|
|
how to handle no data found error in sql server
|
|
|
|
|
How is it an error? It just means youur query returned no results. Handle it where? Please explain exactly what the problem is, my mystic powers are worn out by Friday.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Suppose i have a table like this..
COL1 COL2 COL3
X 100 1
X 200 2
X 300 3
Y 100 1
Y 200 2
Z 300 1
i want to select col1 where 100=1 and 200=2 and 300=3
My small attempt...
|
|
|
|
|
In the where clause convert both col2 & 3 to strings and compare the first character of each.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Solution 1:
SELECT COL1
FROM tbl_Test
WHERE COL2 = (COL3 * 100);
Solution 2:
SELECT COL1
FROM tbl_Test
WHERE (COL2 = 100 AND COL3 = 1)
OR (COL2 = 200 AND COL3 = 2)
OR (COL2 = 300 AND COL3 = 3)
Solution 3:
select COL1
from tbl_Test
where ( (COL2=100 and COL3=1) or (COL2=200 and COL3=2) or (COL2=300 and COL3=3) )
Please vote
Niladri Biswas
|
|
|
|
|
i am using sql server database
please check the below table two columns say column A and Column B
A B
1 100
1 200
1 300
2 100
2 200
3 100
if the user give 100,200 and 300 i want return 1 ( which has all these values)
if the user give 100 and 200 i have to give 1 and 2. Hope this is clear.... Please help me to build the query
My small attempt...
modified on Thursday, June 4, 2009 7:50 AM
|
|
|
|
|
Hello this time the question is not clear.
Please be more specific.
This is a very poor way of asking your question. Please define the function mapping your input to output more specifically
Tell us the desired output.
Is it
For case 1:
Input 100,200,300
Output:
A B
1 100
1 200
1 300
For Case 2:
I/P: 100 , 200
O/P:
A B
1 100
1 200
2 100
2 200
OR
A B
1 100
2 200 ?
Please from next time whenever you post, give the sample output so that it will be easy to solve.
Also, are you passing a comma delimited value or what?
Nothing clear!: 
Niladri Biswas
modified on Sunday, June 7, 2009 2:39 AM
|
|
|
|
|
Anyway
Assuming that
For case 1:
Input 100,200,300
Output:
A B
1 100
1 200
1 300
Solution
SELECT MIN(A), B
FROM tbl_test
WHERE B IN (100, 200, 300)
GROUP BY B;
For Case 2:
I/P: 100 , 200
O/P:
A B
1 100
1 200
2 100
2 200
Solution is
SELECT A, B
FROM tbl_test
WHERE B IN (100, 200)
ORDER BY A, B;
hope this helps.
And vote me
And from next time please define the function mapping your input to output more specifically
Niladri Biswas
|
|
|
|
|
Hello,
When I try to insert unicode chars (Arabic chars) in my database which has its default collation to (latin) these char are inserted incorectly even if I use the nvarchar data type is this because of the collation, can II use the Arabic bin collation to resolve this proplem or ther is an other raison?
Thanks
Dad
|
|
|
|
|
Good Morning All
I have Table Defined as
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EXP_REL_SLOT_DOMN](
[SLOT] [int] NOT NULL,
[DOMN] [int] NOT NULL,
[PREF] [int] NOT NULL
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'references TBL_SLOT_ALLC.ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'EXP_REL_SLOT_DOMN', @level2type=N'COLUMN', @level2name=N'SLOT'
indexed as
CREATE UNIQUE CLUSTERED INDEX [EXP_REL_SLOT_DOMN_INDEX] ON [dbo].[EXP_REL_SLOT_DOMN]
(
[SLOT] ASC,
[DOMN] ASC,
[PREF] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF) ON [PRIMARY]
And i have Two insert statements that takes 29 seconds each
--29 seconds
INSERT into tempslot
select distinct sd1.slot as s1, sd2.slot as s2
from [dbo].[EXP_REL_SLOT_DOMN] sd1
inner join [dbo].[EXP_REL_SLOT_DOMN] sd2
on sd1.domn = sd2.domn
and sd1.slot > sd2.slot
--29 seconds
INSERT into tempslot
select distinct sd1.slot as s1, sd2.slot as s2
from [dbo].[EXP_REL_SLOT_DOMN] sd1
inner join [dbo].[EXP_REL_SLOT_DOMN] sd2
on sd1.domn = sd2.domn
and sd1.slot < sd2.slot
How can i improve the Perfomance of this Insert statements
Thank you
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
