CODEWITHSUNDEEP
sql-server
jreloz
jreloz

Reputation: 430

How to use @@ROWCOUNT in a SQL Server derived table?

I have multiple join (left join) derived tables in my query and one of them returns an empty result set which gives me a problem in my WHERE clause.

WHERE Clause:

WHERE (ISNULL(BalanceThis.Balance,0) + ISNULL(RL.Balance,0) + ISNULL(OtherPayThis.Balance,0) + ISNULL(RUPBalThis.Balance,0)) <> 0

Query:

LEFT JOIN (
            SELECT AE3.IDNo, SUM(ISNULL(AE3.Debit,0)) AS Debit, SUM(ISNULL(AE3.Credit,0)) AS Credit, 
            ABS(SUM(ISNULL(AE3.Debit,0))-SUM(ISNULL(AE3.Credit,0))) AS Balance
            FROM AccountingEntries AE3 WITH (NOLOCK)                
            WHERE AE3.BookName NOT IN ('BFSL')
                AND AE3.GLAccount = '1.03.01' AND ISNULL(AE3.TC,'') = 'LAC'
                AND ((Datepart(MM,AE3.DateEntry)=Datepart(MM,@Date)) 
                AND Datepart(YEAR,AE3.DateEntry)=Datepart(YEAR,@Date))
                GROUP BY AE3.IDNo
            ) RL ON RL.IDNo = Loans.LoanID

Result set:

IDNo    Debit   Credit  Balance

Is there a way to return a static result set whenever a derive table is empty by using @@ROWCOUNT?

like this:

IDNo    Debit   Credit  Balance
EMP12    0        0       0

Thankss.

Upvotes: 0

Views: 51

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19184

Here's one way to do what you want:

WITH MyQuery As
(
-- This is your original query
-- This returns no rows
SELECT 'I have no rows' As OriginalQuery WHERE 0=1
)

-- Select from your query
SELECT * FROM MyQuery
UNION ALL
-- If there's nothing in your query, UNION ALL this part
SELECT 'I only appear when there are no rows' WHERE NOT EXISTS (SELECT * FROM MyQuery)

Upvotes: 1

Related Questions