SELECT * FROM(select dutbin.PGM, dutbin."Pgm version", testitem.pgm,testitem.pgm_version,
testitem.TESTITEM_NO, testitem.TESTITEM_INFO FROM dutswitch,dutbin,testitem WHERE
dutbin.CUSTOMERLOTID=dutswitch."CustomerLotID" and testitem.PGM = 'O524W83D' and
dutbin."Pgm version" = 'N.10A' and testitem_no = '1' and dutswitch.DID=8464)A
UNION ALL
SELECT * FROM(select dutbin_v58b_nt.PGM, dutbin_v58b_nt."Pgm version", testitem.pgm,
testitem.pgm_version,testitem.TESTITEM_NO, testitem.TESTITEM_INFO FROM dutswitch,dutbin_v58b_nt,
testitem WHERE dutbin_v58b_nt.CUSTOMERLOTID=dutswitch."CustomerLotID" and
testitem.PGM = 'O524W83D' and dutbin_v58b_nt."Pgm version" = 'N.10A' and
testitem_no = '1' and dutswitch.DID=8464)B
UNION ALL SELECT * FROM(select dutbin_v58b_yz.PGM, dutbin_v58b_yz."Pgm version",
testitem.pgm,testitem.pgm_version,testitem.TESTITEM_NO, testitem.TESTITEM_INFO FROM dutswitch,
dutbin_v58b_yz,testitem WHERE dutbin_v58b_yz.CUSTOMERLOTID=dutswitch."CustomerLotID" and
testitem.PGM = 'O524W83D' and dutbin_v58b_yz."Pgm version" = 'N.10A' and testitem_no = '1' and
dutswitch.DID=8464)C
UNION ALL
SELECT * FROM(select dutbin_u68a_nt.PGM, dutbin_u68a_nt."Pgm version", testitem.pgm,testitem.pgm_version,
testitem.TESTITEM_NO, testitem.TESTITEM_INFO FROM dutswitch,dutbin_u68a_nt,testitem WHERE
dutbin_u68a_nt.CUSTOMERLOTID=dutswitch."CustomerLotID" and testitem.PGM = 'O524W83D' and
dutbin_u68a_nt."Pgm version" = 'N.10A' and testitem_no = '1' and dutswitch.DID=8464)D
UNION ALL
SELECT * FROM(select dutbin_u68a_yy.PGM, dutbin_u68a_yy."Pgm version", testitem.pgm,testitem.pgm_version,
testitem.TESTITEM_NO, testitem.TESTITEM_INFO FROM dutswitch,dutbin_u68a_yy,testitem WHERE
dutbin.CUSTOMERLOTID=dutswitch."CustomerLotID" and testitem.PGM = 'O524W83D' and dutbin_u68a_yy."Pgm version" = 'N.10A' and
testitem_no = '1' and dutswitch.DID=8464)E
提示错误ora-00918。请问是什么原因?
在共有列前面加上具体表/视图的名称/别名即可。
select name,age,birthday from stu
union all
select null name,0 age,birthday from tec
union all
select name,age,null birthday from pep
你看看错在哪一行。
testitem.TESTITEM_NO, testitem.TESTITEM_INFO FROM dutswitch,dutbin,testitem WHERE
dutbin.CUSTOMERLOTID=dutswitch."CustomerLotID" and testitem.PGM = 'O524W83D' and
dutbin."Pgm version" = 'N.10A' and testitem_no = '1' and dutswitch.DID=8464)A
若行,依次执行B、C、D、E。
testitem.TESTITEM_NO, testitem.TESTITEM_INFO FROM dutswitch,dutbin,testitem WHERE
dutbin.CUSTOMERLOTID=dutswitch."CustomerLotID" and testitem.PGM = 'O524W83D' and
dutbin."Pgm version" = 'N.10A' and testitem_no = '1' and dutswitch.DID=8464)A
UNION ALL
SELECT * FROM(select dutbin_v58b_nt.PGM, dutbin_v58b_nt."Pgm version", testitem.pgm,testitem.pgm_version,
testitem.TESTITEM_NO, testitem.TESTITEM_INFO FROM dutswitch,dutbin_v58b_nt,testitem WHERE
dutbin_v58b_nt.CUSTOMERLOTID=dutswitch."CustomerLotID" and testitem.PGM = 'O524W83D' and
dutbin_v58b_nt."Pgm version" = 'N.10A' and testitem_no = '1' and dutswitch.DID=8464)B
UNION ALL
SELECT * FROM(select dutbin_v58b_yz.PGM, dutbin_v58b_yz."Pgm version", testitem.pgm,testitem.pgm_version,
testitem.TESTITEM_NO, testitem.TESTITEM_INFO FROM dutswitch,dutbin_v58b_yz,testitem WHERE
dutbin_v58b_yz.CUSTOMERLOTID=dutswitch."CustomerLotID" and testitem.PGM = 'O524W83D' and
dutbin_v58b_yz."Pgm version" = 'N.10A' and testitem_no = '1' and dutswitch.DID=8464)C
UNION ALL
SELECT * FROM(select dutbin_u68a_nt.PGM, dutbin_u68a_nt."Pgm version", testitem.pgm,testitem.pgm_version,
testitem.TESTITEM_NO, testitem.TESTITEM_INFO FROM dutswitch,dutbin_u68a_nt,testitem WHERE
dutbin_u68a_nt.CUSTOMERLOTID=dutswitch."CustomerLotID" and testitem.PGM = 'O524W83D' and
dutbin_u68a_nt."Pgm version" = 'N.10A' and testitem_no = '1' and dutswitch.DID=8464)D
UNION ALL
SELECT * FROM(select dutbin_u68a_yy.PGM, dutbin_u68a_yy."Pgm version", testitem.pgm,testitem.pgm_version,
testitem.TESTITEM_NO, testitem.TESTITEM_INFO FROM dutswitch,dutbin_u68a_yy,testitem WHERE
dutbin.CUSTOMERLOTID=dutswitch."CustomerLotID" and testitem.PGM = 'O524W83D' and
dutbin_u68a_yy."Pgm version" = 'N.10A' and testitem_no = '1' and dutswitch.DID=8464)E
PL/SQL的数据怎么导出来?
Name Type Nullable
DID NUMBER(11) N
MachineName VARCHAR2(20) N
CustomerLotID VARCHAR2(20) N
state VARCHAR2(4) N
status1 VARCHAR2(64) N
status2 VARCHAR2(64) Y
Date VARCHAR2(20) Ndutbin:
Name Type Nullable
DID NUMBER(11) N
CUSTOMERLOTID VARCHAR2(15) N
STATE VARCHAR2(6) N
HIFIX VARCHAR2(25) N
PGM VARCHAR2(20) N
Pgm version VARCHAR2(10) N
DEVICE VARCHAR2(20) N
TESTER VARCHAR2(20) N
Date VARCHAR2(8) N
STARTTIME VARCHAR2(8) N
BIN BLOB N
TESTITEM BLOB N testitem:
Name Type Nullable
DID NUMBER(11) N
PGM VARCHAR2(20) N
PGM_VERSION VARCHAR2(20) N
TESTITEM_NO NUMBER(10) N
TESTITEM_INFO VARCHAR2(20) N
不过外面套的那层select * from 可以去掉了,没什么意义
SQL> SELECT ydid,sws_dm FROM test WHERE ydid=1
2 UNION ALL
3 SELECT ydid,sws_dm FROM test WHERE ydid=1;
YDID SWS_DM
---------- ----------
1 1
1 1
SQL>
SQL> SELECT * FROM (SELECT ydid,sws_dm FROM test WHERE ydid=1)
2 UNION ALL
3 SELECT * FROM (SELECT ydid,sws_dm FROM test WHERE ydid=1)
4 ;
YDID SWS_DM
---------- ----------
1 1
1 1
SQL>
FROM (SELECT DUTBIN.PGM,
dutbin."Pgm version",
TESTITEM.PGM,
TESTITEM.PGM_VERSION,
TESTITEM.TESTITEM_NO,
TESTITEM.TESTITEM_INFO
FROM DUTSWITCH, DUTBIN, TESTITEM
WHERE DUTBIN.CUSTOMERLOTID = dutswitch."CustomerLotID"
AND TESTITEM.PGM = 'O524W83D'
AND dutbin."Pgm version" = 'N.10A'
AND TESTITEM_NO = '1'
AND DUTSWITCH.DID = 8464) A
UNION ALL
SELECT *
FROM (SELECT DUTBIN_V58B_NT.PGM,
dutbin_v58b_nt."Pgm version",
TESTITEM.PGM,
TESTITEM.PGM_VERSION,
TESTITEM.TESTITEM_NO,
TESTITEM.TESTITEM_INFO
FROM DUTSWITCH, DUTBIN_V58B_NT, TESTITEM
WHERE DUTBIN_V58B_NT.CUSTOMERLOTID = dutswitch."CustomerLotID"
AND TESTITEM.PGM = 'O524W83D'
AND dutbin_v58b_nt."Pgm version" = 'N.10A'
AND TESTITEM_NO = '1'
AND DUTSWITCH.DID = 8464) B
UNION ALL
SELECT *
FROM (SELECT DUTBIN_V58B_YZ.PGM,
dutbin_v58b_yz."Pgm version",
TESTITEM.PGM,
TESTITEM.PGM_VERSION,
TESTITEM.TESTITEM_NO,
TESTITEM.TESTITEM_INFO
FROM DUTSWITCH, DUTBIN_V58B_YZ, TESTITEM
WHERE DUTBIN_V58B_YZ.CUSTOMERLOTID = dutswitch."CustomerLotID"
AND TESTITEM.PGM = 'O524W83D'
AND dutbin_v58b_yz."Pgm version" = 'N.10A'
AND TESTITEM_NO = '1'
AND DUTSWITCH.DID = 8464) C
UNION ALL
SELECT *
FROM (SELECT DUTBIN_U68A_NT.PGM,
dutbin_u68a_nt."Pgm version",
TESTITEM.PGM,
TESTITEM.PGM_VERSION,
TESTITEM.TESTITEM_NO,
TESTITEM.TESTITEM_INFO
FROM DUTSWITCH, DUTBIN_U68A_NT, TESTITEM
WHERE DUTBIN_U68A_NT.CUSTOMERLOTID = dutswitch."CustomerLotID"
AND TESTITEM.PGM = 'O524W83D'
AND dutbin_u68a_nt."Pgm version" = 'N.10A'
AND TESTITEM_NO = '1'
AND DUTSWITCH.DID = 8464) D
UNION ALL
SELECT *
FROM (SELECT DUTBIN_U68A_YY.PGM,
dutbin_u68a_yy."Pgm version",
TESTITEM.PGM,
TESTITEM.PGM_VERSION,
TESTITEM.TESTITEM_NO,
TESTITEM.TESTITEM_INFO
FROM DUTSWITCH, DUTBIN_U68A_YY, TESTITEM
WHERE DUTBIN.CUSTOMERLOTID = dutswitch."CustomerLotID"
AND TESTITEM.PGM = 'O524W83D'
AND dutbin_u68a_yy."Pgm version" = 'N.10A'
AND TESTITEM_NO = '1'
AND DUTSWITCH.DID = 8464) E
dutswitch:
Name Type Nullable
DID NUMBER(11) N
MachineName VARCHAR2(20) N
CustomerLotID VARCHAR2(20) N
state VARCHAR2(4) N
status1 VARCHAR2(64) N
status2 VARCHAR2(64) Y
Date VARCHAR2(20) Ndutbin:
Name Type Nullable
DID NUMBER(11) N
CUSTOMERLOTID VARCHAR2(15) N
STATE VARCHAR2(6) N
HIFIX VARCHAR2(25) N
PGM VARCHAR2(20) N
Pgm version VARCHAR2(10) N
DEVICE VARCHAR2(20) N
TESTER VARCHAR2(20) N
Date VARCHAR2(8) N
STARTTIME VARCHAR2(8) N
BIN BLOB N
TESTITEM BLOB N testitem:
Name Type Nullable
DID NUMBER(11) N
PGM VARCHAR2(20) N
PGM_VERSION VARCHAR2(20) N
TESTITEM_NO NUMBER(10) N
TESTITEM_INFO VARCHAR2(20) N
SELECT *
FROM (select dutbin.PGM a,
dutbin."Pgm version",
testitem.pgm b,
testitem.pgm_version,
testitem.TESTITEM_NO,
testitem.TESTITEM_INFO
FROM dutswitch, dutbin, testitem
WHERE dutbin.CUSTOMERLOTID = dutswitch."CustomerLotID"
and testitem.PGM = 'O524W83D'
and dutbin."Pgm version" = 'N.10A'
and testitem_no = '1'
and dutswitch.DID = 8464) A
UNION ALL
SELECT *
FROM (select dutbin_v58b_nt.PGM a,
dutbin_v58b_nt."Pgm version",
testitem.pgm b,
testitem.pgm_version,
testitem.TESTITEM_NO,
testitem.TESTITEM_INFO
FROM dutswitch, dutbin_v58b_nt, testitem
WHERE dutbin_v58b_nt.CUSTOMERLOTID = dutswitch."CustomerLotID"
and testitem.PGM = 'O524W83D'
and dutbin_v58b_nt."Pgm version" = 'N.10A'
and testitem_no = '1'
and dutswitch.DID = 8464) B
UNION ALL
SELECT *
FROM (select dutbin_v58b_yz.PGM a,
dutbin_v58b_yz."Pgm version",
testitem.pgm b,
testitem.pgm_version,
testitem.TESTITEM_NO,
testitem.TESTITEM_INFO
FROM dutswitch, dutbin_v58b_yz, testitem
WHERE dutbin_v58b_yz.CUSTOMERLOTID = dutswitch."CustomerLotID"
and testitem.PGM = 'O524W83D'
and dutbin_v58b_yz."Pgm version" = 'N.10A'
and testitem_no = '1'
and dutswitch.DID = 8464) C
UNION ALL
SELECT *
FROM (select dutbin_u68a_nt.PGM a,
dutbin_u68a_nt."Pgm version",
testitem.pgm b,
testitem.pgm_version,
testitem.TESTITEM_NO,
testitem.TESTITEM_INFO
FROM dutswitch, dutbin_u68a_nt, testitem
WHERE dutbin_u68a_nt.CUSTOMERLOTID = dutswitch."CustomerLotID"
and testitem.PGM = 'O524W83D'
and dutbin_u68a_nt."Pgm version" = 'N.10A'
and testitem_no = '1'
and dutswitch.DID = 8464) D
UNION ALL
SELECT *
FROM (select dutbin_u68a_yy.PGM a,
dutbin_u68a_yy."Pgm version",
testitem.pgm b,
testitem.pgm_version,
testitem.TESTITEM_NO,
testitem.TESTITEM_INFO
FROM dutswitch, dutbin_u68a_yy, testitem
WHERE dutbin_u68a_yy.CUSTOMERLOTID = dutswitch."CustomerLotID"
and testitem.PGM = 'O524W83D'
and dutbin_u68a_yy."Pgm version" = 'N.10A'
and testitem_no = '1'
and dutswitch.DID = 8464) E
SELECT *
FROM (SELECT DUTBIN.PGM,
dutbin."Pgm version",
TESTITEM.PGM PGM1,
TESTITEM.PGM_VERSION,
TESTITEM.TESTITEM_NO,
TESTITEM.TESTITEM_INFO
FROM DUTSWITCH, DUTBIN, TESTITEM
WHERE DUTBIN.CUSTOMERLOTID = dutswitch."CustomerLotID"
AND TESTITEM.PGM = 'O524W83D'
AND dutbin."Pgm version" = 'N.10A'
AND TESTITEM_NO = '1'
AND DUTSWITCH.DID = 8464) A
UNION ALL
SELECT *
FROM (SELECT DUTBIN_V58B_NT.PGM,
dutbin_v58b_nt."Pgm version",
TESTITEM.PGM PGM1,
TESTITEM.PGM_VERSION,
TESTITEM.TESTITEM_NO,
TESTITEM.TESTITEM_INFO
FROM DUTSWITCH, DUTBIN_V58B_NT, TESTITEM
WHERE DUTBIN_V58B_NT.CUSTOMERLOTID = dutswitch."CustomerLotID"
AND TESTITEM.PGM = 'O524W83D'
AND dutbin_v58b_nt."Pgm version" = 'N.10A'
AND TESTITEM_NO = '1'
AND DUTSWITCH.DID = 8464) B
UNION ALL
SELECT *
FROM (SELECT DUTBIN_V58B_YZ.PGM,
dutbin_v58b_yz."Pgm version",
TESTITEM.PGM PGM1,
TESTITEM.PGM_VERSION,
TESTITEM.TESTITEM_NO,
TESTITEM.TESTITEM_INFO
FROM DUTSWITCH, DUTBIN_V58B_YZ, TESTITEM
WHERE DUTBIN_V58B_YZ.CUSTOMERLOTID = dutswitch."CustomerLotID"
AND TESTITEM.PGM = 'O524W83D'
AND dutbin_v58b_yz."Pgm version" = 'N.10A'
AND TESTITEM_NO = '1'
AND DUTSWITCH.DID = 8464) C
UNION ALL
SELECT *
FROM (SELECT DUTBIN_U68A_NT.PGM,
dutbin_u68a_nt."Pgm version",
TESTITEM.PGM PGM1,
TESTITEM.PGM_VERSION,
TESTITEM.TESTITEM_NO,
TESTITEM.TESTITEM_INFO
FROM DUTSWITCH, DUTBIN_U68A_NT, TESTITEM
WHERE DUTBIN_U68A_NT.CUSTOMERLOTID = dutswitch."CustomerLotID"
AND TESTITEM.PGM = 'O524W83D'
AND dutbin_u68a_nt."Pgm version" = 'N.10A'
AND TESTITEM_NO = '1'
AND DUTSWITCH.DID = 8464) D
UNION ALL
SELECT *
FROM (SELECT DUTBIN_U68A_YY.PGM,
dutbin_u68a_yy."Pgm version",
TESTITEM.PGM PGM1,
TESTITEM.PGM_VERSION,
TESTITEM.TESTITEM_NO,
TESTITEM.TESTITEM_INFO
FROM DUTSWITCH, DUTBIN_U68A_YY, TESTITEM
WHERE DUTBIN.CUSTOMERLOTID = dutswitch."CustomerLotID"
AND TESTITEM.PGM = 'O524W83D'
AND dutbin_u68a_yy."Pgm version" = 'N.10A'
AND TESTITEM_NO = '1'
AND DUTSWITCH.DID = 8464) E