SQL Counting with Conditions

Not sure why I never thought of this before (and I know I'm not the first to come to this conclusion judging by Google), but I just figured out how to count records in a query based on their value. Let's assume that you have a person table, and a property table. Each person has an unlimited number of properties which are either active or inactive, and you need to figure out how many they have of each. I've typically done this by using subselects or derived queries, but this is heavy on the server and requires a lot of additional table locking when you're not careful. So instead of doing something like this: SELECT Person.Name , Count(SELECT ID FROM Property WHERE PersonID = Person.ID AND Active = 1) AS ActiveProperty , Count(SELECT ID FROM Property WHERE PersonID = Person.ID AND Active = 0) AS Inactive Property FROM Person You can do this: SELECT Person.Name , SUM(CASE WHEN Property.Active = 1 THEN 1 ELSE 0 END) AS ActiveProperty , SUM(CASE WHEN Property.Active = 0 THEN 1 ELSE 0 END) AS InactiveProperty FROM Person INNER JOIN Property ON Person.ID = Property.PersonID The second query is easier to read, and far easier on the database itself.
4 comments | Posted by Daniel Short on Apr 9, 2009 at 9:57 AM | Categories: ColdFusion -

Incorrect Variable Values in AjaxOnLoad

I just ran into another goofy Ajax problem with my ColdFusion apps. I have a page using a bound cfdiv to load a form. The form, once submitted, is supposed to take the value of a form field entered on the page and run an AjaxOnLoad event to use that value elsewhere. Here's a simple test to show what I mean. First, create the main page, test.cfm: Now in form.cfm, add the following code:

When you submit the form, you'll get the value 2 alerted. I thought I would try to be sneaky and add an if statement around the script so that it was only output when the value wasn't 2, like so:

That just caused a javascript error that it couldn't find the testFunction script. So this tells me that the script is rendered to the page when the div is first loaded, and it's not updated on subsequent div reloads. So my way around this is to put the new value into a hidden form field, and then use ColdFusion.getElementValue() to pick up the value for my testFunction. Hope this saves someone else some frustration.
0 comments | Posted by Daniel Short on Mar 31, 2009 at 10:02 AM | Categories: ColdFusion -

Error Building Struct with CreateUUID

This is more for myself than anyone else, but I hit an odd snag in ColdFusion this morning. I got an error when trying to create a new structure member using a CreateUUID() function and ColdFusion's shorthand structure notation. The following code fails: The error I receive is that "Element 435A2A8C-0B35-C81F-4F36BE50E1B8F394 is undefined in a CFML structure referenced as part of an expression." Well duh! I'm trying to create it! So I changed the code to this, which works perfectly: Does anyone have any idea why using the CreateUUID() function directly in the assignment statement would cause a problem?
7 comments | Posted by Daniel Short on Mar 26, 2009 at 10:18 AM | Categories: ColdFusion -