Archives for January 2011 | MacLochlainns Weblog

Archive for January, 2011

Between Vlookup Key

While the VLOOKUP and HLOOKUP functions are powerful tools in Excel, they limit their search to the first column or row of a range. This post shows you how to leverage the COUNTIFS function to perform an inclusive between search against the first two columns or rows of a range.

Moreover, the VLOOKUP function searches the first column of a range and looks for an approximate or exact match. The approximate match search throws an exception when the lookup key’s value precedes the first item in the range, and picks up the row immediately less than the lookup key. This type of search requires two condition. The data must be presorted into an ascending order for the sort column, the range must be contiguous, and two matching keys shouldn’t exist. That means that each search column or row cell points to the row of interest for any search key greater than it and less than the next. An exact match search finds the row that matches the lookup key and throws an error when there isn’t an exact match.

Neither of these allow for range searches between non-contiguous sets, like the one below. The date ranges where a value should be found are from the 16th of a month to the end of a month rather than the range between the 16th of one month to the 15th of the next. While this could be done by structuring a row with zeros the gap periods, a more effective solution is possible by using the COUNTIFS. At least, this is true from Excel 2007 forward.

The solution to this problem starts with recognizing how a COUNTIFS works. The COUNTIF provides the opportunity to compare a range of values against a single value, and the COUNTIFS allows multiple comparisons of values against ranges of values. The COUNTIFS function returns the number of matches that meet all conditions. Therefore, when a value is found in only one of the ranges the COUNTIFS function returns a 1, and when a value is found n times it returns n as a number.

The formula in cell F2 checks for the number of times the value in F1 exists:

=COUNTIFS($A$1:$A$13,"<="&F1,$B$1:$B$13,">="&F1)

If you evaluate when the foregoing function returns 1 before performing a VLOOKUP function, you can guarantee a match within a non-contiguous range of values. That formula is:

=IF(COUNTIFS($A$1:$A$13,"<="&F1,$B$1:$B$13,">="&F1)=1,VLOOKUP(F1,A2:C13,3),0)

Hope this helps some folks, as always …

Written by maclochlainn

January 26th, 2011 at 1:17 am

Posted in Excel 2007,Excel 2008,Excel 2010,Excel 2011,Microsoft Excel,VBA

Prepared Statement Failure

with 3 comments

One of my students asked for some help on developing a MySQL stored procedure that would conditionally drop a foreign key constraint. I put together a quick example, which compiled fine but failed at run time. With some investigation it appears that either the MySQL 5.5 Documentation in Section 12.6 is incorrect or you can’t ALTER TABLE inside a MySQL Prepared Statement with placeholders.

A subsequent test showed me that you couldn’t use ALTER TABLE statement in a prepared statement outside of a stored procedure. I logged Bug #59604 with my other test case, and put the test case there. They closed the bug and validated what I suspected and clarified what I missed, you can’t use placeholders in prepared DDL statements.

The following is the test code example that failed:

DROP PROCEDURE IF EXISTS dropForeignKey;
 
DELIMITER $$
 
CREATE PROCEDURE dropForeignKey
( pv_database   VARCHAR(64)
, pv_table      VARCHAR(64)
, pv_constraint VARCHAR(64))
BEGIN
 
  /* Declare a local variable for the SQL statement. */
  DECLARE stmt VARCHAR(1024);
 
  /* Set a session variable with two parameter markers. */
  SET @SQL := 'ALTER TABLE ? DROP FOREIGN KEY ?';
 
  /* Assign the formal parameters to session variables because prepared statements require them. */
  SET @sv_table := pv_table;
  SET @sv_constraint := pv_constraint;
 
  /* Check if the constraint exists. */  
  IF EXISTS (SELECT NULL
             FROM   information_schema.referential_constraints
             WHERE  constraint_schema = pv_database
             AND    TABLE_NAME = pv_table
             AND    constraint_name = pv_constraint)
  THEN
 
    /* Dynamically allocated and run statement. */
    PREPARE stmt FROM @SQL;
    EXECUTE stmt USING @sv_table, @sv_constraint;
    DEALLOCATE PREPARE stmt;
  END IF;
 
