All Forums Database
CCSlice 36 posts Joined 03/14
05 Jun 2014
Failed 3607: Too many expressions in the select list of a query

Hi Everyone,
I am trying to simplify a query that has a couple of subqueries.  Primarily the main subquery (outer) is what I am challenged with at this time.  Although the error code is plain to understand, I am trying to see a way in which I could simplify the query.   Hopefully, a second set of eyes will clarify things.  The query to follow below:
 

SELECT * 
    FROM 
(
SELECT     
                    T1.ENTITY_1, T1.ENTITY_2
                  , T1.ENTITY_3, T1.ENTITY_4, T1.ENTITY_5
                  , T1.ENTITY_6,T2.ENTITY_7
                  , T3.ENTITY_8, T3.ENTITY_9
                                      
   FROM      DATABASE.ACTION_LOG T1,  DATABASE. ACTION_REASON T2
                  , DATABASE.ACTIONS T3
WHERE    T2.ACTION_REASON_ID = T1.ACTION_REASON_ID
      AND     T3.ACTION_ID = T1.ACTION_ID
      AND     T2.VIEW_DT IN (SELECT MAX(VIEW_DT) FROM DATABASE.ACTION_REASON)
      AND     T3.VIEW_DT IN (SELECT MAX(VIEW_DT) FROM  DATABASE.ACTIONS)
      AND   ( T3.WRK_CODE IN ('003')
        OR      T2.WRK_ACTN_REASON_CODE IN (' 006','023') )
 )      AS       L1

WHERE     L1.ENTITY_1 
          IN 
(
SELECT      *
    FROM     DATABASE.BUREAU T4, DATABASE.CUSTOMERS T5
 WHERE     T4.BUREAU_ID = T5.BUREAU_ID
       AND      T4.VIEW_DT IN (SELECT MAX(VIEW_DT) FROM DATABASE.BUREAU) 
 )  

Please forgive the generic names, due to the sensitivity of the data I am working with I cannot disclose the true entity field names. 
 
Many thanks in advance for your help.
 
 

WAQ 158 posts Joined 02/10
09 Jun 2014
WHERE L1.ENTITY_1 IN
(    SELECT *
    FROM DATABASE.BUREAU T4, DATABASE.CUSTOMERS T5
    WHERE T4.BUREAU_ID = T5.BUREAU_ID
    AND T4.VIEW_DT IN (SELECT MAX(VIEW_DT) FROM DATABASE.BUREAU)
)

You are doing L1.ENTITY_1 filter with SELECT * list

CCSlice 36 posts Joined 03/14
11 Jun 2014

Thanks for the reply WAQ.  Great help.

You must sign in to leave a comment.