Use Parameter to SELECT Column

BobTheDbBuilder · #1 (**permalink**) 03-11-11, 14:35

I have the procedure below. If I replace all of the 'columnName's in the body of the procedure with an actual column name from my table, then it works exactly as needed. What I want to do is be able to pass the column name in as a parameter, so I can call this procedure for all applicable columns. However, when I try to do that, the cursor continuously returns whatever the value of the parameter is as opposed to the distinct values in the parameterized column of the database.

CREATE PROCEDURE PopulateNbc ( IN columnName VARCHAR (50) )
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE currAttribute VARCHAR(50);
DECLARE totalForAttribute DOUBLE;
DECLARE positiveForAttribute DOUBLE;
DECLARE negativeForAttribute DOUBLE;

-- Declare cursor
DECLARE attributeCursor CURSOR WITH RETURN for
SELECT distinct(columnName) FROM TrainSet;

-- Cursor left open for client application
OPEN attributeCursor;

FETCH attributeCursor INTO currAttribute;
WHILE currAttribute <> '' DO
SET totalForAttribute = CAST(((SELECT count(columnName) FROM TrainSet WHERE columnName=currAttribute) + 2) AS DOUBLE);
SET positiveForAttribute = CAST(((SELECT count(columnName) FROM TrainSet WHERE columnName=currAttribute AND class='>50K') + 1) AS DOUBLE);
SET negativeForAttribute = CAST(((SELECT count(columnName) FROM TrainSet WHERE columnName=currAttribute AND class='<=50K') + 1) AS DOUBLE);

INSERT INTO NBC(column_name, attribute_name, positive, negative) VALUES
(columnName, currAttribute, positiveForAttribute/totalForAttribute, negativeForAttribute/totalForAttribute);

FETCH attributeCursor INTO currAttribute;
END WHILE;

-- Close the cursor.
CLOSE attributeCursor;
END P1

ARWinner · #2 (**permalink**) 03-11-11, 14:59

You need to use dynamic SQL to do what you want. You will have to build the SQL select statement and the execute it.

Andy

tonkuma · #3 (**permalink**) 03-11-11, 19:19

1) As the table is fixed to "TrainSet",
then you must know the names of the columns of the table.
If so, you can code without using dynamic SQL, like this...

Code:

DECLARE attributeCursor CURSOR WITH RETURN for
SELECT DISTINCT
       CASE columnName
       WHEN 'column_a' THEN column_a
       WHEN 'column_b' THEN column_b
       ...
       END
  FROM TrainSet;

2)

Quote:

WHILE currAttribute <> '' DO

I think that you should check "NOT FOUND" condition to finish the loop.
(with adding "DECLARE CONTINUE HANDLER FOR NOT FOUND ...")

tonkuma · #4 (**permalink**) 03-11-11, 19:55

I thought that your procedure body could be written with an INSERT statement, like this ...

Code:

INSERT INTO NBC
     ( column_name
     , attribute_name
     , positive
     , negative
     )
SELECT columnName
     , currAttribute
     , positiveForAttribute / totalForAttribute
     , negativeForAttribute / totalForAttribute
  FROM (SELECT currAttribute
               CAST( COUNT(*) + 2 AS DOUBLE) AS totalForAttribute
             , CAST( COUNT( CASE class
                            WHEN '>50K'  THEN 0
                            END
                          )   + 1 AS DOUBLE) AS positiveForAttribute
             , CAST( COUNT( CASE class
                            WHEN '<=50K' THEN 0
                            END
                          )   + 1 AS DOUBLE) AS negativeForAttribute
          FROM TrainSet
             , LATERAL
               (VALUES CASE columnName
                       WHEN 'column_a' THEN column_a
                       WHEN 'column_b' THEN column_b
                       ...
                       END
               ) q(currAttribute)
         GROUP BY
               currAttribute
       ) r
;

tonkuma · #5 (**permalink**) 03-11-11, 19:56

Why did you added "2" to totalForAttribute and "1" to positiveForAttribute and negativeForAttribute?

tonkuma · #6 (**permalink**) 03-15-11, 14:23

If I gave full play to my imagination,
you might want to insert into NBC the rows corresponding to some columns of TrainSet.

And if you can list up the column names like ('column_a', 'column_b', 'column_c', ...),
the INSERT statements could be made into one statement by using the list of the column names,
like ...

Code:

INSERT INTO NBC
     ( column_name
     , attribute_name
     , positive
     , negative
     )
SELECT columnName
     , currAttribute
     , positiveForAttribute / totalForAttribute
     , negativeForAttribute / totalForAttribute
  FROM (SELECT columnName
             , currAttribute
             , CAST( COUNT(*) + 2 AS DOUBLE) AS totalForAttribute
             , CAST( COUNT( CASE class
                            WHEN '>50K'  THEN 0
                            END
                          )   + 1 AS DOUBLE) AS positiveForAttribute
             , CAST( COUNT( CASE class
                            WHEN '<=50K' THEN 0
                            END
                          )   + 1 AS DOUBLE) AS negativeForAttribute
         FROM  (VALUES 'column_a', 'column_b', 'column_c', ...) p(columnName)
             , TrainSet
             , LATERAL
               (VALUES CASE columnName
                       WHEN 'column_a' THEN column_a
                       WHEN 'column_b' THEN column_b
                       ...
                       END
               ) q(currAttribute)
         GROUP BY
               columnName
             , currAttribute
       ) r
;

Internet Services

Web Development

Digital Marketing

Consumer Tech