Select with UNION in where clause subselect

Dev Shed Forums Sponsor:

March 15th, 2013, 03:10 AM

galaxy08151

Registered User

Join Date: Mar 2013

Posts: 2

Time spent in forums: 27 m 57 sec
Reputation 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

March 15th, 2013, 05:48 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,380

Time spent in forums: 3 Months 1 Week 2 Days 7 h 54 m 8 sec
Reputation Power: 4140

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

March 15th, 2013, 07:26 AM

galaxy08151

Registered User

Join Date: Mar 2013

Posts: 2

Time spent in forums: 27 m 57 sec
Reputation 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

March 15th, 2013, 08:09 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,380

Time spent in forums: 3 Months 1 Week 2 Days 7 h 54 m 8 sec
Reputation Power: 4140

Quote:

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?

Select with UNION in where clause subselect

Developer Shed Advertisers and Affiliates