| Author |
 Topic  |
|
|
mike13
Posting Yak Master
Netherlands
181 Posts |
 Posted - 11/13/2012 : 11:27:39
|
Hi All,
Having trouble just showing the results that have more then 0
SELECT 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 amountemails
FROM dbo.T_SYS_ShopSystem
ORDER BY amountemails DESC, shopname
tnx a lot |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/13/2012 : 11:36:11
|
Does adding a WHERE clause satisfy the requirement?....
FROM dbo.T_SYS_ShopSystem
WHERE
(
SELECT COUNT(*) AS msg
FROM dbo.T_Customer_Mailbox
WHERE (datereply IS NULL)
AND (STATUS = 1)
AND (ToUser = dbo.T_SYS_ShopSystem.managername)
) > 0
ORDER BY
....BTW, using a JOIN might be more efficient and more readable than using the subquery. |
 |
|
|
mike13
Posting Yak Master
Netherlands
181 Posts |
Posted - 11/13/2012 : 11:53:52
|
hi the ms amount should be bigger then 0
but that doesn't work with the "AS amountemails"
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/13/2012 : 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
Flowing Fount of Yak Knowledge
3833 Posts |
|
|
mike13
Posting Yak Master
Netherlands
181 Posts |
Posted - 11/14/2012 : 02:39:48
|
Thanks Lamprey,that did almost the trick.
i need the amount and shopname in 1 field ex: 80 - shopname
thanks a lot |
|
|
|
mike13
Posting Yak Master
Netherlands
181 Posts |
Posted - 11/14/2012 : 06:59:12
|
Got it to work on 2008 but i get <Unsupported Data Type> on 2005, any idea how to fix it
SELECT TOP (100) PERCENT managername, '(' + RTRIM(CAST(Aemails AS char)) + ') ' + shopname AS amountemails
FROM (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 T
WHERE (Aemails > 0)
ORDER BY shopname |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3833 Posts |
Posted - 11/14/2012 : 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 it
SELECT TOP (100) PERCENT managername, '(' + RTRIM(CAST(Aemails AS char)) + ') ' + shopname AS amountemails
FROM (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 T
WHERE (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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
|
| |
Topic |
|
select in select >0