END;
$$
 
DELIMITER ;

Calling this with the following syntax:

CALL dropForeignKey(DATABASE(),'telephone','telephone_fk4');

It raised the following error message.

ERROR 1064 (42000): You have an error IN your SQL syntax; CHECK the manual that corresponds TO your MySQL server version FOR the RIGHT syntax TO USE near '? DROP FOREIGN KEY ?' at line 1

Based on the comment from Bug #59604, I concluded that the ALTER TABLE statement doesn’t support using session variables. However, the CONCAT() function solves the problem. Given this is a DDL command, and any extraneous quoting would simply fail parsing rules because of the CONCAT function, SQL injection doesn’t appear a threat.

DROP PROCEDURE IF EXISTS dropForeignKey;
 
DELIMITER $$
 
CREATE PROCEDURE dropForeignKey
( pv_database  VARCHAR(64)
, pv_table      VARCHAR(64)
, pv_constraint VARCHAR(64))
BEGIN
 
  /* Declare a local variable for the SQL statement. */
  DECLARE stmt VARCHAR(1024);
 
  /* Set a session variable with two parameter markers. */
  SET @SQL := CONCAT('ALTER TABLE ',pv_table,' DROP FOREIGN KEY ',pv_constraint);
 
  /* Check if the constraint exists. */    
  IF EXISTS (SELECT NULL
             FROM   information_schema.referential_constraints
             WHERE  constraint_schema = pv_database
             AND    TABLE_NAME = pv_table
             AND    constraint_name = pv_constraint)
  THEN
 
    /* Dynamically allocated and run statement. */
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
 
END;
$$
 
DELIMITER ;

A more useful approach would be to drop all foreign keys that reference a table. Here’s how you would accomplish that.

-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropForeignKeys;
 
-- Change delimiter to create procedure.
DELIMITER $$
 
-- Create procedure.
CREATE PROCEDURE dropForeignKeys
( pv_database          VARCHAR(64)
, pv_referenced_table  VARCHAR(64))
BEGIN
 
  /* Declare local statement variables. */
  DECLARE lv_stmt VARCHAR(1024);
 
  /* Declare local cursor variables. */
  DECLARE lv_table_name       VARCHAR(64);
  DECLARE lv_constraint_name  VARCHAR(64);
 
  /* Declare control variable for handler. */
  DECLARE fetched       INT DEFAULT 0;
 
  /* Declare local cursor. */
  DECLARE foreign_key_cursor CURSOR FOR
    SELECT   rc.table_name
    ,        rc.constraint_name
    FROM     information_schema.referential_constraints rc
    WHERE    constraint_schema = pv_database
    AND      referenced_table_name = pv_referenced_table
    ORDER BY rc.table_name
    ,        rc.constraint_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Open a local cursor. */  
  OPEN foreign_key_cursor;
  cursor_foreign_key: LOOP
 
    FETCH foreign_key_cursor
    INTO  lv_table_name
    ,     lv_constraint_name;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_foreign_key; END IF;
 
    /* Set a SQL statement by using concatenation. */
    SET @SQL := CONCAT('ALTER TABLE ',lv_table_name,' DROP FOREIGN KEY ',lv_constraint_name);
 
    /* Prepare, run, and deallocate statement. */
    PREPARE lv_stmt FROM @SQL;
    EXECUTE lv_stmt;
    DEALLOCATE PREPARE lv_stmt;
 
  END LOOP cursor_foreign_key;
  CLOSE foreign_key_cursor;  
 
END;
$$
 
-- Reset delimiter to run SQL statements.
DELIMITER ;

You would test it with this call:

CALL dropForeignKeys(DATABASE(),'system_user');

As always, I hope this helps somebody.

Written by maclochlainn

January 19th, 2011 at 1:39 am

Posted in Mac OS X,MAMP,MySQL,PSM

Handling Bash Parameters

with 2 comments

