how to display - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to display

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-14 : 00:53:41

hi all,

i have a table called email_meassage,
columns are like this
msg_id,
article_id,
msg_sub,
Msg_text,
msg_status,
msg_attachment etc....

i have to select some of the columns from this table

like msg_id,article_id,Msg_text

problem is i want to display only first 25 characters of msg_text after that i have to display like this '...'(3 dots has to be followed by the select msg_text)
how we can do this select

Regards,
DIvya

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-14 : 01:01:16

Help!!! Anybody there............

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-14 : 01:04:33

Simple....

select left(Msg_text,25)+'...'

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-14 : 01:12:15

hi all,

while executing its showing error like this

Msg 102, Level 15, State 1, Procedure FSP_UserID_ByArticleID, Line 25
Incorrect syntax near '+'.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-14 : 01:14:58

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-14 : 01:20:12

How its throws error??

check this!!

select left('ghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhjhgyutyubghgjkh',25)+'...'

Post your full query!!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/
Go to Top of Page

this wrking fine but my SP is throwing error i dnt knw y... im posting whole SP what i have done

USE [FRONTIERS_TEST]
GO
/****** Object: StoredProcedure [dbo].[FSP_UserID_ByArticleID] Script Date: 12/14/2009 10:43:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: DIVYA GR
-- Create date:
-- Description:

-- EXEC FSP_UserID_ByArticleID 5
-- ==================== =========================
ALTER PROCEDURE [dbo].[FSP_UserID_ByArticleID]
@User_ID int,
@Article_ID INT =null

AS
BEGIN

SET NOCOUNT ON;

IF (@Article_ID = null OR @Article_ID='' OR @Article_ID is Null)

BEGIN

SELECT
FIS_Email_Message_Transactions .Recv_ID as user_mailid,
FIS_Email_Message_Transactions .Msg_ID ,
FIS_Email_Message .Sub AS 'Subject',
(select substring (Msg_Text,1,25) +'...' from FIS_Email_Message),
(case
when FIS_Email_Message_Transactions.Created_Date= GETDATE()
then 'Today'
when FIS_Email_Message_Transactions.Created_Date = (GETDATE ()-1)
then 'yesterday'
else FIS_Email_Message_Transactions.Created_Date
end ) as SendDate
from FIS_Email_Message INNER JOIN FIS_Email_Message_Transactions
ON FIS_Email_Message .Msg_ID =FIS_Email_Message_Transactions .Msg_ID
where FIS_Email_Message.Sender_ID = @User_ID

END
ELSE
BEGIN
SELECT

FIS_Email_Message_Transactions .Recv_ID as User_MailID,
FIS_Email_Message_Transactions .Msg_ID ,
FIS_Email_Message .Sub AS 'Subject',
FIS_Email_Message .Msg_Text as 'Body',
(select Created_Date =
case
when Created_Date= GETDATE()
then 'Today'
when Created_Date = (GETDATE ()-1)
then 'yesterday'
else Created_Date
end
from dbo.FIS_Email_Message_Transactions
)as 'sent date'
from FIS_Email_Message INNER JOIN FIS_Email_Message_Transactions
ON FIS_Email_Message .Msg_ID =FIS_Email_Message_Transactions .Msg_ID
where FIS_Email_Message.Article_ID = @Article_ID
END
END

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-14 : 01:22:51

the table structure is like this......
email_message

Msg_ID int
Article_ID int
Sender_ID int
Role_ID int
Salutation_ID int
Sub varchar(2000)
Msg_Text text
MsgFlag tinyint
Msg_Status bit
IsFileAttached bit
IsDeleted bit
Created_By int
Created_Date datetime
Modified_By int
Modified_Date datetime

email_transaction table

Mail_Rec_ID int
Msg_ID int
Recv_Type char(10)
Recv_ID varchar(100)
Role_ID int
Mail_Status bit
IsDeleted bit
Created_By int
Created_Date datetime
Modified_By int
Modified_Date datetime

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-14 : 01:25:33

<<
but my SP is throwing error i
>>

What is the new error?

Madhivanan

Failing to plan is Planning to fail

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-14 : 01:26:53

my error is like this...

Msg 512, Level 16, State 1, Procedure FSP_UserID_ByArticleID, Line 21
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-14 : 01:33:52

I suspect the problem on your case

(case
when FIS_Email_Message_Transactions.Created_Date= GETDATE()
then 'Today'
when FIS_Email_Message_Transactions.Created_Date = (GETDATE ()-1)
then 'yesterday'
else cast(FIS_Email_Message_Transactions.Created_Date as varchar(20))
end ) as SendDate

Check it now...

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-14 : 01:36:35

quote:
Originally posted by divyaram

my error is like this...

Msg 512, Level 16, State 1, Procedure FSP_UserID_ByArticleID, Line 21
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Put a where clause in your sub query too..

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-14 : 01:36:38

:( again is throwing error

Msg 512, Level 16, State 1, Procedure FSP_UserID_ByArticleID, Line 21
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-14 : 01:37:49

quote:
Originally posted by divyaram

:( again is throwing error

Msg 512, Level 16, State 1, Procedure FSP_UserID_ByArticleID, Line 21
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The Error Clearly says

(select substring (Msg_Text,1,25) +'...' from FIS_Email_Message)

return more then one value.. Restrict it for one value

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-14 : 01:53:20

Hi

In Inner select you will be getting more than 1 value. Try to find why more than 1 value is returned there. You can use Top 1 in inner select to pick first value and avoid this error.

-------------------------
R...

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-14 : 02:01:31

Thank u all...
its wrking fine now.... :)

regards,
Divya

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-14 : 02:09:40

Good...

Started with concatenation '+', travel with 'case', ended with Subquery.. Cool

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-16 : 02:35:56

quote:
Originally posted by divyaram

How its throws error??

check this!!

select left('ghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhjhgyutyubghgjkh',25)+'...'

Post your full query!!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/
Go to Top of Page

this wrking fine but my SP is throwing error i dnt knw y... im posting whole SP what i have done

USE [FRONTIERS_TEST]
GO
/****** Object: StoredProcedure [dbo].[FSP_UserID_ByArticleID] Script Date: 12/14/2009 10:43:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: DIVYA GR
-- Create date:
-- Description:

-- EXEC FSP_UserID_ByArticleID 5
-- ==================== =========================
ALTER PROCEDURE [dbo].[FSP_UserID_ByArticleID]
@User_ID int,
@Article_ID INT =null

AS
BEGIN

SET NOCOUNT ON;

IF (@Article_ID = null OR @Article_ID='' OR @Article_ID is Null)

BEGIN

SELECT
FIS_Email_Message_Transactions .Recv_ID as user_mailid,
FIS_Email_Message_Transactions .Msg_ID ,
FIS_Email_Message .Sub AS 'Subject',
(select substring (Msg_Text,1,25) +'...' from FIS_Email_Message),
(case
when FIS_Email_Message_Transactions.Created_Date= GETDATE()
then 'Today'
when FIS_Email_Message_Transactions.Created_Date = (GETDATE ()-1)
then 'yesterday'
else FIS_Email_Message_Transactions.Created_Date
end ) as SendDate
from FIS_Email_Message INNER JOIN FIS_Email_Message_Transactions
ON FIS_Email_Message .Msg_ID =FIS_Email_Message_Transactions .Msg_ID
where FIS_Email_Message.Sender_ID = @User_ID

END
ELSE
BEGIN
SELECT

FIS_Email_Message_Transactions .Recv_ID as User_MailID,
FIS_Email_Message_Transactions .Msg_ID ,
FIS_Email_Message .Sub AS 'Subject',
FIS_Email_Message .Msg_Text as 'Body',
(select Created_Date =
case
when Created_Date= GETDATE()
then 'Today'
when Created_Date = (GETDATE ()-1)
then 'yesterday'
else Created_Date
end
from dbo.FIS_Email_Message_Transactions
)as 'sent date'
from FIS_Email_Message INNER JOIN FIS_Email_Message_Transactions
ON FIS_Email_Message .Msg_ID =FIS_Email_Message_Transactions .Msg_ID
where FIS_Email_Message.Article_ID = @Article_ID
END
END

hi i need one more colums for output....
for that it has to find count of 'msg_ID' if the msg_id count is more then one it has to show the no of count only else it has to it has to show the'FIS_Email_Message_Transactions.Mail_Rec_ID' ie to whom the user has forwarded the mail

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-16 : 02:53:45

hi all,
we can declare one parameter like @ count, and we check condition for @ count with case statement .... but i m confused on how to do that???

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-16 : 02:57:16

SELECT
FIS_User.First_Name +' '+FIS_User.Last_Name FullName ,
FIS_Email_Message_Transactions .Recv_ID as user_MailID,
--FIS_Email_Message_Transactions .Recv_Type,
(case
when count (FIS_Email_Message_Transactions .Msg_ID )<> 0
then @count = count (FIS_Email_Message_Transactions .Msg_ID
else FIS_Email_Message_Transactions.Mail_Rec_ID
end
)
FIS_Email_Message .Sub AS 'Subject',

divyaram
Posting Yak Master

180 Posts

Posted - 2009-12-16 : 02:57:40

but its not working..... :(

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 03:32:28

SELECT
FIS_User.First_Name +' '+FIS_User.Last_Name FullName ,
FIS_Email_Message_Transactions .Recv_ID as user_MailID,
--FIS_Email_Message_Transactions .Recv_Type,
(counting = case
when count (FIS_Email_Message_Transactions .Msg_ID )<> 0
then count (FIS_Email_Message_Transactions .Msg_ID
else FIS_Email_Message_Transactions.Mail_Rec_ID
end
)
FIS_Email_Message .Sub AS 'Subject',

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources