SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select in select >0
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
211 Posts

Posted - 11/13/2012 :  11:27:39  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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

5155 Posts

Posted - 11/13/2012 :  11:36:11  Show Profile  Reply with Quote
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

Netherlands
211 Posts

Posted - 11/13/2012 :  11:53:52  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
hi the ms amount should be bigger then 0
but that doesn't work with the "AS amountemails"
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/13/2012 :  14:16:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 11/13/2012 :  14:52:21  Show Profile  Reply with Quote
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

Netherlands
211 Posts

Posted - 11/14/2012 :  02:39:48  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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

Netherlands
211 Posts

Posted - 11/14/2012 :  06:59:12  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 11/14/2012 :  11:33:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/14/2012 :  11:38:11  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000