1 Subquery returns more than 1 row - MySQL

Узнай цену своей работы

Формулировка задачи:

Господа, Hola! Есть запросик у меня и все вроде бы было хорошо и он отрабатывал, пока я не влепил подзапросы и он не выдал ошибочку: 1 Subquery returns more than 1 row...
Листинг программы 
  1. SELECT 
  2.   Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos, 
  3.   SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog, 
  4.   ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL, 
  5.   ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  6. FROM
  7.   deal, stfcrd, postemp, prod, zrp
  8. WHERE
  9.   Deal_StfCrdRcd = 35 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  10.   AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  11.   AND prod.Prod_Rcd = deal.Deal_ProdRcd
  12.   AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  13.   AND deal_date =
  14.   (
  15.   SELECT deal_date FROM deal WHERE deal_date BETWEEN '01.10.2017' AND '30.10.2017'
  16.   ) 
  17.  
  18. UNION ALL 
  19.  
  20. SELECT 
  21.   Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos, 
  22.   SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog, 
  23.   ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL, 
  24.   ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  25. FROM
  26.   deal, stfcrd, postemp, prod, zrp
  27. WHERE
  28.   Deal_StfCrdRcd = 36 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  29.   AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  30.   AND prod.Prod_Rcd = deal.Deal_ProdRcd
  31.   AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  32.   AND deal_date = 
  33.   (
  34.   SELECT deal_date FROM deal WHERE deal_date BETWEEN '01.10.2017' AND '30.10.2017'
  35.   )
Пожалуйста о магистры знаний, укажите верный путь... В чем может быть проблема?

Решение задачи: «1 Subquery returns more than 1 row»

textual
Листинг программы 
  1. SET @vardt1 = '01.10.2017';
  2. SET @vardt2 = '01.11.2017';
  3. SET @vardt3 = '2017-10-01';
  4. SET @vardt4 = '2017-10-30';
  5. SET @vardt5 = '2017-11-01';
  6. SET @vardt6 = '2017-11-30';
  7. SET @varid1 = '34';
  8. SET @varid2 = '35';
  9. SET @varid3 = '36';
  10. SET @varid4 = '71';
  11.  
  12. SELECT
  13.   Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  14.   , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  15.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  16.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  17.   , MONTHNAME(@vardt1) AS Mnh
  18. FROM
  19.   deal, stfcrd, postemp, prod, zrp
  20. WHERE
  21.   Deal_StfCrdRcd = @varid1 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  22.   AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  23.   AND prod.Prod_Rcd = deal.Deal_ProdRcd
  24.   AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  25.   AND deal_date BETWEEN @vardt3 AND @vardt4
  26.  
  27. UNION
  28.  
  29. SELECT
  30.   Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  31.   , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  32.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  33.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  34.   , MONTHNAME(@vardt1) AS Mnh
  35. FROM
  36.   deal, stfcrd, postemp, prod, zrp
  37. WHERE
  38.   Deal_StfCrdRcd = @varid2 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  39.   AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  40.   AND prod.Prod_Rcd = deal.Deal_ProdRcd
  41.   AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  42.   AND deal_date BETWEEN @vardt3 AND @vardt4
  43.  
  44. UNION
  45.  
  46. SELECT
  47.   Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  48.   , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  49.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  50.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  51.   , MONTHNAME(@vardt1) AS Mnh
  52. FROM
  53.   deal, stfcrd, postemp, prod, zrp
  54. WHERE
  55.   Deal_StfCrdRcd = @varid3 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  56.   AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  57.   AND prod.Prod_Rcd = deal.Deal_ProdRcd
  58.   AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  59.   AND deal_date BETWEEN @vardt3 AND @vardt4
  60.  
  61. UNION
  62.  
  63. SELECT
  64.   Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  65.   , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  66.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  67.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  68.   , MONTHNAME(@vardt1) AS Mnh
  69. FROM
  70.   deal, stfcrd, postemp, prod, zrp
  71. WHERE
  72.   Deal_StfCrdRcd = @varid4 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  73.   AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  74.   AND prod.Prod_Rcd = deal.Deal_ProdRcd
  75.   AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  76.   AND deal_date BETWEEN @vardt3 AND @vardt4
  77.  
  78. UNION
  79.  
  80. SELECT
  81.   Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  82.   , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  83.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  84.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  85.   , MONTHNAME(@vardt2) AS Mnh
  86. FROM
  87.   deal, stfcrd, postemp, prod, zrp
  88. WHERE
  89.   Deal_StfCrdRcd = @varid1 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  90.   AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  91.   AND prod.Prod_Rcd = deal.Deal_ProdRcd
  92.   AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  93.   AND deal_date BETWEEN @vardt5 AND @vardt6
  94.  
  95. UNION
  96.  
  97. SELECT
  98.   Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  99.   , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  100.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  101.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  102.   , MONTHNAME(@vardt2) AS Mnh
  103. FROM
  104.   deal, stfcrd, postemp, prod, zrp
  105. WHERE
  106.   Deal_StfCrdRcd = @varid2 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  107.   AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  108.   AND prod.Prod_Rcd = deal.Deal_ProdRcd
  109.   AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  110.   AND deal_date BETWEEN @vardt5 AND @vardt6
  111.  
  112. UNION
  113.  
  114. SELECT
  115.   Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  116.   , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  117.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  118.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  119.   , MONTHNAME(@vardt2) AS Mnh
  120. FROM
  121.   deal, stfcrd, postemp, prod, zrp
  122. WHERE
  123.   Deal_StfCrdRcd = @varid3 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  124.   AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  125.   AND prod.Prod_Rcd = deal.Deal_ProdRcd
  126.   AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  127.   AND deal_date BETWEEN @vardt5 AND @vardt6
  128.  
  129. UNION
  130.  
  131. SELECT
  132.   Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  133.   , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  134.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  135.   , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  136.   , MONTHNAME(@vardt2) AS Mnh
  137. FROM
  138.   deal, stfcrd, postemp, prod, zrp
  139. WHERE
  140.   Deal_StfCrdRcd = @varid4 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  141.   AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  142.   AND prod.Prod_Rcd = deal.Deal_ProdRcd
  143.   AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  144.   AND deal_date BETWEEN @vardt5 AND @vardt6

ИИ поможет Вам:

  • решить любую задачу по программированию
  • объяснить код
  • расставить комментарии в коде
  • и т.д
Попробуйте бесплатно

Оцени полезность:

10   голосов , оценка 3.5 из 5

Нужна аналогичная работа?

Оформи быстрый заказ и узнай стоимость

Бесплатно
Оформите заказ и авторы начнут откликаться уже через 10 минут