Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    1

    Unanswered: Use Parameter to SELECT Column

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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)
    WHILE currAttribute <> '' DO
    I think that you should check "NOT FOUND" condition to finish the loop.
    (with adding "DECLARE CONTINUE HANDLER FOR NOT FOUND ...")

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;
    Last edited by tonkuma; 03-12-11 at 12:31. Reason: Reduce redundancy by adding ", LATERAL(VALUES ...) q(currAttribute)".

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why did you added "2" to totalForAttribute and "1" to positiveForAttribute and negativeForAttribute?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •