#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    2
    Rep Power
    0

    Select with UNION in where clause subselect


    Hello,

    I have the following Query which is not working, but I do not see where the issue is comming from.
    The query has 2 subselects in the where clause part (1 of them with UNION) and the MySQL database cannot execute it.


    [MYSQL]SELECT
    FUND_DATA.FUND
    ,FUND_DATA.FUND_NAME
    FROM
    ((SELECT FUND
    ,CASE
    WHEN ACCTCATEGORY.CATTYPE_ACT IN (
    '20'
    ,'21'
    ,'22'
    )
    THEN 'Retail'
    ELSE 'Institutional'
    END AS DESIGNATION_BUSINESS_TYPE
    FROM ACCTCATEGORY AS ACCTCATEGORY
    INNER JOIN TRANSACTION AS TRANSACTION ON ACCTCATEGORY.ACCOUNT = TRANSACTION.ACCOUNT
    AND ACCTCATEGORY.CATTYPE_ACT IN (
    '20'
    ,'21'
    ,'22'
    ,'23'
    )
    AND TRANSACTION.DEFF BETWEEN DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 2 MONTH)), INTERVAL 1 DAY)
    AND LAST_DAY(CURDATE() - INTERVAL 1 MONTH)
    AND TRANSACTION.TRANSTYPE IN (
    'ED'
    ,'TI'
    ,'AW'
    ,'PW'
    ,'TO'
    )
    GROUP BY FUND
    ,CASE
    WHEN ACCTCATEGORY.CATTYPE_ACT IN (
    '20'
    ,'21'
    ,'22'
    )
    THEN 'Retail'
    ELSE 'Institutional'
    END
    UNION
    SELECT FUND
    ,CASE
    WHEN ACCTCATEGORY.CATTYPE_ACT IN (
    '20'
    ,'21'
    ,'22'
    )
    THEN 'Retail'
    ELSE 'Institutional'
    END AS DESIGNATION_BUSINESS_TYPE
    FROM ACCTCATEGORY AS ACCTCATEGORY
    INNER JOIN ACCOUNTVALU AS ACCOUNTVALU ON ACCTCATEGORY.ACCOUNT = ACCOUNTVALU.ACCOUNT
    AND ACCTCATEGORY.CATTYPE_ACT IN (
    '20'
    ,'21'
    ,'22'
    ,'23'
    )
    GROUP BY FUND
    ,CASE
    WHEN ACCTCATEGORY.CATTYPE_ACT IN (
    '20'
    ,'21'
    ,'22'
    )
    THEN 'Retail'
    ELSE 'Institutional'
    END) AS FUND_DESIGNATION_COMBINATION INNER JOIN
    (SELECT FUND.FUND
    ,TRIM(CONCAT (
    FUND.ENGLISHLONG
    ,FUND.ENGLISHLONG2
    )) AS FUND_NAME
    FROM FUND AS FUND
    GROUP BY FUND.FUND) AS FUND_DATA ON FUND_DESIGNATION_COMBINATION.FUND = FUND_DATA.FUND)[/MYSQL]

    Error message I am getting is: "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 ') AS FUND_DESIGNATION_COMBINATION INNER JOIN (SELECT FUND.FUND ,TRIM(CONCA' at line 1"


    I can execute each single subselect without any issue, but as soon as I try to execute the complete query, it is not working. I did as well a different test. When I delete from the union subquery the 2nd part including the union I am as well able to execute the whole part, but can anyone tell me why it is not working as it is written above?

    FYI - this query is just a part of my main query. In the main query I have 4 subselects in the where clause which I join via left or inner join. this is working fine except for the above mentioned union part.


    Thanks very much in advance.

    Thanks,
    Martin
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    try removing the outer parentheses in the FROM clause
    Code:
    SELECT fund_data.fund
         , fund_data.fund_name
      FROM ( SELECT fund
                  , CASE WHEN acctcategory.cattype_act IN ('20','21','22')
                         THEN 'retail'
                         ELSE 'institutional'
                     END AS designation_business_type
               FROM acctcategory
             INNER 
               JOIN transaction
                 ON acctcategory.account = transaction.account
                AND acctcategory.cattype_act IN ('20','21','22','23')
                AND transaction.deff 
                    BETWEEN DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 2 MONTH)), INTERVAL 1 DAY)
                        AND LAST_DAY(CURDATE() - INTERVAL 1 MONTH)
                AND transaction.transtype IN ('ED','TI','AW','PW','TO')
             GROUP 
                 BY fund
                  , CASE WHEN acctcategory.cattype_act IN ('20','21','22')
                         THEN 'retail'
                         ELSE 'institutional'
                     end
             UNION
             SELECT fund
                  , CASE WHEN acctcategory.cattype_act IN ('20','21','22')
                         THEN 'retail'
                         ELSE 'institutional'
                     END AS designation_business_type
               FROM acctcategory
             INNER 
               JOIN accountvalu
                 ON acctcategory.account = accountvalu.account
                AND acctcategory.cattype_act IN ('20','21','22','23')
             GROUP 
                 BY fund
                  , CASE WHEN acctcategory.cattype_act IN ('20','21','22')
                         THEN 'retail'
                         ELSE 'institutional'
                     END
           ) AS fund_designation_combination 
    INNER 
      JOIN ( SELECT fund.fund
                  , TRIM(CONCAT(fund.englishlong,fund.englishlong2)) AS fund_name
               FROM fund
             GROUP 
                 BY fund.fund
           ) AS fund_data 
        ON fund_designation_combination.fund = fund_data.fund
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    2
    Rep Power
    0
    Hi,

    THANKS!!!! That solved my issue. I could not find this issue at all when I was checking the query

    But one question on this. When I remove the union in the first subselect including the second query of the union, everything works as well with those parentheses. I have as well a different query with 4 subselcts (without a union) + parentheses and all works fine.

    Really seems to be an issue using a union subselct + no union subselect + parentheses

    Thanks again!
    Martin
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    Originally Posted by galaxy08151
    But one question on this. When I remove the union in the first subselect including the second query of the union, everything works as well with those parentheses. I have as well a different query with 4 subselcts (without a union) + parentheses and all works fine.
    weird, eh?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo