그룹바이 항목에서 날짜를 제외시키면 되는거 아닌가요?

 마농님  데이트를 그룹바이에서 제외시켠 not a groupby... 이 에로가 나와서요..ㅜㅜ

그룹바이에 없는 항목이 select 절에 있어서 그렇죠.

Select 절에서도 빼시던가 Min 또는 Max로 처리하시던가 하셔야죠.

WITH TBL1 AS (
              SELECT '567' AUID, 'A' NAME, 'AA' ORG, 'AAA' TEAM, 'AAAA' STEAM , 'AAAA' TITLE, 'B' SHIFT  FROM DUAL UNION ALL
              SELECT '303'     , 'B'     , 'BB'    , 'BBB'     , 'BBBB'       , 'BBBB'      , 'A'        FROM DUAL UNION ALL
              SELECT '959'     , 'C'     , 'CC'    , 'CCC'     , 'CCCC'       , 'CCCC'      , 'C'        FROM DUAL UNION ALL
              SELECT '739'     , 'D'     , 'DD'    , 'DDD'     , 'DDDD'       , 'DDDD'      , 'B'        FROM DUAL UNION ALL
              SELECT '906'     , 'E'     , 'EE'    , 'EEE'     , 'EEEE'       , 'EEEE'      , 'A'        FROM DUAL
             ),
     TBL2 AS (  
              SELECT '567' AUID, '20110901' L_DATE, '1' L_MODE, '074808' L_TIME FROM DUAL UNION ALL
              SELECT '567'     , '20110901'       , '2'       , '075343'        FROM DUAL UNION ALL
              SELECT '303'     , '20110901'       , '1'       , '025304'        FROM DUAL UNION ALL
              SELECT '303'     , '20110901'       , '2'       , '074857'        FROM DUAL UNION ALL
              SELECT '959'     , '20110901'       , '1'       , '221920'        FROM DUAL UNION ALL
              SELECT '959'     , '20110902'       , '2'       , '075017'        FROM DUAL UNION ALL
              SELECT '739'     , '20110901'       , '1'       , '025649'        FROM DUAL UNION ALL
              SELECT '739'     , '20110901'       , '2'       , '075134'        FROM DUAL UNION ALL
              SELECT '906'     , '20110901'       , '1'       , '152449'        FROM DUAL UNION ALL
              SELECT '906'     , '20110901'       , '2'       , '224939'        FROM DUAL
             )                                                                      
SELECT  *
  FROM                                                                                                                                                                                
        (                                                                                                                      
         SELECT                                                                                                                 
                 A.AUID                                                                                                                
                ,A.NAME
                ,A.ORG
                ,A.TEAM
                ,A.STEAM
                ,A.TITLE
                ,A.SHIFT                                                       
                ,MAX(B.L_DATE)
                ,MIN(DECODE(L_MODE, '1', L_TIME)) ATTENT
                ,MAX(DECODE(L_MODE, '2', L_TIME)) LEAVE                           
                ,ROUND(TRUNC(TO_DATE(MIN(DECODE(L_MODE, '2', L_DATE || L_TIME)), 'yyyymmddHH24MISS') -                                  
                             TO_DATE(MIN(DECODE(L_MODE, '1', L_DATE || L_TIME)), 'yyyymmddHH24MISS'),6)*24)  AS W_HOURS                              
           FROM  TBL1 A
                ,TBL2 B                                                               
          WHERE  A.AUID   = B.AUID(+)                                                                                            
            AND  B.L_DATE(+) BETWEEN '20110901' AND '20110902'
            AND  B.L_DATE IS NOT NULL                                                                      
          GROUP  BY A.AUID, A.NAME, A.ORG, A.TEAM, A.STEAM, A.TITLE, A.SHIFT                        
        )                                                                                                                      
 WHERE  ORG LIKE '%'                                                                                                   
   AND  UPPER(STEAM) LIKE '%' || '%'                                                                                  
   AND  SHIFT LIKE '%';

 마농님 강실님 감사합니다.

SQL 쓸때마다 느끼는거지만  안되는게 없는것 같습니다.

다시한번 감사드립니다. ^^