November 20, 2007 at 2:15 am
I am using select statement to print the output of the table to the email messages. I want to avoid printing null values. Is there a way out.
Data type is defined as int. and when there is no value it takes null.
November 20, 2007 at 2:38 am
you could cast the int to a varchar (since it is an email it will be textual anyway, and replace nulls with say ''
Example:
select coalesce(cast(intcolumn as varchar(10)),'') from mytable
Regards,
Andras
November 20, 2007 at 2:49 am
u could use the function COALESCE...
SELECT COALESCE( CONVERT( VARCHAR(15), @SomeInteger ), 'No Value' ) FROM SomeTable
--Ramesh
November 20, 2007 at 3:01 am
This should do it.
ISNULL([CHECK EXPRESSION],[REPLACE STRING])
November 20, 2007 at 3:17 am
Thanks Ramesh/Andras Your solutions worked fine, but they are only good for the first row of the table, What about the other rows. All the records are not null.
November 20, 2007 at 3:20 am
ckmoied (11/20/2007)
Thanks Ramesh/Andras Your solutions worked fine, but they are only good for the first row of the table, What about the other rows. All the records are not null.
You would need to use this for all your columns that may contain a null value. If you post your query we could help.
Andras
November 20, 2007 at 3:28 am
select top 1 Task Task,coalesce(cast(Added as varchar(10)),'') Added,coalesce(cast(Updated as varchar(10)),'') Updated, Time [Time(ms)], Message Message from MyTable
The columns Added and Updated have null values in the first row while the remaining rows have integer values in it. In future my table may have null values in these two columns say in rows 7 and 8 as well.
November 20, 2007 at 3:42 am
First of all, the statement
select top 1 Task Task,coalesce(cast(Added as varchar(10)),'') Added,coalesce(cast(Updated as varchar(10)),'') Updated, Time [Time(ms)], Message Message from MyTable
will not guarantee the order of rows (i.e. it might not return the same rows on every execution). To ensure the order, you must specifiy an ORDER BY clause....
Secondly, I'm not sure i've understood your query, but the COALESCE function will be executed for each row of the table.
--Ramesh
November 20, 2007 at 3:49 am
Ramesh
as of now I have only 5 rows in my table and only the first one contains null values in Added and Updated columns.
In worst case I may have more rows with null values in the future. but as of now I want to deal with only 5 rows where first one has nulls.
November 20, 2007 at 4:30 am
Could be elaborate on what exactly you want?
I still couldn't get you what you are pointing to.:cool:
Do you want only those rows where the said columns are not null?
--Ramesh
November 20, 2007 at 5:01 am
I want my complete table 5X5 printed by select statment other than the nulls. as of now only the first row contains the null values in two columns.
November 20, 2007 at 6:17 am
Then, just remove the TOP clause from the select statement...
--Ramesh
November 20, 2007 at 11:52 pm
Thanks Ramesh, It works fine.
November 28, 2007 at 2:08 am
When run through query analyzer the following query gives me the correct results:
Query:
select Task Task,coalesce(cast(Added as varchar(10)),'') Added,coalesce(cast(Updated as varchar(10)),'') Updated, [Time] [Time(ms)], Message Message from MyTable
Results:
Task Added Updated Time(ms) Message
----- ------ -------- --------- --------------
T100:00:25:877Successful
T2056100:00:00:923Successful
T301681400:00:31:873Successful
but when run through the stored procedure in xp_sendmail it gives me the wrong results as below:
Stored Procedure:
EXEC master.dbo.xp_sendmail
@recipients = 'myname@mydomain.com,
@query = 'select Task Task,coalesce(cast(Added as varchar(10)),'') Added,coalesce(cast(Updated as varchar(10)),'') Updated, [Time] [Time(ms)], Message Message from MyTable',
@Subject = 'My Subject',
@Message = 'My Message'
Results:
T! ) Added,coalesce(cast(Updated as varchar(10)),
00:00:25:877 Successful
T2 0
00:00:00:923 Successful
T3 0
00:00:31:873 Successful
Whats the reason for this:
Please note that the first row has Nulls in Added and Updated Columns
November 28, 2007 at 2:19 am
ckmoied (11/28/2007)
When run through query analyzer the following query gives me the correct results:Query:
select Task Task,coalesce(cast(Added as varchar(10)),'') Added,coalesce(cast(Updated as varchar(10)),'') Updated, [Time] [Time(ms)], Message Message from MyTable
Results:
Task Added Updated Time(ms) Message
----- ------ -------- --------- --------------
T100:00:25:877Successful
T2056100:00:00:923Successful
T301681400:00:31:873Successful
but when run through the stored procedure in xp_sendmail it gives me the wrong results as below:
Stored Procedure:
EXEC master.dbo.xp_sendmail
@recipients = 'myname@mydomain.com,
@query = 'select Task Task,coalesce(cast(Added as varchar(10)),'') Added,coalesce(cast(Updated as varchar(10)),'') Updated, [Time] [Time(ms)], Message Message from MyTable',
@Subject = 'My Subject',
@Message = 'My Message'
Results:
....
Because you are putting your query string into a '' you need to escape the '. In a string literal this is done by duplicating it, so instead of ' you need '', instead of '' you need ''''
So try using:
@query = 'select Task Task,coalesce(cast(Added as varchar(10)),'''') Added,coalesce(cast(Updated as varchar(10)),'''') Updated, [Time] [Time(ms)], Message Message from MyTable'
Regards,
Andras
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply