안녕하세요.
기 구축된 테이블의 정보를 가져와야 하는 상황인데 현재 기 테이블을 바꿀수 없는 상태입니다.
예를 들어 서브쿼리로 select 해온 값들을 가지고 다른 값을 select 할수 있는지 문의드립니다.
SELECT (select pordno from t1 p where p.project_no = x.sernr limit 1) as pordno
,(select pordno_child from t1 p where p.project_no = x.sernr limit 1) as pordno_child
,(select item_value from t2 p where p.sub_name like 'Detector%' and p.pordno = (select pordno from t1 p where p.project_no = x.sernr limit 1)) as det_sn
,sp.zzwarranty as total_warranty
FROM test1 x LEFT OUTER JOIN test2 sp On x.vbeln = sp.vbeln and x.posnr = sp.posnr
where 조건값
위와 같은 쿼리가 있을때, 저 pordno, pordno_child, det_sn을 가지고 select 에 값을 하나더 가지고 와야 하는데, 위 쿼리에서 가능한 방법이나 아님 다른 방법이 없는지요.
즉 쿼리해온 select 값을 가지고 하나의 값을 더 가져올수 있는 방법 문의드립니다.
안녕하세요. 마농님
위 서브쿼리로 가져온 값들을 가지고 한가지 더 가져오고 싶은 방식입니다.
select (select pordno from t1 p where p.project_no = x.sernr limit 1) as pordno
,(select pordno_child from t1 p where p.project_no = x.sernr limit 1) as pordno_child
,(select item_value from t2 p where p.sub_name like 'Detector%' and p.pordno = (select pordno from t1 p where p.project_no = x.sernr limit 1)) as det_sn
,(select item_code from t1 where a = pordno and b = pordno_child and c = det_sn) as item_code
FROM test1 x LEFT OUTER JOIN test2 sp On x.vbeln = sp.vbeln and x.posnr = sp.posnr
저 위 빨간색 부분의 값들을 가지고 같은 t1에 있는 item_code 를 가지고 오고 싶습니다. (4번째 줄입니다.)
pord_no | pordno_child | det_sn | item_code |
10001 | 0010 | XXXXX | 1800-1900 |
이런식으로 가지고 오고 싶은데 방법이 없을까요?
select pordno, pordno_child, det_sn
,(select item_code from t1 where a = pordno and b = pordno_child and c = det_sn) as item_code
from (
select (select pordno from t1 p where p.project_no = x.sernr limit 1) as pordno
,(select pordno_child from t1 p where p.project_no = x.sernr limit 1) as pordno_child
,(select item_value from t2 p where p.sub_name like 'Detector%' and p.pordno = (select pordno from t1 p where p.project_no = x.sernr limit 1)) as det_sn
FROM test1 x LEFT OUTER JOIN test2 sp On x.vbeln = sp.vbeln and x.posnr = sp.posnr
)
신이만든지기님 전 무한루프로 빠져버립니다.
실 쿼리문 첨부해 드립니다. 한번만더 조언 부탁드립니다.
select pordno, pordno_child, det_sn
,(select ITNBR from vmes.prod_tbl where pordno = xxx.pordno and pordno_child = xxx.pordno_child and project_no = xxx.det_sn) as item_code
from vmes.shipment_sernr_tbl x LEFT OUTER JOIN vmes.shipment_tbl sp On x.vbeln = sp.vbeln and x.posnr = sp.posnr,
(
select (select pordno from vmes.prod_tbl p where p.project_no = x.sernr limit 1) as pordno
,(select pordno_child from vmes.prod_tbl p where p.project_no = x.sernr limit 1) as pordno_child
,(select item_value from cs_mes_insp_header_save p where p.sub_name like 'Detector%' and p.pordno = (select pordno from vmes.prod_tbl p where p.project_no = x.sernr limit 1)) as det_sn
FROM vmes.shipment_sernr_tbl x LEFT OUTER JOIN vmes.shipment_tbl sp On x.vbeln = sp.vbeln and x.posnr = sp.posnr
where x.sernr != '' and sp.if_flag != 'D' and sp.shipment_status = 's3' and sp.vstel = '1110'
and (sp.vkbur = '1110' or (sp.vkbur = '1120' and sp.auart = 'ZOR1'))
) xxx
where x.sernr != '' and sp.if_flag != 'D' and sp.shipment_status = 's3' and sp.vstel = '1110'
and (sp.vkbur = '1110' or (sp.vkbur = '1120' and sp.auart = 'ZOR1'))
SELECT PORDNO , PORDNO_CHILD , DET_SN , (SELECT ITNBR FROM VMES.PROD_TBL WHERE PORDNO = XXX.PORDNO AND PORDNO_CHILD = XXX.PORDNO_CHILD AND PROJECT_NO = XXX.DET_SN) AS ITEM_CODE FROM (SELECT (SELECT PORDNO FROM VMES.PROD_TBL P WHERE P.PROJECT_NO = X.SERNR limit 1) AS PORDNO , (SELECT PORDNO_CHILD FROM VMES.PROD_TBL P WHERE P.PROJECT_NO = X.SERNR limit 1) AS PORDNO_CHILD , (SELECT ITEM_VALUE FROM CS_MES_INSP_HEADER_SAVE P WHERE P.SUB_NAME LIKE 'Detector%' AND P.PORDNO = (SELECT PORDNO FROM VMES.PROD_TBL P WHERE P.PROJECT_NO = X.SERNR limit 1)) AS DET_SN FROM VMES.SHIPMENT_SERNR_TBL X LEFT OUTER JOIN VMES.SHIPMENT_TBL SP ON X.VBELN = SP.VBELN AND X.POSNR = SP.POSNR WHERE X.SERNR != '' AND SP.IF_FLAG != 'D' AND SP.SHIPMENT_STATUS = 's3' AND SP.VSTEL = '1110' AND ( SP.VKBUR = '1110' OR (SP.VKBUR = '1120' AND SP.AUART = 'ZOR1'))) XXX