: : : i want to count and display the number of order numbers associated with each state. some states do not have any orders and therefore are null, and i need to display every state. so when i do a select count(order_num), state_code from ...... the states that have no orders are null and not displayed. can i solve this matter in my select statement, or will i have to do it in a procedure?
: : :
: : Hi,
: : It depends where you are running this SQL Statement. If you are doing it in a VB Code module then you will have to test for Null on the return value and replace it with zero.
: : If, however, you are running this in a SQL-Server Stored Procedure then you can use "IsNull" in the Select statement - SELECT @Count = IsNull( (count.......) , 0)
: : Whatever you place in the second part after the comma is returned if the firstpart is null. In your case you want it to be Zero.
: :
: : im using this in powerbuilder and its accessing a sql server db
: :
: :
Have not use powerbuilder so I'm not sure what your return is, presumably you feed the return to a variable, so you just need to put a line of code in that says :
If IsNull(varReturn) then
varReturn = 0
End If
However, if you are running it as a whole batch and retrieving a Recordset then presumably you are missing entries for those with no count, so you could either get a list of state-code then loop through one at a time so that you can make the above test, or construct the SQL String with the IsNull in it and then pass that to sp_executesql to return a full recordset.
In a World full of blind men the one-eyed man is King
