Sub-query which returns several lines

Question

0.00/5 (No votes)

See more:

, +

SQL0811 : Résultat de SELECT dépasse une ligne.
Cause . . . . . : La table résultante d'une instruction SELECT INTO, d'une sous-requête ou d'une sous-requête d'une instruction SET comporte plusieurs lignes. Il s'agit d'une erreur de type 2. Une erreur de ce type signale qu'une instruction SELECT INTO a tenté de retourner plusieurs lignes. Dans le cas d'une erreur de type 2, une sous-requête précédée d'un prédicat de base a produit plusieurs lignes. Or, une seule ligne est admise. Que faire . . . : Modifiez la requête de sorte qu'une seule ligne soit extraite, puis renouvelez votre demande. Pour traiter plusieurs lignes, vous devez utiliser les instructions DECLARE CURSOR, OPEN et FETCH ou, dans le cas d'une sous-requête, les prédicats IN, EXISTS, ANY ou ALL. L'extraction de plusieurs lignes est peut-être due à une erreur liée aux données (cas des lignes dupliquées, par exemple). (IBMDA400 Command)

Edit - translation:

Quote:
SQL0811 : SELECT result exceeds one line.

Cause. . . . . : The table resulting from a SELECT INTO statement, a subquery or a subquery from a SET statement has several rows. This is a type 2 error. An error of this type indicates that a SELECT INTO statement attempted to return multiple rows.
In the case of a type 2 error, a subquery preceded by a basic predicate produced several lines. However, only one line is allowed.
What to do . . . : Modify the request so that only one line is extracted, then repeat your request. To process several lines, you must use the DECLARE CURSOR, OPEN and FETCH instructions or, in the case of a subquery, the predicates IN, EXISTS, ANY or ALL.
The extraction of several lines may be due to an error related to the data (case of duplicate lines, for example). (IBMDA400 Command)

What I have tried:

I am creating different sub-queries in select query that return several lines but I get the problem that the sub-query return only one line.

This problem is repeating for each sub-query.

SQL

SELECT Quittance.DTNUMEXE, Quittance.DTMOIEXE, Quittance.DTANNQIT, Quittance.DTMOIQIT, Quittance.DTCODBRC, Quittance.DTSERQIT, Quittance.DTPLCQTC,Quittance.DTCATPOL, Quittance.DTCODRSQ, Quittance.DTNATRSQ, Quittance.DTCODPRO, Quittance.DTTYPQIT, Quittance.DTLIBTYP, Quittance.DTCODETA, Quittance.DTLIBETA, Quittance.DTCODAGC, Quittance.DTNUMCLT, Quittance.DTNATCLT, Quittance.DTJJDVAL, Quittance.DTMMDVAL, Quittance.DTAADVAL,Quittance.DTJJFVAL, Quittance.DTMMFVAL, Quittance.DTAAFVAL, Quittance.DTCODART, Quittance.DTCODGRN, Quittance.DTPRMNET, Quittance.DTMNTACC, Quittance.DTJJEMIS, Quittance.DTMMEMIS, Quittance.DTAAEMIS, CASE WHEN Quittance.DTNUMCLT >= 50000 AND Quittance.DTNUMCLT <= 100000 THEN 'OUI' ELSE 'NON' END DTCLTIMP,
CASE UPPER(Quittance.DTPLCEXO) WHEN 'O' THEN 'OUI' WHEN 'N' THEN 'NON' END DTEXOTAX, Quittance.DTMNTCOM , (SELECT Garantie.VGFRNNBP FROM AUTOPRD.AUVCLGRPP0 AS Garantie where (Garantie.VGNUMPLC = Quittance.DTPLCQTC ) AND (Garantie.VGCODGRN= Quittance.DTCODGRN) AND (Garantie.VGJJOCCU * 1000000 + Garantie.VGMMOCCU * 100000000 + Garantie.VGAAOCCU * 10000000000 + Garantie.VGNUMAVT) IN (SELECT MAX(Garantie.VGJJOCCU * 1000000 + Garantie.VGMMOCCU * 100000000 + Garantie.VGAAOCCU * 10000000000 + Garantie.VGNUMAVT ) AS DATEOCC FROM AUTOPRD.AUVCLGRPP0 WHERE (Garantie.VGMMOCCU * 100000000 + Garantie.VGAAOCCU * 10000000000 <= (Quittance.DTAADVAL * 10000 + Quittance.DTMMDVAL * 100 + Quittance.DTJJDVAL)*1000000 ) AND (Garantie.VGNUMPLC =Quittance.DTPLCQTC) AND (Garantie.VGCODGRN= Quittance.DTCODGRN))), (SELECT Garantie.VGCPTPTN FROM AUTOPRD.AUVCLGRPP0 AS Garantie where (Garantie.VGNUMPLC = Quittance.DTPLCQTC ) AND (Garantie.VGCODGRN= Quittance.DTCODGRN) AND (Garantie.VGJJOCCU * 1000000 + Garantie.VGMMOCCU * 100000000 + Garantie.VGAAOCCU * 10000000000 + Garantie.VGNUMAVT) IN (SELECT MAX(Garantie.VGJJOCCU * 1000000 + Garantie.VGMMOCCU * 100000000 + Garantie.VGAAOCCU * 10000000000 + Garantie.VGNUMAVT ) AS DATEOCC FROM AUTOPRD.AUVCLGRPP0 WHERE (Garantie.VGMMOCCU * 100000000 + Garantie.VGAAOCCU * 10000000000 <= (Quittance.DTAADVAL * 10000 + Quittance.DTMMDVAL * 100 + Quittance.DTJJDVAL)*1000000 ) AND (Garantie.VGNUMPLC =Quittance.DTPLCQTC) AND (Garantie.VGCODGRN= Quittance.DTCODGRN)) )