Bash shell or shells in general hang on details. An ex-student was trying to sort something out in Learning the bash Shell, 3^rd Edition, which isn’t on my short list of good shell scripting books. I concur more or less with the comment on Amazon.com that there are too few examples in the book. I think the free examples here may serve folks in lieu of a book.

Anyway, the student’s problem involved processing multiple word parameters in an array. They were confused about how to handle ${*}, ${@} "${*}" and "${@}" when assigning them into an array for subsequent processing. By the way, I did find a decent explanation of the concept on Page 89 in my copy of the book. Though my copy is a 2^nd Edition.

Here’s a dressed up sample of what they were attempting to do:

#!/bin/bash
 
# Print header information.
echo "Begin Program!"
echo ""
 
# Count the call parameters.
echo "Count call parameters [" ${#} "]"
echo ""
 
# Declare an array of delimited parameters.
ARRAY=(${@})
 
# Declare a numeric constant of array elements.
ELEMENTS=${#ARRAY[@]}
 
# Does the parameter account agree with array elements.
if [[ ${#} = ${#ARRAY[@]} ]]; then
  echo "Parameters match exploded array elements."
else
  echo "Parameters ["${#}"] don't match exploded array elements ["${ELEMENTS}"]."
fi
 
# Echo line break.
echo ""
 
# Echo the parameter list.
for (( i = 0; i < ${ELEMENTS}; i++ )); do
  echo "  ARRAY["${i}"]=["${ARRAY[${i}]}"]"
done
 
# Print footer information.
echo ""
echo "End Program!"

With the ARRAY=(${@}) assignment on line #12, they exploded the elements into individual words. They thought that the IFS (Internal Field Separator) environment variable was defined wrong but it wasn’t.

They called the program like this from the command-line:

sample.sh "Me too" "You too"

Then, they got this syntax and were surprised.

Begin Program!
 
Count call parameters [ 2 ]
 
Parameters [2] don't match exploded array elements [4].
 
  ARRAY[0]=[Me]
  ARRAY[1]=[too]
  ARRAY[2]=[You]
  ARRAY[3]=[too]
 
End Program!

They were close. The ARRAY=(${@}) assignment on line #12. There attempt to fix it with ARRAY=(${*}) led nowhere because it’s more or less the same and explodes into 4 words. To their credit, they put quotes around it like this ARRAY=("${*}") and got two parameters but one array element, as shown below:

Begin Program!
 
Count call parameters [ 2 ]
 
Parameters [2] don't match exploded array elements [1].
 
  ARRAY[0]=[Me too You too]
 
End Program!

What they needed was ARRAY=("${@}") on line #12 to explode quote delimited parameters. Here’s a complete working example of the final code.

#!/bin/bash
 
# Print header information.
echo "Begin Program!"
echo ""
 
# Count the call parameters.
echo "Count call parameters [" ${#} "]"
echo ""
 
# Declare an array of delimited parameters.
ARRAY=("${@}")
 
# Declare a numeric constant of array elements.
ELEMENTS=${#ARRAY[@]}
 
# Does the parameter account agree with array elements.
if [[ ${#} = ${#ARRAY[@]} ]]; then
  echo "Parameters match exploded array elements."
else
  echo "Parameters ["${#}"] don't match exploded array elements ["${ELEMENTS}"]."
fi
 
# Echo line break.
echo ""
 
# Echo the parameter list.
for (( i = 0; i < ${ELEMENTS}; i++ )); do
  echo "  ARRAY["${i}"]=["${ARRAY[${i}]}"]"
done
 
# Print footer information.
echo ""
echo "End Program!"

Changing that one element yields their desired output:

Begin Program!
 
Count call parameters [ 2 ]
 
Parameters match exploded array elements.
 
  ARRAY[0]=[Me too]
  ARRAY[1]=[You too]
 
End Program!

As always, I hope this helps some folks.

Written by maclochlainn

January 2nd, 2011 at 1:40 am

Posted in bash,Linux,Mac OS X,Ubuntu

MacLochlainns Weblog

Archive for January, 2011

Between Vlookup Key

Prepared Statement Failure

Handling Bash Parameters

Recent Posts

Things Written About

Pages

Blogroll

Archives