{"id":1666,"date":"2015-07-23T23:09:33","date_gmt":"2015-07-24T04:09:33","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1666"},"modified":"2015-07-23T23:48:57","modified_gmt":"2015-07-24T04:48:57","slug":"decoding-sql-grammar-select-sql-statement-with-union-intersect-and-except-set-operators","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/decoding-sql-grammar-select-sql-statement-with-union-intersect-and-except-set-operators\/","title":{"rendered":"Decoding SQL grammar — Select SQL Statement with UNION, INTERSECT, and EXCEPT set operators"},"content":{"rendered":"

In order to decode Select SQL Statement with UNION, INTERSECT, and EXCEPT set operators, you need to know how Select SQL Statement with set operators was organized after parsing by GSP library.<\/p>\n

Take this SQL for example:<\/p>\n

####

####

    \r\nSELECT 1 from dual  -- first part\r\nunion \r\nSELECT 2 from dual  -- second part\r\n<\/pre>\nThere are some properties in TSelectSqlStatement help you to judge whether a Select SQL Statement including set operator or not, if yes, how to get two separate query expressions which is type of TSelectSqlStatement too.  <\/p>\nGSP Java version of TSelectSqlStatement<\/h4>\npublic boolean isCombinedQuery()<\/strong>, this function return true if set operator is used, otherwise, return false. If it’s a Select SQL Statement with set operators, you can’t get select list, from clause and other clause from this statement directly. You should get two separate query expressions by using getLeftStmt() and getRightStmt() which can be a Select SQL Statement with set operators recursively.<\/p>\npublic int getSetOperator()<\/strong>, return type of following set operators.<\/p>\n    public static final int setOperator_none = 0;
    \n    public static final int setOperator_union = 1;
    \n    public static final int setOperator_unionall = 2;
    \n    public static final int setOperator_intersect = 3;
    \n    public static final int setOperator_intersectall = 4;
    \n    public static final int setOperator_minus = 5;
    \n    public static final int setOperator_minusall = 6;
    \n    public static final int setOperator_except = 7;
    \n    public static final int setOperator_exceptall = 8;<\/p>\npublic TSelectSqlStatement getLeftStmt()<\/strong>, return first part of two separate query expressions.<\/p>\npublic TSelectSqlStatement getRightStmt()<\/strong>, return second part of two separate query expressions.<\/p>\nGSP .NET version of TSelectSqlStatement<\/h4>\nSelectSetType<\/strong> is type of TSelectSetType. If value is sltNone, then it’s an ordinary Select SQL Statement, otherwise, it’s a Select SQL Statement with set operators.<\/p>\nTSelectSetType = (sltNone,sltUnion,sltUnionAll,sltMinus,sltIntersect,sltIntersectAll,sltExcept,sltExceptAll);
    \n<\/strong><\/p>\nLeftStmt<\/strong> is type of TSelectSqlStatement, return the first part of two separate query expressions.<\/p>\nRightStmt<\/strong> is type of TSelectSqlStatement, return the second part of two separate query expressions.<\/p>\nOnce you get a Select SQL Statement with no set operator, you can decode Select SQL Statement illustrated as this document<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"In order to decode Select SQL Statement with UNION, INTERSECT, and EXCEPT set operators, you need to know how Select SQL Statement with set operators was organized after parsing by GSP library. Take this SQL for example: SELECT 1 from dual — first part union SELECT 2 from dual — second part There are some properties in TSelectSqlStatement help you to judge whether a Select SQL Statement including set operator or not, if yes, how to get two separate query expressions which is type of TSelectSqlStatement too. GSP Java version of TSelectSqlStatement public boolean isCombinedQuery(), this function return true if\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":235,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n