单行子查询返回多行，带有多个OR。

huangapple
go评论102阅读模式
英文:

single-row subquery returns more than one row with multiple OR

问题

I got this error while using multiple looped OR : single-row subquery returns more than one row,

The error only occurs via postman (maybe related to hibernate), the query works in oracle sql develoer

My request :

  2. WITH SPRM_TABLE AS (
  3.   SELECT 
  4.     DISTINCT P.SPRM_KEY, 
  5.     P.SPRM_PARENT_KEY AS PARENTS, 
  6.     P.CPRM_KEY, 
  7.     P.SPRM_PARENT_KEY 
  8.   FROM 
  9.     S4C_SUB_PERIMETER P 
  10.     INNER JOIN S4C_CRITERIA CRI ON CRI.SPRM_KEY = P.SPRM_KEY 
  11.   WHERE 
  12.     (
  13.       (
  14.         CRI.CRI_TYPE = 'OFFER' 
  15.         AND CRI.CRI_VALUE = 'BUSINESS_ETHERNET'
  16.       )
  17.     ) 
  18.     or (
  19.       (
  20.         P.SPRM_PARENT_KEY IS NULL 
  21.         OR (
  22.           CRI.CRI_TYPE IN (
  23.             'SATIN_ACCOUNT', 'SATIN_SUBACCOUNT', 
  24.             'IDENT_RCE', 'ICO1'
  25.           ) 
  26.           AND (
  27.             SELECT 
  28.               CHP.CRI_KEY_PARENT 
  29.             FROM 
  30.               S4C_CRITERIA_HERITANCE CHP 
  31.             WHERE 
  32.               CHP.CRI_KEY_CHILD = CRI.CRI_KEY
  33.           ) IN (
  34.             SELECT 
  35.               CBB.CRI_KEY 
  36.             FROM 
  37.               S4C_CRITERIA CBB 
  38.             WHERE 
  39.               CBB.SPRM_KEY = P.SPRM_PARENT_KEY
  40.           )
  41.         )
  42.       ) 
  43.       AND (
  44.         (
  45.           CRI.CRI_TYPE = 'IDENT' 
  46.           AND CRI.CRI_VALUE = '00010525'
  47.         )
  48.       )
  49.     )
  50. ) 
  51. SELECT 
  52.   DISTINCT CPRM.CPRM_KEY AS PRM_ID, 
  53.   'CLIENT' AS PRM_TYPE 
  54. FROM 
  55.   CPRM_TABLE CPRM 
  56. UNION ALL 
  57. SELECT 
  58.   DISTINCT PRM.SPRM_KEY AS PRM_ID, 
  59.   'SUB' AS PRM_TYPE 
  60. FROM 
  61.   SPRM_TABLE PRM START WITH PRM.SPRM_PARENT_KEY IS NULL 
  62.   AND PRM.CPRM_KEY IN (
  63.     SELECT 
  64.       CLI_PRM.CPRM_KEY 
  65.     FROM 
  66.       CPRM_TABLE CLI_PRM
  67.   ) CONNECT BY PRIOR PRM.SPRM_KEY = PRM.SPRM_PARENT_KEY

If Someone could help please? and if someone could explain why does this works in Oracle sql develper but not in java code with hibernate?

英文:

I got this error while using multiple looped OR : single-row subquery returns more than one row,

The error only occurs via postman (maybe related to hibernate), the query works in oracle sql develoer

