Author |
Topic |
mike13
Posting Yak Master
219 Posts |
Posted - 2012-11-13 : 11:27:39
|
Hi All,Having trouble just showing the results that have more then 0SELECT TOP (100) PERCENT managername, shopname, (SELECT COUNT(*) AS msg FROM dbo.T_Customer_Mailbox WHERE (datereply IS NULL) AND (status = 1) AND (ToUser = dbo.T_SYS_ShopSystem.managername)) AS amountemailsFROM dbo.T_SYS_ShopSystemORDER BY amountemails DESC, shopnametnx a lot |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-13 : 11:36:11
|
Does adding a WHERE clause satisfy the requirement?....FROM dbo.T_SYS_ShopSystemWHERE ( SELECT COUNT(*) AS msg FROM dbo.T_Customer_Mailbox WHERE (datereply IS NULL) AND (STATUS = 1) AND (ToUser = dbo.T_SYS_ShopSystem.managername) ) > 0ORDER BY.... BTW, using a JOIN might be more efficient and more readable than using the subquery. |
|
|
mike13
Posting Yak Master
219 Posts |
Posted - 2012-11-13 : 11:53:52
|
hi the ms amount should be bigger then 0but that doesn't work with the "AS amountemails" |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-13 : 14:16:37
|
I must admit that I am not following what you are saying. I don't see any ms amount column in the query at all? Is there more to the query than what you posted? |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-11-13 : 14:52:21
|
If this doesn't get you closer to your desired solution please see the links at the end:SELECT managername, shopname, amountemailsFROM ( SELECT B.managername, B.shopname, ( SELECT COUNT(*) AS msg FROM dbo.T_Customer_Mailbox AS A WHERE A.datereply IS NULL AND A.status = 1 AND A.ToUser = B.managername ) AS amountemails FROM dbo.T_SYS_ShopSystem AS B ) AS TWHERE amountemails > 0ORDER BY amountemails DESC, shopname http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
mike13
Posting Yak Master
219 Posts |
Posted - 2012-11-14 : 02:39:48
|
Thanks Lamprey,that did almost the trick.i need the amount and shopname in 1 field ex: 80 - shopnamethanks a lot |
|
|
mike13
Posting Yak Master
219 Posts |
Posted - 2012-11-14 : 06:59:12
|
Got it to work on 2008 but i get <Unsupported Data Type> on 2005, any idea how to fix itSELECT TOP (100) PERCENT managername, '(' + RTRIM(CAST(Aemails AS char)) + ') ' + shopname AS amountemailsFROM (SELECT managername, shopname, (SELECT COUNT(*) AS msg FROM dbo.T_Customer_Mailbox AS A WHERE (datereply IS NULL) AND (status = 1) AND (ToUser = B.managername)) AS Aemails FROM dbo.T_SYS_ShopSystem AS B) AS TWHERE (Aemails > 0)ORDER BY shopname |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-11-14 : 11:33:40
|
quote: Originally posted by mike13 Got it to work on 2008 but i get <Unsupported Data Type> on 2005, any idea how to fix itSELECT TOP (100) PERCENT managername, '(' + RTRIM(CAST(Aemails AS char)) + ') ' + shopname AS amountemailsFROM (SELECT managername, shopname, (SELECT COUNT(*) AS msg FROM dbo.T_Customer_Mailbox AS A WHERE (datereply IS NULL) AND (status = 1) AND (ToUser = B.managername)) AS Aemails FROM dbo.T_SYS_ShopSystem AS B) AS TWHERE (Aemails > 0)ORDER BY shopname
That appears to be only part of you code, But, here are some observations.1. There is no need for the TOP clause.2. You casted Aemails as a CHAR, but did not specify a size.3. Is shopname also a CHAR?4. In the code you posted you are reference in alias Aemails from the select list in the WHERE clause.5. Since you didn't post your DDL. What are the datatypes of all the columns |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
|
|