[Apokio]

I am trying to figure out if there is a good way to exclude NULL values from a select statement. My code is used to extract data from a chart of accounts and separate the account into groups based on the first two digits of the account number. It also subtotals the values for each set of accounts and then prints a grand total at the end. The problem is I have created a loop that loops through all the accounts and gets this data by incrementing the first two digits by 1 each time. Unfortunately, the accounts do not cover every number between 10 and 62 so I get some NULL data. I do not want NULL data in the report. Is there a good way to fix this short of writing a query specifically for every set of accounts? Thanks. Code and screen shot of data below.

DECLARE @index INT

SELECT @index = 10

WHILE @index < 62

BEGIN
SELECT Account, Description, ShortDescription, Balance
FROM ChartofAccounts 
WHERE LEFT(Account,2) = @index
GROUP BY Account, Description, ShortDescription, Balance

SELECT SUM(Balance) as SubTotal
FROM ChartofAccounts
WHERE LEFT(Account,2) = @index 

SELECT @index = @index + 1
END

SELECT SUM(Balance) AS Total
FROM ChartofAccounts

[Apokio]

Figured it out:

DECLARE @index INT

SELECT @index = 10

WHILE @index < 62

BEGIN
   IF ((SELECT COUNT(Account) FROM ChartofAccounts WHERE LEFT(Account,2) = @index) <> 0)
   BEGIN
     SELECT Account, Description, ShortDescription, Balance
     FROM ChartofAccounts
     WHERE LEFT(Account,2) = @index
     GROUP BY Account, Description, ShortDescription, Balance

     SELECT SUM(Balance) as SubTotal
     FROM ChartofAccounts
     WHERE LEFT(Account,2) = @index
   END

   SELECT @index = @index + 1
END

SELECT SUM(Balance) AS Total
FROM ChartofAccounts

[baavgai]

Looping in a database is always a bad idea and should only be used as a last resort.

Can your code be written like this?

SELECT Account, Description, ShortDescription, Balance, SUM(Balance) as SubTotal
	FROM ChartofAccounts
	WHERE LEFT(Account,2) between 10 and 61
	GROUP BY Account, Description, ShortDescription, Balance
	ORDER BY Account
	
SELECT SUM(Balance) AS Total
	FROM ChartofAccounts

[Apokio]

baavgai, on 28 March 2010 - 05:28 AM, said:

Looping in a database is always a bad idea and should only be used as a last resort.

Can your code be written like this?

SELECT Account, Description, ShortDescription, Balance, SUM(Balance) as SubTotal
	FROM ChartofAccounts
	WHERE LEFT(Account,2) between 10 and 61
	GROUP BY Account, Description, ShortDescription, Balance
	ORDER BY Account
	
SELECT SUM(Balance) AS Total
	FROM ChartofAccounts

The instructor wants certain formatting and that prints all the accounts in one section. I know SQL doesn't do breaks so I made that loop so it would print the accounts and then break print the subtotal for above then break again. And so on and so on. I tried to use the reporting services to create the report but my limited knowledge in how to use that compiled with the limited time to finish this project made me just print this to text file. Whateve I plug into the reporting services just returns the first set of accounts and nothing else. I will probably still work on this even after the class because I would like to figure all of this out.

Another question because I am ignorant about some things. Why is it a bad idea to use a loop in SQL?

[keakTheGEEK]

Short version. SQL is designed and best optimized for set based computations. Row by Row computations are extremely slow in sql.

[baavgai]

Apokio, on 29 March 2010 - 09:11 AM, said:

The instructor wants certain formatting and that prints all the accounts in one section.

Sad. SQL returns resultsets; formating is irrelevant. Focusing on presentation is a different thing entirely.

I'd use HTML for that kind of thing, which is dead simple. However, for in SQL itself...

Treat each row as a line of text and go from there. Most databases have text conversion functions. This looks like MSSQL, so I'll give an example for that. You can pad left or right, then trim for consistent column sizes.

select '  Account  Description                     Short                   Balance'
select '---------  ------------------------------  --------------- ---------------'
select Line 
from ( 
	SELECT 1 as Seq, Account,
			right(replicate(' ',9)+cast(Account as varchar(9)),9)
			+ '  ' + left(Description+replicate(' ',30),30)
			+ '  ' + left(Short+replicate(' ',15),15)
			+ '  ' + right(replicate(' ',14)+'$'+CONVERT(varchar(15), Balance, 1),15)
			as Line
		FROM ChartofAccounts
		WHERE LEFT(Account,2) between 10 and 61
		GROUP BY Account, Description, ShortDescription, Balance
	union
	SELECT 2 as Seq, Account, 
			'                                           Sub Total       '
			+ right(replicate(' ',14)+'$'+CONVERT(varchar(15), Sum(Balance), 1),15)
			as Line
		FROM ChartofAccounts
		WHERE LEFT(Account,2) between 10 and 61
		GROUP BY Account
	union
	SELECT 3 as Seq, 999999999 as Account, 
			'                                           Grand Total     '
			+ right(replicate(' ',14)+'$'+CONVERT(varchar(15), Sum(Balance), 1),15)
			as Line
		FROM ChartofAccounts
) a
order by Account, Seq

Apokio, on 29 March 2010 - 09:11 AM, said:

Another question because I am ignorant about some things. Why is it a bad idea to use a loop in SQL?

SQL is optimized for queries. A select is always the fastest way to data. Programmers, who are mostly procedural by nature, often use loops. SQL is a declarative languages, more functional than procedural. Loops, or cursors, will always be slower and are sometimes detrimentally so. i.e. looping in SQL is like ordering fish at a steak house.

[keakTheGEEK]

baavgai, on 29 March 2010 - 11:08 AM, said:

i.e. looping in SQL is like ordering fish at a steak house.

HaHa... Great analogy.

[Apokio]

Thanks for the replies. I understand what you are saying. I am glad this SQL class is almost over and I will definitly have to keep on learning some of the more advanced functions. Half of what you wrote in your code wasn't even in our text book. This is just an introduction course so I know there is a lot more that could be covered. Thanks again.