|
|
netJP12L wrote: First name is John and there is no last name then upon concatanting both names i get null.
netJP12L wrote: I have set the database "Concatenate Null yeilds Null" to True
It sounds like this is doing exactly what you told it to. You could:
1) follow i.j.russell's suggestion and use COALESCE, [edit - added]
2) try changing the setting,
3) write code that tests for Null and handles the various cases, or
4) don't allow Null values in these fields.
Looking through the list, i.j.russell's suggestion sounds very good to me.
Please do not read this signature.
|
|
|
|
|
For MS SQL Server I use ISNULL like ISNULL(FirstName + ' ', '') + ISNULL(LastName, '') I put a space after the first name but if it is null then the space does not get added.
|
|
|
|
|
please go through the below query.
SELECT DR.id
, case PC.id when 4 then sum(amount) end
, case PC.id when 5 then sum(amount) end
, case PC.id when 3 then sum(amount) end
, case PC.id when 6 then sum(amount) end
, case when pc.id = 2 and DP.id = 211 then sum(amount) end
, case when pc.id = 2 and DP.id = 210 then sum(amount) end
, case when pc.id = 2 and DP.id = 212 then sum(amount) end
, case when pc.id = 2 and DP.id = 213 then sum(amount) end
, case when pc.id = 2 and DP.id = 214 then sum(amount) end
, case when pc.id = 2 and DP.id = 197 then sum(amount) end
, case when PC.id = 1 and DP.id3 = 45 then sum(amount) end
FROM
table1 DR
LEFT JOIN table2 DP ON DP.id1 = DR.id1
LEFT JOIN table3 PL ON DP.id2 = PL.id
LEFT JOIN table4 PC ON PL.id1 = PC.id
WHERE
DR.date = GETDATE()
group by
DR.id, PC.id, DP.id, DP.id3
hope you understood my requirement.
i didn't think this is the right way to do.
please help any one can do this in a proper manner.
thanks in advance.
|
|
|
|
|
poornas wrote: hope you understood my requirement.
What is your requirement??
Regards,
Prakash Kalakoti
|
|
|
|
|
SELECT DR.id,
SUM(case PC.id when 4 then amount else 0 end) AS PCID4,
SUM(case PC.id when 5 then amount else 0 end) AS PCID5,
SUM(case PC.id when 3 then amount else 0 end) AS PCID3,
SUM(case PC.id when 6 then amount else 0 end) AS PCID6,
SUM(case when pc.id = 2 and DP.id = 211 then amount else 0 end) AS DPID211,
SUM(case when pc.id = 2 and DP.id = 210 then amount else 0 end) AS DPID210,
SUM(case when pc.id = 2 and DP.id = 212 then amount else 0 end) AS DPID212,
SUM(case when pc.id = 2 and DP.id = 213 then amount else 0 end) AS DPID213,
SUM(case when pc.id = 2 and DP.id = 214 then amount else 0 end) AS DPID214,
SUM(case when pc.id = 2 and DP.id = 197 then amount else 0 end) AS DPID197,
SUM(case when PC.id = 1 and DP.id3 = 45 then amount else 0 end) AS DPID45
FROM
table1 DR
LEFT JOIN table2 DP
ON DP.id1 = DR.id1
LEFT JOIN table3 PL
ON DP.id2 = PL.id
LEFT JOIN table4 PC
ON PL.id1 = PC.id
WHERE
DR.date = GETDATE()
group by
DR.id
modified on Tuesday, March 23, 2010 11:24 AM
|
|
|
|
|
I had thought about answering this as I knew something was wrong but could not think what was. i.j.russell you have hit it on the head.
|
|
|
|
|
Hi everOne
I have used switch case statment before but not sure the way I want to use it is implementable or not. If it is then please could you convert the following code into switch Case statement.
create proc Details_GetBYID
(
@Id int,
@TypeId nvarchar(max)
)
AS
IF @TypeId = 'P1'
BEGIN
SELECT motot.Completed,motot.Hours,motot.Subject
FROM MasterT as motot
INNER JOIN PSALevel as psal on psal.Id = motot.Id
WHERE motot.Id = @Id
END
ELSE IF @TypeId = 'P2'
BEGIN
SELECT motot.Completed,motot.Hours,motot.Subject
FROM MasterT as motot
INNER JOIN PSALevel as psal on psal.Id = motot.Id
WHERE motot.Id = @Id
END
ELSE
BEGIN
SELECT motot.Completed,motot.Hours,motot.Subject
FROM MasterT as motot
INNER JOIN PSALevel as psal on psal.Id = motot.Id
WHERE motot.Id = @Id
END
any help would be appriciated.
thanks in adv.
regards
learner
|
|
|
|
|
|
Hey,
why did you use "ELSE IF" when "ELSE" is same???
thats like
if a = 1 then
"abc"
else if a = 2 then
"222"
else
"222"
|
|
|
|
|
Hi
I want to Call stored procedure inside a stored procedure in sqlserver 2005.
I could not found a good example of the above problem.
Please provide an example
Thanks
|
|
|
|
|
use
Exec ProcedureName @Variable
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello Folks,
I'm using powershell in conjuction with the Outlook Redemption library in an attempt to import messages already present in a folder on the desktop. Although, the code gives no errors, I just don't see the end result of the message being in the inbox. The code being used is as follows:
-----------------------------------------------
$outlook = New-Object -COM Outlook.Application
$routlook = New-Object -COM Redemption.RDOSession
$routlook.Logon
$routlook.MAPIOBJECT = $outlook.Session.MAPIOBJECT
$msg = $routlook.GetDefaultFolder(6).Items.Add(0)
$msg.Import("c:\test.msg", 1024)
$msg.save
$routlook.Logoff
-------------------------------------------------
Any help will be appreciated.
Thanks.
Kernel
|
|
|
|
|
I'm sure that there is a more appropriate forum for this question to be in.
|
|
|
|
|
Is there any benefit in setting a primary key in a table variable? Especially if you will perform SELECT and INSERT operations on it?
Like,
DECLARE @Tbl TABLE(Id INT PRIMARY KEY, Name NVARCHAR(36))
|
|
|
|
|
John Gathogo wrote: Is there any benefit in setting a primary key in a table variable?
Yes, mainly to ensure referential integrity. That's probably an unsatisfying answer.
Imagine that your table only has "name" defined in it. We could add "John" as a record, and have a list of things that John should do in some other table. Might look like this;
TABLE Users (Name NVARCHAR(36))
"John"
"Peter"
TABLE Tasks (Name NVARCHAR(36), ThingToDo NVARCHAR(50))
"John", "Water the plants"
"John", "Cook dinner"
"Peter", "Write an article for CodeProject"
We're in trouble as soon as someone adds another user with the firstname "John"; we would no longer be able to differentiate between the tasks of the first "John" and the second "John". Now, it's a "best practice" to define a set of column-names that make a record unique. We could add the [Birthdate] of the user to the "Users" table, that way we could distingiush between the first and the second Johnny. It's easier[^] to just add a generated identity and to use that as a primary key.
A primary key is often accompanied by a clustered index. There's a heap of information on MSDN[^], if that is your kind of thing 
I are Troll 
|
|
|
|
|
Hi Guys,
I've just finished a .NET application that uses MSSQL EXPRESS as its local temp DB. I just wanna ask, for convenience sake, is there a way i can put SQLEXPRESS into the installation such that it installs "silently", with the other app? I mean, without the user having to run the .exe by himself.
He who goes for revenge must first dig two graves.
|
|
|
|
|
Look Here[^]
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Thanx. Was quite helpful.
He who goes for revenge must first dig two graves.
|
|
|
|
|
I want to connect to the sql server database and do some query command via ado. I use this connection string to do that work:"Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DaDian2008;Data Source=localhost;UID=sa;PWD=123456;Network Library=dbmssocn", it works well. But how about with a remote server(LAN). For example, my ip address is 192.168.1.100, i just replace localhost to 192.168.1.100, it would get an exception says:
Code = 80004005
Code meaning = Unspecified error
Source = Microsoft OLE DB Provider for SQL Server
Description = [DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found.
Why? And how can i connect to a remote sql server(MS SQL Server within LAN)?
btw, if I use "Driver={SQL Server};Server=192.168.1.100;Address=192.168.1.100,1433;Network=DBMSSOCN;User ID=sa;PWD=123456;Database=DaDian2008; Then, I can connect to the database on my locale computer, but it would fail again on another computer.
|
|
|
|
|
You need the name/IP address of the server you want to connection to.
localhost - 192.168.1.100 and your computer name are all pointing the the instance of SQL server installed on your machine.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your reply. But my real problem is that, how to connect to a remote ms sql server(within LAN)?
|
|
|
|
|
And the answer is to find out the name/IP Address of the server you wish to connect to and use that in the connection string.
I assume you have the database set up with credentials on the other server!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Oh, I use my computer as sql server, the code which useing "Driver={SQL Server};Server=192.168.1.100;Address=192.168.1.100,1433;Network=DBMSSOCN;User ID=sa;PWD=123456;Database=DaDian2008" works well on my own computer, but doesn't on another computer.
|
|
|
|
|
kcynic wrote: 192.168.1.100
This is a local IP, the other machine is looking for SQL on their machine, not yours. You might try replacing that with a machine name or find out your lan IP. I am presuming you want to use your machine as the SQL server.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