My request :

  2. WITH SPRM_TABLE AS (
  3.   SELECT 
  4.     DISTINCT P.SPRM_KEY, 
  5.     P.SPRM_PARENT_KEY AS PARENTS, 
  6.     P.CPRM_KEY, 
  7.     P.SPRM_PARENT_KEY 
  8.   FROM 
  9.     S4C_SUB_PERIMETER P 
  10.     INNER JOIN S4C_CRITERIA CRI ON CRI.SPRM_KEY = P.SPRM_KEY 
  11.   WHERE 
  12.     (
  13.       (
  14.         CRI.CRI_TYPE = 'OFFER' 
  15.         AND CRI.CRI_VALUE = 'BUSINESS_ETHERNET'
  16.       )
  17.     ) 
  18.     or (
  19.       (
  20.         P.SPRM_PARENT_KEY IS NULL 
  21.         OR (
  22.           CRI.CRI_TYPE IN (
  23.             'SATIN_ACCOUNT', 'SATIN_SUBACCOUNT', 
  24.             'IDENT_RCE', 'ICO1'
  25.           ) 
  26.           AND (
  27.             SELECT 
  28.               CHP.CRI_KEY_PARENT 
  29.             FROM 
  30.               S4C_CRITERIA_HERITANCE CHP 
  31.             WHERE 
  32.               CHP.CRI_KEY_CHILD = CRI.CRI_KEY
  33.           ) IN (
  34.             SELECT 
  35.               CBB.CRI_KEY 
  36.             FROM 
  37.               S4C_CRITERIA CBB 
  38.             WHERE 
  39.               CBB.SPRM_KEY = P.SPRM_PARENT_KEY
  40.           )
  41.         )
  42.       ) 
  43.       AND (
  44.         (
  45.           CRI.CRI_TYPE = 'IDENT' 
  46.           AND CRI.CRI_VALUE = '00010525'
  47.         )
  48.       )
  49.     )
  50. ) 
  51. SELECT 
  52.   DISTINCT CPRM.CPRM_KEY AS PRM_ID, 
  53.   'CLIENT' AS PRM_TYPE 
  54. FROM 
  55.   CPRM_TABLE CPRM 
  56. UNION ALL 
  57. SELECT 
  58.   DISTINCT PRM.SPRM_KEY AS PRM_ID, 
  59.   'SUB' AS PRM_TYPE 
  60. FROM 
  61.   SPRM_TABLE PRM START WITH PRM.SPRM_PARENT_KEY IS NULL 
  62.   AND PRM.CPRM_KEY IN (
  63.     SELECT 
  64.       CLI_PRM.CPRM_KEY 
  65.     FROM 
  66.       CPRM_TABLE CLI_PRM
  67.   ) CONNECT BY PRIOR PRM.SPRM_KEY = PRM.SPRM_PARENT_KEY

If Someone could help please? and if someone could explain why does this works in Oracle sql develper but not in java code with hibernate?

答案1

得分: 1

以下是您要翻译的内容：

"你不能这样做：

AND (
SELECT
CHP.CRI_KEY_PARENT
FROM
S4C_CRITERIA_HERITANCE CHP
WHERE
CHP.CRI_KEY_CHILD = CRI.CRI_KEY
) IN (
SELECT
CBB.CRI_KEY
FROM
S4C_CRITERIA CBB
WHERE
CBB.SPRM_KEY = P.SPRM_PARENT_KEY
)

IN运算符的左侧需要一个标量，而您提供的子查询显然返回了多行。

您需要将这两个查询合并为一个：

AND EXISTS (
SELECT *
FROM S4C_CRITERIA_HERITANCE CHP ,
S4C_CRITERIA CBB
WHERE CHP.CRI_KEY_CHILD = CRI.CRI_KEY
AND CBB.CRI_KEY = CHP.CRI_KEY_PARENT
AND CBB.SPRM_KEY = P.SPRM_PARENT_KEY
)"

英文:

You can't do this:

  1.       AND (
  2.             SELECT 
  3.               CHP.CRI_KEY_PARENT 
  4.             FROM 
  5.               S4C_CRITERIA_HERITANCE CHP 
  6.             WHERE 
  7.               CHP.CRI_KEY_CHILD = CRI.CRI_KEY
  8.           ) IN (
  9.             SELECT 
  10.               CBB.CRI_KEY 
  11.             FROM 
  12.               S4C_CRITERIA CBB 
  13.             WHERE 
  14.               CBB.SPRM_KEY = P.SPRM_PARENT_KEY
  15.           )

The left side of the IN operator expects a scalar, and you are providing a subquery that is apparently returning multiple rows.

You will need to combine these two queries into one:

  1.   AND EXISTS (
  2.         SELECT *
  3.           FROM S4C_CRITERIA_HERITANCE CHP ,
  4.                S4C_CRITERIA CBB
  5.          WHERE CHP.CRI_KEY_CHILD = CRI.CRI_KEY
  6.            AND CBB.CRI_KEY = CHP.CRI_KEY_PARENT 
  7.            AND CBB.SPRM_KEY = P.SPRM_PARENT_KEY
  8.         )

huangapple
  • 本文由 发表于 2023年4月20日 02:45:04
  • 转载请务必保留本文链接：https://go.coder-hub.com/76057884.html
  • java
  • oracle
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定