==================== 소스코드 Start ==============================
codeCheck = "211" '연관되는 코드 010, 231, 112
if codeCheck = "211" then
unionKey = "1"
end if
Sql = " SELECT DISTINCT D.field1 AS fieldName,B.field2,B.field3, COUNT(*) AS CNT, B.field4, B.field5, C.field6, A.field7, "
Sql = Sql & " C.field8 , C.field9, C.field10, C.field11, C.field12, C.field13, C.field14, C.field15"
Sql = Sql & " FROM table1 A, table2 B, table3 C, table4 D "
Sql = Sql & " WHERE (A.afield1 = 'N')"
Sql = Sql & " AND (A.afield1 = 'N')"
Sql = Sql & " AND (A.afield2 = B.bfield2)"
Sql = Sql & " AND (A.afieldCode = '211')"
Sql = Sql & " AND (A.afieldCode = C.cfieldCode) "
Sql = Sql & " AND (B.bfield3 = D.dfield3) "
Sql = Sql & " GROUP BY D.field1,B.field2,B.field3, B.field4, B.field5, C.field6, A.field7, "
Sql = Sql & " C.field8 , C.field9, C.field10, C.field11, C.field12, C.field13, C.field14, C.field15"
Sql = Sql & " ORDER BY A.afieldCode, D.field1, B.field2 "
IF (unionKey = "1") and codeCheck = "211" THEN
Sql = Sql & " union all"
Sql = " SELECT DISTINCT D.field1 AS fieldName,B.field2,B.field3, COUNT(*) AS CNT, B.field4, B.field5, C.field6, A.field7, "
Sql = Sql & " C.field8 , C.field9, C.field10, C.field11, C.field12, C.field13, C.field14, C.field15"
Sql = Sql & " FROM table1 A, table2 B, table3 C, table4 D "
Sql = Sql & " WHERE (A.afield1 = 'N')"
Sql = Sql & " AND (A.afield1 = 'N')"
Sql = Sql & " AND (A.afield2 = B.bfield2)"
Sql = Sql & " AND (A.afieldCode = '010')"
Sql = Sql & " AND (A.afieldCode = C.cfieldCode) "
Sql = Sql & " AND (B.bfield3 = D.dfield3) "
Sql = Sql & " GROUP BY D.field1,B.field2,B.field3, B.field4, B.field5, C.field6, A.field7, "
Sql = Sql & " C.field8 , C.field9, C.field10, C.field11, C.field12, C.field13, C.field14, C.field15"
Sql = Sql & " ORDER BY A.afieldCode, D.field1, B.field2 "
Sql = Sql & " union all"
Sql = " SELECT DISTINCT D.field1 AS fieldName,B.field2,B.field3, COUNT(*) AS CNT, B.field4, B.field5, C.field6, A.field7, "
Sql = Sql & " C.field8 , C.field9, C.field10, C.field11, C.field12, C.field13, C.field14, C.field15"
Sql = Sql & " FROM table1 A, table2 B, table3 C, table4 D "
Sql = Sql & " WHERE (A.afield1 = 'N')"
Sql = Sql & " AND (A.afield1 = 'N')"
Sql = Sql & " AND (A.afield2 = B.bfield2)"
Sql = Sql & " AND (A.afieldCode = '231')"
Sql = Sql & " AND (A.afieldCode = C.cfieldCode) "
Sql = Sql & " AND (B.bfield3 = D.dfield3) "
Sql = Sql & " GROUP BY D.field1,B.field2,B.field3, B.field4, B.field5, C.field6, A.field7, "
Sql = Sql & " C.field8 , C.field9, C.field10, C.field11, C.field12, C.field13, C.field14, C.field15"
Sql = Sql & " ORDER BY A.afieldCode, D.field1, B.field2 "
Sql = Sql & " union all"
Sql = " SELECT DISTINCT D.field1 AS fieldName,B.field2,B.field3, COUNT(*) AS CNT, B.field4, B.field5, C.field6, A.field7, "
Sql = Sql & " C.field8 , C.field9, C.field10, C.field11, C.field12, C.field13, C.field14, C.field15"
Sql = Sql & " FROM table1 A, table2 B, table3 C, table4 D "
Sql = Sql & " WHERE (A.afield1 = 'N')"
Sql = Sql & " AND (A.afield1 = 'N')"
Sql = Sql & " AND (A.afield2 = B.bfield2)"
Sql = Sql & " AND (A.afieldCode = '112')"
Sql = Sql & " AND (A.afieldCode = C.cfieldCode) "
Sql = Sql & " AND (B.bfield3 = D.dfield3) "
Sql = Sql & " GROUP BY D.field1,B.field2,B.field3, B.field4, B.field5, C.field6, A.field7, "
Sql = Sql & " C.field8 , C.field9, C.field10, C.field11, C.field12, C.field13, C.field14, C.field15"
Sql = Sql & " ORDER BY A.afieldCode, D.field1, B.field2 "
end if
==================== 소스코드 End ==============================
위의 소스에서 보시는 봐와 같이 상단에서 쿼리로 가져오고 싶은 결과값은
codeCheck(숨김) 실제물품명(뿌려줄내용)
112 112 231 상품4
위의 값은 실제의 소스에서 출력되는 화면이고 아래는 원하는 값입니다.
-------------------------------------------------------
codeCheck(숨김) 실제물품명(뿌려줄내용)
-------------------------------------------------------
211 211 010 상품1
010 010 211 상품1
231 231 112 상품3
112 112 231 상품3
=======================================================
위와 같이 화면출력을 하고 싶습니다.
상단에서 쿼리를 돌리면 제일하단에 있는 union all의 (A.afieldCode = '112')의 쿼리만 실행이 되어 집니다.
인포믹스에서는 UNION 이나 UNION ALL이 인식을 못하는것인가요.
아니면 위의 쿼리가 문제인지...
고수님의 도움을 부탁드립니다.
메일도 기다리고 있습니다.
많은 부탁드립니다.