|
dbexpress 에서 sqlquery 의 sql 문의 한계는?
현재 시스템 환경은
mssql2000 server
delphi 7.0 입니다.
bde 의 query component 로 되어 있는 프로그램을 dbexpress 로 수정중에
조금 복잡한 쿼리문를 돌리면
ado,bde 에서는 잘되는 query문이 dbexpress 의 sqlquery, SQLDataSet1 에서는
error 가 발생
sql state 42000
error code = 0
이런 에러가 발생함니다.
같은 환경에 간단한 query를 보내면 정상적으로 동작함니다.
dbexpress 에서는 안되는 query 문이 있는지 아님 다른 무언가를 설정해야하는지
고수분의 도움을 기다립니다.
아래의 문장은 bde,ado 에선 정상적으로 작동됨니다.
혹시라고 제가 편집해서 올리는중에 잘못 올라갈수는 있으나
bde, ado 에선 정상이다는 걸 감안해서 봐주세요..
sql.add(' SELECT a.room as room ,');
sql.add(' a.name as name ,');
sql.add(' a.jumin as jumin ,');
sql.add(' a.jongbeul as jongbeul ,');
sql.add(' a.jewon_day as jewon_day ,');
sql.add(' a.chart as chart ,');
sql.add(' b.sa_name as sa_name ,');
sql.add(' a.doc_code as doc_code ,');
sql.add(' a.gub_gu as gub_gu ,');
sql.add(' a.kam_rate as kam_rate ,');
sql.add(' a.taewon_day as taewon_day ,');
sql.add(' a.jin_gu as jin_gu ,');
sql.add(' a.gongsang as gongsang ,');
sql.add(' a.jewon_id as jewon_id ,');
sql.add(' a.kwamok as kwamok , ');
sql.add(' c.kwamok as p_kwamok , ');
sql.add(' K.kw_ENG as kw_ENG , ');
sql.add(' max_hang1, max_hang2, max_hang3, max_hang4, max_hang5, max_hang6, ');
sql.add(' max_hang7, max_hang8, max_hang9, max_hang10, max_hang11, max_hang12, ');
sql.add(' C.JIN_DAY, isnull(C.KWAMOK,'''') as c_kwamok, C.MAX_ORDER_ILSU, c.day_repeat , ');
sql.add(' (SELECT COUNT(CHART) FROM CHART_S CS ');
sql.add(' WHERE a.JEWON_ID = CS.JEWON_ID ');
sql.add(' AND a.CHART = CS.CHART ');
sql.add(' AND CS.kw_code =''D'' ');
sql.add(' AND (CS.EDIT_CHK = ''N'' OR CS.EDIT_CHK =''C'' )) AS APPEND_EDIT_CNT, ');
sql.add(' (SELECT COUNT(CHART) FROM CHART_P AC ');
sql.add(' WHERE a.JEWON_ID = AC.JEWON_ID ');
sql.add(' AND a.CHART = AC.CHART ');
sql.add(' AND ac.kw_code =''D'' ');
sql.add(' AND (AC.RECEIVER IS NULL OR AC.RECEIVER ='''' )) AS NOT_RECEIVER_CNT, ');
sql.add(' (SELECT COUNT(CHART) FROM CHART_P BC ');
sql.add(' WHERE a.JEWON_ID = BC.JEWON_ID ');
sql.add(' AND a.CHART = BC.CHART ');
sql.add(' AND bc.kw_code =''D'' ');
sql.add(' AND (BC.RECEIVER IS NOT NULL OR BC.RECEIVER <> '''' )) AS RECEIVER_CNT, ');
sql.add(' (SELECT COUNT(CHART) FROM CHART_P DC ');
sql.add(' WHERE a.JEWON_ID = DC.JEWON_ID ');
sql.add(' AND a.CHART = DC.CHART ');
sql.add(' AND DC.kw_code = :KW_CODE ');
sql.add(' AND DC.JIN_DAY = '''+CURR_JINDAY+''' ');
sql.add(' AND DC.KWAMOK = C.KWAMOK ) AS ORDER_CNT , ');
sql.add(' (SELECT ISNULL(max(jin_day),0) FROM CHART_P AD ');
sql.add(' WHERE a.JEWON_ID = AD.JEWON_ID ');
sql.add(' AND a.CHART = AD.CHART ');
sql.add(' AND aD.kw_code =:KW_CODE ');
sql.add(' AND AD.JIN_DAY = C.JIN_DAY ');
sql.add(' AND AD.OPDAY_CHK = ''1'' ) AS MAX_OPDAY ');
sql.add(' FROM jewon a ');
sql.add(' LEFT OUTER JOIN SAWON B ');
sql.add(' ON B.SA_KEY = A.DOC_CODE ');
sql.add(' LEFT OUTER JOIN CHART_P C ');
sql.add(' ON C.JEWON_ID = A.JEWON_ID ');
sql.add(' AND C.JIN_DAY = (SELECT MAX(JIN_DAY) FROM CHART_P CC ');
sql.add(' WHERE a.JEWON_ID = CC.JEWON_ID ');
sql.add(' AND a.CHART = CC.CHART ');
sql.add(' AND CC.JIN_DAY <= ''' + s_jin_day + ''' ');
sql.add(' AND Cc.kw_code = :KW_CODE ) ');
sql.add(' AND C.DUP = (SELECT MAX(DUP) FROM CHART_P CC ');
sql.add(' WHERE a.JEWON_ID = CC.JEWON_ID ');
sql.add(' AND a.CHART = CC.CHART ');
sql.add(' AND C.JIN_DAY = CC.JIN_DAY ');
sql.add(' AND Cc.kw_code = :KW_CODE ) ');
sql.add(' AND c.kw_code = :KW_CODE ');
sql.add(' LEFT OUTER JOIN KWAMOK K ');
sql.add(' ON K.KW_KEY = a.KWAMOK ');
sql.add(' WHERE a.sunap = ''1'' and taewon_gu <> ''3'' ');
sql.add(' AND a.jewon_day <= '''+s_jin_day+''' ');
sql.add(' AND a.room IN (SELECT room FROM byungsil WHERE byungdong = '''+s_byungdong+''') ');
sql.add(' ORDER BY a.room , A.CHART ');
|