FROM TRANSFERT.DTQTTNCPP1 AS Quittance

WHERE (Quittance.DTCODBRC = 1) AND (Quittance.DTCODGRN>0) AND (DTNUMEXE =2020)

Posted 29-May-20 0:15am

houssem eddine ayari

Updated 29-May-20 0:41am

#realJSOP

v4

Add a Solution

Comments

Nelek 29-May-20 6:17am

This is an english site, you should try to translate the text if you want to maximize your opportunities to get help

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2020-05-29T00:42:00

Dude, learn how to format your code so we can read it. Beyond that, here is your code formatted. Note the comments:

SQL

SELECT Q.DTNUMEXE
       ,Q.DTMOIEXE
       ,Q.DTANNQIT
       ,Q.DTMOIQIT
       ,Q.DTCODBRC
       ,Q.DTSERQIT
       ,Q.DTPLCQTC
       ,Q.DTCATPOL
       ,Q.DTCODRSQ
       ,Q.DTNATRSQ
       ,Q.DTCODPRO
       ,Q.DTTYPQIT
       ,Q.DTLIBTYP
       ,Q.DTCODETA
       ,Q.DTLIBETA
       ,Q.DTCODAGC
       ,Q.DTNUMCLT
       ,Q.DTNATCLT
       ,Q.DTJJDVAL
       ,Q.DTMMDVAL
       ,Q.DTAADVAL
       ,Q.DTJJFVAL
       ,Q.DTMMFVAL
       ,Q.DTAAFVAL
       ,Q.DTCODART
       ,Q.DTCODGRN
       ,Q.DTPRMNET
       ,Q.DTMNTACC
       ,Q.DTJJEMIS
       ,Q.DTMMEMIS
       ,Q.DTAAEMIS
       ,CASE WHEN Q.DTNUMCLT >= 50000 AND Q.DTNUMCLT <= 100000 THEN 'OUI' ELSE 'NON' END AS DTCLTIMP
       ,CASE UPPER(Q.DTPLCEXO) WHEN 'O' THEN 'OUI' WHEN 'N' THEN 'NON' END AS DTEXOTAX
       ,Q.DTMNTCOM 
       ,(
	        -- this subquery can only return 1 value
            SELECT G.VGFRNNBP 
            FROM   AUTOPRD.AUVCLGRPP0 AS G 
            WHERE  (G.VGNUMPLC = Q.DTPLCQTC ) 
            AND    (G.VGCODGRN= Q.DTCODGRN) 
            AND    (G.VGJJOCCU * 1000000 + G.VGMMOCCU * 100000000 + G.VGAAOCCU * 10000000000 + G.VGNUMAVT) 
            	   IN (
            			  SELECT MAX(G.VGJJOCCU * 1000000 + G.VGMMOCCU * 100000000 + G.VGAAOCCU * 10000000000 + G.VGNUMAVT ) AS DATEOCC 
            			  FROM   AUTOPRD.AUVCLGRPP0 
            			  WHERE  (G.VGMMOCCU * 100000000 + G.VGAAOCCU * 10000000000 <= (Q.DTAADVAL * 10000 + Q.DTMMDVAL * 100 + Q.DTJJDVAL) * 1000000 ) 
            			  AND    (G.VGNUMPLC =Q.DTPLCQTC) AND (G.VGCODGRN= Q.DTCODGRN)
                      )
		) -- you neglected to specifyy the column name here 
       ,(
            -- this subquery can only return 1 value
	        SELECT G.VGCPTPTN 
			FROM   AUTOPRD.AUVCLGRPP0 AS G 
			WHERE  (G.VGNUMPLC = Q.DTPLCQTC ) 
			AND    (G.VGCODGRN= Q.DTCODGRN) AND (G.VGJJOCCU * 1000000 + G.VGMMOCCU * 100000000 + G.VGAAOCCU * 10000000000 + G.VGNUMAVT) 
			       IN (
                          SELECT MAX(G.VGJJOCCU * 1000000 + G.VGMMOCCU * 100000000 + G.VGAAOCCU * 10000000000 + G.VGNUMAVT ) AS DATEOCC 
                          FROM   AUTOPRD.AUVCLGRPP0 
                          WHERE  (G.VGMMOCCU * 100000000 + G.VGAAOCCU * 10000000000 <= (Q.DTAADVAL * 10000 + Q.DTMMDVAL * 100 + Q.DTJJDVAL) * 1000000 ) 
				          AND    (G.VGNUMPLC =Q.DTPLCQTC) 
				          AND    (G.VGCODGRN= Q.DTCODGRN)
					  ) 
		) -- you neglected to specifyy the column name here 
FROM  TRANSFERT.DTQTTNCPP1 AS Q
WHERE (Q.DTCODBRC = 1) 
AND   (Q.DTCODGRN>0) 
AND   (DTNUMEXE = 2020)