database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
ㆍDB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
DB2 Q&A 1221 게시물 읽기
No. 1221
단순 쿼리같은데 잘안돼네요 ~
작성자
지나(iejina)
작성일
2006-12-21 19:20
조회수
8,923

하려는 쿼리고
from 안에 있는건 성공
전체 다돌리면 error
===================================================================
            select sum(SKT) SV, 0 SW, sum(KTF) KV, 0 KW, sum(LGT) LV, 0 LW from                
            (select sum(st_0) - sum(st_1) SKT, 0 KTF, 0 LGT from mpg_stat_cust            where telco_gb = 'S'                                             
            union                                                            
            select 0 SKT, sum(st_0) - sum(st_1) KTF, 0 LGT from mpg_stat_cust             where telco_gb = 'K'                                             
            union                                                            
            select 0 SKT ,0 KTF, sum(st_0) - sum(st_1) LGT from mpg_stat_cust             where telco_gb = 'L')
===================================================================

 

★from 절뒤부터는 적용됨
db2 => (select sum(st_0) - sum(st_1) SKT, 0 KTF, 0 LGT from mpg_stat_cust  where telco_gb = 'S'  union    select 0 SKT, sum(st_0) - sum(st_1) KTF, 0 LGT from mpg_stat_cust   where telco_gb = 'K'    union        select 0 SKT ,0 KTF, sum(st_0) - sum(st_1) LGT from mpg_stat_cust    where telco_gb = 'L')

SKT         KTF         LGT       
----------- ----------- -----------
          -           0           0
          0           -           0
          0           0           -

  3 record(s) selected.

★그러나 from 앞을붙이면 안됌!!!
db2 =>             select sum(SKT) SV, 0 SW, sum(KTF) KV, 0 KW, sum(LGT) LV, 0 LW from   (select sum(st_0) - sum(st_1) SKT, 0 KTF, 0 LGT from mpg_stat_cust  where telco_gb = 'S'  union    select 0 SKT, sum(st_0) - sum(st_1) KTF, 0 LGT from mpg_stat_cust   where telco_gb = 'K'    union        select 0 SKT ,0 KTF, sum(st_0) - sum(st_1) LGT from mpg_stat_cust    where telco_gb = 'L')
SQL0104N  An unexpected token "" was found following ")".  Expected tokens may
include:  "AS".  SQLSTATE=42601
db2 =>

★-로 인한 에러같아서   COALESCE()함수 적용
===================================================================
            select sum(SKT) SV, 0 SW, sum(KTF) KV, 0 KW, sum(LGT) LV, 0 LW from                
            (select COALESCE(sum(st_0) - sum(st_1),0) SKT, 0 KTF, 0 LGT from mpg_stat_cust            where telco_gb = 'S'                                             
            union                                                            
            select 0 SKT, COALESCE(sum(st_0) - sum(st_1),0) KTF, 0 LGT from mpg_stat_cust             where telco_gb = 'K'                                             
            union                                                            
            select 0 SKT ,0 KTF, COALESCE(sum(st_0) - sum(st_1),0) LGT from mpg_stat_cust             where telco_gb = 'L')
===================================================================


db2 =>             (select COALESCE(sum(st_0) - sum(st_1),0) SKT, 0 KTF, 0 LGT from mpg_stat_cust            where telco_gb = 'S'  union    select 0 SKT, COALESCE(sum(st_0) - sum(st_1),0) KTF, 0 LGT from mpg_stat_cust             where telco_gb = 'K'   union  select 0 SKT ,0 KTF, COALESCE(sum(st_0) - sum(st_1),0) LGT from mpg_stat_cust             where telco_gb = 'L')

SKT         KTF         LGT       
----------- ----------- -----------
          0           0           0

  1 record(s) selected.


★다시 전체 실행 error!!!
  db2 =>             select sum(SKT) SV, 0 SW, sum(KTF) KV, 0 KW, sum(LGT) LV, 0 LW from   (select COALESCE(sum(st_0) - sum(st_1),0) SKT, 0 KTF, 0 LGT from mpg_stat_cust            where telco_gb = 'S'  union     select 0 SKT, COALESCE(sum(st_0) - sum(st_1),0) KTF, 0 LGT from mpg_stat_cust             where telco_gb = 'K'      union  select 0 SKT ,0 KTF, COALESCE(sum(st_0) - sum(st_1),0) LGT from mpg_stat_cust             where telco_gb = 'L')
SQL0104N  An unexpected token "" was found following ")".  Expected tokens may
include:  "AS".  SQLSTATE=42601

이 글에 대한 댓글이 총 3건 있습니다.

select * from (); 
-> 
select * from () as a;

마라도님이 2006-12-22 10:33에 작성한 댓글입니다. Edit
SELECT COALESCE(SUM(CASE WHEN telco_gb = 'S' THEN st_0 - st_1 ELSE 0 END),0) SV ,0 SW
,COALESCE(SUM(CASE WHEN telco_gb = 'K' THEN st_0 - st_1 ELSE 0 END),0) KV ,0 KW
,COALESCE(SUM(CASE WHEN telco_gb = 'L' THEN st_0 - st_1 ELSE 0 END),0) LV ,0 LW
FROM mpg_stat_cust

간단하게 했슴.
박진복(pjb708)님이 2006-12-22 15:34에 작성한 댓글입니다.

감사합니다

잘해결되었습니다 ^____^

happy new year~~~

지나님이 2006-12-26 12:07에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
1227날짜 변화 질문입니다. [1]
지나
2006-12-28
9404
1226AIX5.3.3.0에서 db2v7.1.0.60 지원이 되는지요? [1]
db2초보
2006-12-28
11431
1225DB2 저장시 날짜 변환은 어떻게 하나요? [1]
신참..
2006-12-26
11024
1221단순 쿼리같은데 잘안돼네요 ~ [3]
지나
2006-12-21
8923
1219컴파일 JCL PARM.LKED에 AMODE와 RMODE대해서좀 알려주세요 [3]
CEDF
2006-12-19
8075
1218nickname이 걸린 테이블에도 인덱스 생성 후 효과가 있나여? [1]
db
2006-12-15
8419
1217디비 연결에 대해서 알고 싶습니다
2006-12-14
7158
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.020초, 이곳 서비스는
	PostgreSQL v16.4로 자료를 관리합니다