当前位置: 动力学知识库 > 问答 > 编程问答 >

Single row subquery returns more than one row SQL Oracle

问题描述:

I am trying to get multiple rows to display on the same line. I have the follow code:

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

The part where I run into trouble is:

(SELECT SUBSTR (CON.CONDNAME,1,75) FROM CTMSLO80.CMS_CON_J CON

WHERE CON.ROWTYPE = 'I'

AND JOB.JOBNO = CON.JOBNO) "In Cond",'|',

It will return one value, but if I have multiple values to return, it will give an error "ORA-01427: Single Row Subquery returns more than one row"

I've tried various methods like XML path, IN, EXISTS, etc, but those give me different errors that I am not able to resolve. Anyone have experience with something like this?

===========================

Thank you for responses

Version if PL/SQL is 9.0.6.1655

Don't know what you mean by "Delimiter"

Not having success breaking down the code into a smaller chunk. I did not write the bulk of this code, but am trying to tweak it to work with what I am trying to do.

The link that was provided did help get rid of the error. I used the "MAX" function and changed the pertinent query to be:

(SELECT max(SUBSTR (CON.CONDNAME,1,75)) FROM CTMSLO80.CMS_CON_J CON

WHERE CON.ROWTYPE = 'I'

AND JOB.JOBNO = CON.JOBNO) "In Cond",'|',

Now it will return the first item at least. However, I would like to be able to return multiple items. This part of the query returns a column that has a value like below:

In Cond

example1

There is more data to pull however, I want it to return like so:

In Cond

example1, example2

Any ideas on how I can get it to do that?

网友答案:

Use LISTAGG() if you want a list:

(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", 

Note that the maximum size of the list is subject to Oracle limits on strings, 4,000 characters. You might overflow if your list is too long.

分享给朋友:
您可能感兴趣的文章:
随机阅读: