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

SQL query result into a table

I find this pretty useful plenty of time.

First an INSERT,

<cfquery name="Into_tempWorkHistory" datasource="myDataSource">
INSERT INTO toTable(WONUM, WorkerType, Worker, WorkStartDate)
SELECT WONUM, WorkerType, Worker, WorkStartDate
FROM fromTable
WHERE WONUM = '#ARGUMENTS.WONUM#'
</cfquery>

Second, an UPDATE could be also done via query result, read more

UPDATE TABLE1
SET WorkEndDate = T.Date
FROM TABLE2 T2
WHERE T2.WONUM = '#ARGUMENTS.WONUM#' AND T2.Action = 'R' AND T2.WorkerType = '#WorkerType#'
AND T2.Worker IN (SELECT T1.Worker
FROM TABLE1 T1
WHERE T1.WorkerType = '#WorkerType#'
AND T1.WONUM = '#ARGUMENTS.WONUM#')

*NOTE: Make sure not to use ALIAS on the UPDATE. And above UPDATE query is a mere example only, above update will do the job however result query doesn’t behave as expexted .

Added Bonus: I also found some nifty new (SQL 2005 and above) features. Have a look.

SQL query result into a table

Dynamically get SQL query result row numbers

I think this is super useful. Here is my example,

<cfquery name="qGetTHIS_FBS" datasource="myDataSource">
SELECT ROW_NUMBER() OVER(ORDER BY M.Item_Nm DESC) AS rNo, M.Item_Nm, M.Manufacturer, M.Description, M.CaseSize, M.Unit_Desc, M.Case_Desc, F.VolumeQTY, F.UOM, P.UnitSize
FROM M_Table M, P_Table P, F_Table F
WHERE M.Item_Nm = P.ItemNo AND F.PO = P.PO AND F.PO = '#ARGUMENTS.PO#' AND M.InUse = 'Y'
<cfquery>

Read more and learn more from here.

Dynamically get SQL query result row numbers

Clean Water For All Campaign

I started this last year for my birthday and I think it’s something I’m going to continue.
This year (2012), I had a successful first half and soon I’ll be starting my second half of the year.

Soon (when available to me) I’ll be updating this post with the progress report of the project, how the money is spent.

As always any support is greatly appreciated.

Clean Water For All Campaign

SQL connecting One Query to another

Just by looking at it seems like this is just an SQL join (or in Coldfusion just a QoQ) but I ran into a complication because one query contains SQL arithmatic and other doesn’t. Obviously SQL JOIN is also out of the picture.

So this is my solution,

<cfquery name="q1" datasource="myDataSource">
 SELECT T1.ItemNo, T1.UnitSize, T2.uCount, (T1.UnitSize * T2.uCount) AS ItemUsedAmt
 FROM TABLE1 AS T1,
    (SELECT DISTINCT ItemNo, SUM(UnitCount) AS uCount
    FROM TABLE2
    WHERE PO = '#ARGUMENTS.PO#'
    GROUP BY ItemNo) AS T2
 WHERE T2.ItemNo = T1.ItemNo 
 </cfquery>

(my answer is inspired with internet research and I wish i wrote down the website that lead me to this solution.)

SQL connecting One Query to another