SQL IF/CASE Statement

By now I’m sure you must have came across multiple situations, where you have to use a IF(in SQL terms CASE) condition. I know I’ve and to be honest I’m actually a big fan of it.

If you are new and want to learn about IF/CASE in SQL, I think you should first start off w/ a basic like this and then to dive into more in depth, try out this and this. These articles are well written w/ easy to follow examples.

Yesterday, I have to create an very interesting query and of course IF/CASE came to the rescue. However, this query involves little bit of calculations so after some web search, I’m glad I found this (and especially the 4th example).

SQL IF/CASE Statement

ColdFusion Casting QueryNew()

I haven’t been a big fan of QueryNew() but today I can across a scenario that could come handy.

But then, I ran into a issue that baffled me.  May be this issue has been addressed by Adobe but on ColdFusion 8, it’s a still there.

The Issue is. when the value is passed say the column type is not supported(something in that nature. I don’t remember the exact message.) But there are solutions for it.

Here is Adobe‘s workaround but as usual I wasn’t satisfied with that.

So after some more searching, I found a solution. It’s a casting issue and can be addressed by,
<cfset x = queryNew("foo,bar","integer,varchar") />

Read more from here.

ColdFusion Casting QueryNew()

sql CTE tables

Recently I had to tackle a some-what impossible task. Well, it’s not impossible-impossible but I wanted to get it done in a more non-traditional way.
Long story short, it involves hierarchy structure converted into a sql rational db, a query can be run to get results.
It may sounds simple in theory but it is a challenging task depending on the complexity of the hierarchy.
So to get some insight and to review my work I came across, something useful yet never knew function in sql.
It’s called ‘Command Table Expressions’ (CTE).
It’s pretty nifty and I’m sure it’ll cone in handy any day now.
Read about it.

sql CTE tables

ajax jquery loop iteration

First things first, yes I did my first ever ajax, jquery functions and must say I think I’m digging it and thinking of using it where ever and when ever possible. I’m so new to this, I don’t even know whether to call it, ajax, jquery or both (idk, that doesn’t matter minor detail).

So long story short, I have a temp table that gets updated using cfdivs on user onClick. Now what I want is, onSubmit to check whether there are corresponding entries in this temp table. So to do this onClick, I have to bring out my newest tool in the arsenal as it involves javascript checking SQL query for values (or recordCounts).

PREPARATION:

I have a cfc called MiceDB.cfc and a component function name  TempTableDateToRealData. Function returntype is query and importantly access type must be set to remote, otherwise jquery ajax call wouldn’t work.

FUNCTION:

(here we go) function is as below. For good programming practices have an error log and have the ResponseID as <div id="ResponseID"></div> somewhere in the BODY so that you can have an output.

showResults = function(vGetSex,vDoWhat){
$.ajax({
url: '../../../MiceDB/DEV/Full/MiceDB.cfc?method=TempTableDateToRealData&getSex='+vGetSex+'&doWhat='+vDoWhat+'&ReturnFormat=json',
dataType: 'json',
success: function(response) {
if(response.DATA.length == 0){
$("#ReturnID").html('No mice' + response.DATA.length);
}
else{
$("#ReturnID").html('Yes we have mice' + response.DATA.length);
}
},
error: function(ErrorMsg) {
console.log('Error');
}
});
}

EXPLAINING:

What’s happening here is when the vGetSex is ‘F’ (or ‘M’) it checks for component return record count. If the recordcount is ResponseID should say ‘No mice’  and vice-versa.

Like I said before, I want to run this OnSubmit and I have to run it twice for one time to check for Female and again for Male.  So the obvious solution was to put in a loop (for loop to be specific) and run/check it twice.

So I included my ajax, jquery function in a for loop (my choice of loop) and to see prominent results instead of having a div to output I added a alert.  Here is what I have now.

// My Variables
var vDoWhat = 'MiceToCage';

for(var i=1; i<3; i++){
switch (i){
case 1:
var vGetSex = 'M';
break;
case 2:
var vGetSex = 'F';
break;
}

$.ajax({
type: 'GET',
url: 'MiceDB.cfc?method=TempTableDateToRealData&getSex='+vGetSex+'&doWhat='+vDoWhat+'&ReturnFormat=json',
dataType: 'json',
success: function(response) {
if(response.DATA.length == 0){
//$("#ResponseID").html('No Male mice' + response.DATA.length);
alert('No Mice' +vGetSex);
}
else{
//$("#ResponseID").html('Yes we have male mice' + response.DATA.length);
alert('Yes Mice' +vGetSex);
}
},
error: function(ErrorMsg) {
console.log('Error');
}
});
}

Strangely enough, this work but if you look closely, results are kinda ‘funky‘ looking (in other words, not accurate). Actually what really gave it away(i.e. the results not being correct) is  having the +response.DATA.length in the alert. 

PROBLEM:

Well you’ll get two alerts as expected but if you look closely both of these have vGetSex is ‘F’ (which is the second iteration value). Which is a problem and got me thinking. It is bizarre and if you put in more alerts you’ll see how iteration skips the ajax call.

After some searching I think I found out how important you should pay attention to the foundation, the basics are. Yes I knew what AJAX stands for and what it meant, but never thought it’ll be this important till I got reinstated by this blog, which was addressing same problem and eventually put me on the right track. So thank you, you humble blogger.

After some inspirational from here I’m was able to get it done. Here is my completed, accurately working code.

CheckEntry = function(){
// My Variables
var doWhat = 'MiceToCage';

//Check for selected Mice (Male and Female)
for(var a=1; a<3; a++){
switch (a){
case 1:
var getSex = 'M';
break;
case 2:
var getSex = 'F';
break;
}
//Calling the Check Function
Check_MiceToCage(getSex,doWhat);
}
}

Check_MiceToCage = function(vGetSex,vDoWhat){
$.ajax({
type: 'GET',
url: 'MiceDB.cfc?method=TempTableDateToRealData&getSex='+vGetSex+'&doWhat='+vDoWhat+'&ReturnFormat=json',
dataType: 'json',
success: function(response) {
if(response.DATA.length == 0){
//$("#ResponseID").html('No Male mice' + response.DATA.length);
alert('No Mice' +vGetSex);
}
else{
//$("#ResponseID").html('Yes we have male mice' + response.DATA.length);
alert('Yes Mice' +vGetSex);
}
},
error: function(ErrorMsg) {
console.log('Error');
}
});
}

Wow all the things I learned from one piece of code.

UPDATE 08/31/2012

It seems like my functions don’t play nice in IE.

ajax jquery loop iteration