MySQL Forums
Forum List  »  MySQL Workbench

Workbench Query Using Complex Variables
Posted by: RUSSELL E WIMBERLEY
Date: October 23, 2016 12:45PM

file: Workbench Query Using Complex Variables


The following two EXAMPLES (QUERY-ONE(a & b) and QUERY-TWO) represent results
as used in MySQL WORKBENCH 6.3 (Version 6.3.6 Build 511 CE (64 bits)

QUERY-ONE-a WORKS with expected results when @org1 is NOT Used in query.
NOTE: "... AND cl_org_cd NOT IN (3,4,5,6,7,8,9) ); "

QUERY-ONE-b DOES NOT WORK with expected results when @org1 is Used in query.
NOTE: "... AND cl_org_cd NOT IN (@org1) ); "


The PROBLEM is "What is the correct construction of QUERY-ONE-a using php and or
MySQL Workbench to obtain a correct result when using @org1 variable
as illustrated in QUERY-ONE-b?


/*  QUERY-ONE-a  */
/*-------------------------------------*/
/*  Workbench WORKS - @org1 NOT Used   */
/* rows to EXCLUDE WHERE @org1= 1 or 2 */
/*-------------------------------------*/
SET @date = '2015-01-01';
SET @org1 = '(3,4,5,6,7,8,9)';

DROP TABLE IF EXISTS tmp_class;

CREATE TABLE IF NOT EXISTS tmp_class AS SELECT * FROM ce_class
    WHERE cl_date >= @date;

SELECT cl_id, cl_date, cl_classname, cl_instructor1, cl_org_cd FROM tmp_class A
    WHERE NOT EXISTS
       (SELECT cl_org_cd FROM tmp_class B WHERE A.cl_id = B.cl_id
          AND cl_org_cd NOT IN (3,4,5,6,7,8,9) );

    /*-------------------------------------------------------------*/
    /* Workbench Returns: Expected (4) records cl_org_cd = 4,3,3,9 */
    /*           cl_org_cd = '4'   1 record  - OK                  */
    /*           cl_org_cd = '3'   2 records - OK                  */
    /*           cl_org_cd = '9'   1 record  - OK                  */
    /* EXCLUDES  cl_org_cd = '1'  24 records - OK                  */
    /* EXCLUDES  cl_org_cd = '2' 117 records - OK                  */
    /*-------------------------------------------------------------*/



/*  QUERY-ONE-b  */
/*---------------------------------------*/
/*  Workbench DOESEN'T WORK - @org1 Used */
/*---------------------------------------*/
SET @date = '2015-01-01';
SET @org1 = '3,4,5,6,7,8,9';

DROP TABLE IF EXISTS tmp_class;

CREATE TABLE IF NOT EXISTS tmp_class AS SELECT * FROM ce_class
    WHERE cl_date >= @date;

SELECT cl_id, cl_date, cl_classname, cl_instructor1, cl_org_cd FROM tmp_class A
    WHERE NOT EXISTS
       (SELECT cl_org_cd FROM tmp_class B WHERE A.cl_id = B.cl_id
          AND cl_org_cd NOT IN (@org1) );
          
    /*-------------------------------------------------------------*/
    /* Workbench Returns: Unexpected (2) records where             */
    /*           cl_org_cd = '3'   2 records - OK                  */
    /* MISSED    cl_org_cd = '4'   1 record  - WRONG               */
    /* MISSED    cl_org_cd = '9'   1 record  - WRONG               */
    /* EXCLUDES  cl_org_cd = '1'  24 records - OK                  */
    /* EXCLUDES  cl_org_cd = '2' 117 records - OK                  */
    /*-------------------------------------------------------------*/



/*  QUERY-TWO  */
/*-----------------------------------*/
/*  Workbench  WORKS - @org1 Used    */
/*  EXCLUDE all rows where org1 = 2; */
/*-----------------------------------*/

SET @date = '2015-01-01';
SET @org1 = '2';    /*  rows to EXCLUDE    */

DROP TABLE IF EXISTS tmp_class;

CREATE TABLE IF NOT EXISTS tmp_class AS SELECT * FROM ce_class
    WHERE cl_date >= @date;
SELECT * FROM tmp_class WHERE cl_date >= @date AND cl_org_cd != @org1;

    /*-------------------------------------------------------------*/
    /* Workbench Returns: CORRECT (28) records where returned      */
    /*           cl_org_cd = '1'  24 records - OK                  */
    /*           cl_org_cd = '3'   2 record  - OK                  */
    /*           cl_org_cd = '4'   1 record  - OK                  */
    /*           cl_org_cd = '9'   1 record  - OK                  */
    /*                                                             */
    /* EXCLUDES  cl_org_cd = '2' 117 records - OK                  */
    /*-------------------------------------------------------------*/

If you have an answer - Please respond to wimbre042@gmail.com
Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Workbench Query Using Complex Variables
1817
October 23, 2016 12:45PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.