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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select in select >0

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 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
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_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.
Go to Top of Page

mike13
Posting Yak Master

219 Posts

Posted - 2012-11-13 : 11:53:52
hi the ms amount should be bigger then 0
but that doesn't work with the "AS amountemails"
Go to Top of Page

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?
Go to Top of Page

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,
amountemails
FROM
(
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 T
WHERE
amountemails > 0
ORDER BY
amountemails DESC,
shopname


http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

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 - shopname

thanks a lot
Go to Top of Page

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 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
Go to Top of Page

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 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
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-14 : 11:38:11
This was resolved in a different thread
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=180645

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
   

- Advertisement -