我试图使多行显示在同一行上。我有以下代码:
SELECT DISTINCT
SUBSTR(JOB.JOBNAME,1,25) "Jobname",'|',
(SELECT SUBSTR(VAR.VAREXPR,1,15)
FROM CTMSLO80.CMS_SETVAR VAR
WHERE var.var = '%%PS8-PRCSNAME'
AND JOB.jobno = VAR.jobno) "Process",'|',
(SELECT SUBSTR(VAR.VAREXPR,1,30) FROM CTMSLO80.CMS_SETVAR VAR
WHERE var.var = '%%PS8-RUNCONTROLID'
AND JOB.jobno = VAR.jobno) "Run Cntrl ID",'|',
NVL((SELECT SUBSTR(VAR.VAREXPR,1,20) FROM CTMSLO80.CMS_SETVAR VAR
WHERE var.var = '%%PS8-PRCSTYPE'
AND JOB.jobno = VAR.jobno), ' ') "Process Type",'|',
SUBSTR(DAYSCAL,1,10) "Calendar",'|',
NVL2(JOB.FROMTIME,SUBSTR(JOB.FROMTIME,1,2)||':'||SUBSTR(JOB.FROMTIME,3,2),' ') "From",'|',
NVL2(JOB.Until ,SUBSTR(JOB.Until,1,2)||':'||SUBSTR(JOB.Until,3,2),' ') "Until",'|',
Case
WHEN JOB.DAYSTR = 'ALL' THEN
JOB.DAYSTR
Else
SUBSTR(NVL(REPLACE(REPLACE(REPLACE(REPLACE(TRANSLATE(JOB.WDAYSTR,'01234567AL','0MTW4F7AL'),'0','Su'),'4','Th'),'7','Sa'),'ALL','Daily'),' '),1,16)
End
"Days",'|',
NVL(JOB.DESCRIPT,' ') "Description",'|',
SUBSTR (job.jobname,1,5) "Table",'|',
(SELECT SUBSTR (CON.CONDNAME,1,75) FROM CTMSLO80.CMS_CON_J CON
WHERE CON.ROWTYPE = 'I'
AND JOB.JOBNO = CON.JOBNO) "In Cond",'|',
(SELECT SUBSTR(VAR.VAREXPR,1,35) FROM CTMSLO80.CMS_SETVAR VAR
WHERE var.var = '%%PS8-DESCRIPTION'
AND JOB.jobno = VAR.jobno) "Description",'|',
SUBSTR(JOB.OWNER,1,6) "BMCID",'|',
NVL2(JOB.DESCRIPT,(SUBSTR(JOB.DESCRIPT,1,INSTR(JOB.DESCRIPT,'/',1,1)-1)),'1') JOBORDER
FROM CTMSLO80.CMS_JOBDEF JOB, CTMSLO80.CMS_SETVAR VAR
where OWNER LIKE 'BMCHR'
and JOB.JOBno = VAR.JOBno
我遇到麻烦的部分是:
(SELECT SUBSTR (CON.CONDNAME,1,75) FROM CTMSLO80.CMS_CON_J CON
WHERE CON.ROWTYPE = 'I'
AND JOB.JOBNO = CON.JOBNO) "In Cond",'|',
它将返回一个值,但是如果我有多个值要返回,它将给出错误“ ORA-01427:单行子查询返回多个行”
我尝试了各种方法,例如XML路径,IN,EXISTS等,但是这些方法给了我无法解决的不同错误。任何人都有类似的经验吗?
==========================
谢谢你的回应
如果PL / SQL为9.0.6.1655的版本
不知道您所说的“分隔符”是什么意思
将代码分解成较小的块没有成功。我没有编写大量的这段代码,但是正在尝试对其进行调整以使其与我正在尝试的工作一起使用。
提供的链接确实有助于摆脱该错误。我使用了“ MAX”功能,并将相关查询更改为:
(SELECT max(SUBSTR (CON.CONDNAME,1,75)) FROM CTMSLO80.CMS_CON_J CON
WHERE CON.ROWTYPE = 'I'
AND JOB.JOBNO = CON.JOBNO) "In Cond",'|',
现在它将至少返回第一项。但是,我希望能够返回多个项目。查询的此部分返回一列,其值如下所示:
在康德
例子1
但是,还有更多数据可以提取,我希望它像这样返回:
在康德
example1,example2
关于如何获得它的任何想法?
LISTAGG()
如果需要列表,请使用:
(SELECT LISTAGG(SUBSTR(CON.CONDNAME, 1, 75), '|') WITHIN GROUP (ORDER BY CON.CONDNAME)
FROM CTMSLO80.CMS_CON_J CON
WHERE CON.ROWTYPE = 'I' AND JOB.JOBNO = CON.JOBNO
) as "In Cond",
请注意,列表的最大大小受Oracle对字符串(4,000个字符)的限制。如果列表太长,可能会溢出。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句