March 25, 2021 at 9:01 am
Hello,
I run the following query:
SELECT RIS_OPT_SERV.CTC_DEPOT
,CONVERT(CHAR(4), B.DATE_DEPOT, 120) + '-' + CONVERT(CHAR(2), B.DATE_DEPOT, 101) DATE_DEPOT
,RIS_OPT_SERV.NO_CONTRAT_DEPOSANT
,RIS_OPT_SERV.NOM_DEPOSANT
,RIS_OPT_SERV.NO_CLIENT_DEPOSANT
,RIS_OPT_SERV.NUMERO_REF
,RIS_OPT_SERV.LB_PRODUIT_LONG
,RIS_OPT_SERV.LIB_OPTION_AFF
,RIS_OPT_SERV.NO_BORDEREAU
,RIS_OPT_SERV.LIB_SERVICE
,B.PRODUIT
,B.MECA
,B.PRESENTATION
,B.TRANCHE
,B.NB_PLIS
,B.POIDS
,B.TARIF
FROM
(SELECT RIS.NO_CONTRAT_DEPOSANT
,RIS.NOM_DEPOSANT
,RIS.NO_CLIENT_DEPOSANT
,RIS.CTC_DEPOT
,RIS.NUMERO_REF
,RIS.NO_BORDEREAU
,RP.LB_PRODUIT_LONG
,(SELECT RS.LIB_SERVICE
FROM RESSOURCE_SERVICE RS
WHERE RS.CLE_REF = RIS.CLE_REF
AND RS.CODE_SERVICE in (SELECT BS.CODE_SERVICE
FROM BORD_SERVICE BS
WHERE BS.NUM_AUTO in (SELECT MIN(B.NUM_AUTO)
FROM BORDEREAUX B
WHERE B.NO_BORDEREAU = RIS.NO_BORDEREAU
)
)
) AS LIB_SERVICE
,(SELECT ROA.LIB_OPTION_AFF
FROM RESSOURCE_OPTION_AFF ROA
WHERE ROA.CLE_REF = RIS.CLE_REF
AND ROA.CODE_OPTION_AFF in (SELECT BOA.CODE_OPTION_AFF
FROM BORD_OPTION_AFF BOA
WHERE BOA.NUM_AUTO in (SELECT MIN(B.NUM_AUTO)
FROM BORDEREAUX B
WHERE B.NO_BORDEREAU = RIS.NO_BORDEREAU
)
)
) AS LIB_OPTION_AFF
FROM
(SELECT
S.NO_CONTRAT_DEPOSANT
,S.NOM_DEPOSANT
,S.NO_CLIENT_DEPOSANT
,S.CTC_DEPOT
,S.NUMERO_REF
,B.NO_BORDEREAU
,B.CO_PRODUIT_COMMERCIAL
,B.CLE_REF
FROM JOB_DEPOT JD
INNER JOIN JOB J ON (JD.ID = J.ID AND JD.NO_ETIQ = J.NO_ETIQ)
INNER JOIN BORDEREAUX B ON ( B.NO_BORDEREAU = JD.NUM_DEPOT AND B.CODE_SEP = J.CODE_SEP)
INNER JOIN SITES S ON (S.NO_SITE = J.NO_SITE)
GROUP BY
S.NO_CONTRAT_DEPOSANT
,S.NOM_DEPOSANT
,S.NO_CLIENT_DEPOSANT
,S.CTC_DEPOT
,S.NUMERO_REF
,B.NO_BORDEREAU
,B.CO_PRODUIT_COMMERCIAL
,B.CLE_REF
) RIS
INNER JOIN RESSOURCE_PRODUIT RP ON ( RIS.CO_PRODUIT_COMMERCIAL = RP.CO_PRODUIT_COMMERCIAL AND RIS.CLE_REF = RP.CLE_REF )
) RIS_OPT_SERV
INNER JOIN BORDEREAUX B ON (B.NO_BORDEREAU = RIS_OPT_SERV.NO_BORDEREAU)
I get the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Any ideas or suggestions please ?
March 25, 2021 at 9:53 am
some of your subqueries gives more than one results. This would break the one value per row
Check if
(SELECT RS.LIB_SERVICE FROM RESSOURCE_SERVICE RS WHERE RS.CLE_REF = RIS.CLE_REFAND RS.CODE_SERVICE in (SELECT BS.CODE_SERVICE FROM BORD_SERVICE BS WHERE BS.NUM_AUTO in (SELECT MIN(B.NUM_AUTO) FROM BORDEREAUX B WHERE B.NO_BORDEREAU = RIS.NO_BORDEREAU))) AS LIB_SERVICE
doesn't return multiple rows for one particular item
and the other subsquery as wel
It would be easier with sample data en structure
March 25, 2021 at 6:55 pm
The error message pretty well explains itself. But because you didn't bother to post DDL we would just be guessing at anything, or trying to write the DDL that you didn't give us. Would you mind following basic netiquette and doing that?
Why are you formatting a date in your DML? This is not COBOL anymore! I also love the way you put a comma. The front of each line. This reminds me of how we used to program on punch cards in the 1960s. This convention allowed us to reuse and rearrange the deck of cards. Ever since the 1970s, programmers have had pretty printers and they reformat their source code by calling procedure.
Please post DDL and follow ANSI/ISO standards when asking for help.
March 25, 2021 at 11:20 pm
These are your subqueries
,(SELECT RS.LIB_SERVICE
FROM RESSOURCE_SERVICE RS
WHERE RS.CLE_REF = RIS.CLE_REF
AND RS.CODE_SERVICE in (SELECT BS.CODE_SERVICE
FROM BORD_SERVICE BS
WHERE BS.NUM_AUTO in (SELECT MIN(B.NUM_AUTO)
FROM BORDEREAUX B
WHERE B.NO_BORDEREAU = RIS.NO_BORDEREAU
)
)
) AS LIB_SERVICE
,(SELECT ROA.LIB_OPTION_AFF
FROM RESSOURCE_OPTION_AFF ROA
WHERE ROA.CLE_REF = RIS.CLE_REF
AND ROA.CODE_OPTION_AFF in (SELECT BOA.CODE_OPTION_AFF
FROM BORD_OPTION_AFF BOA
WHERE BOA.NUM_AUTO in (SELECT MIN(B.NUM_AUTO)
FROM BORDEREAUX B
WHERE B.NO_BORDEREAU = RIS.NO_BORDEREAU
)
)
) AS LIB_OPTION_AFF
If you change them to SELECT TOP(1) it will stop the error, but it might not give you the results you want so you need to check your SQL.
,(SELECT TOP(1) RS.LIB_SERVICE
FROM RESSOURCE_SERVICE RS
WHERE RS.CLE_REF = RIS.CLE_REF
AND RS.CODE_SERVICE in (SELECT BS.CODE_SERVICE
FROM BORD_SERVICE BS
WHERE BS.NUM_AUTO in (SELECT MIN(B.NUM_AUTO)
FROM BORDEREAUX B
WHERE B.NO_BORDEREAU = RIS.NO_BORDEREAU
)
)
) AS LIB_SERVICE
,(SELECT TOP(1) ROA.LIB_OPTION_AFF
FROM RESSOURCE_OPTION_AFF ROA
WHERE ROA.CLE_REF = RIS.CLE_REF
AND ROA.CODE_OPTION_AFF in (SELECT BOA.CODE_OPTION_AFF
FROM BORD_OPTION_AFF BOA
WHERE BOA.NUM_AUTO in (SELECT MIN(B.NUM_AUTO)
FROM BORDEREAUX B
WHERE B.NO_BORDEREAU = RIS.NO_BORDEREAU
)
)
) AS LIB_OPTION_AFF
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply