Topic: SQL отчет
В программе формируется SQL отчет:
SELECT DISTINCT
dogovor.nomer AS `dogovor.nomer`,
dogovor.data AS `dogovor.data`,
(SELECT kontr.name FROM kontr WHERE kontr.id=dogovor.id_kontr) AS 'dogovor.kontr',
(SELECT rashod.vid FROM rashod LEFT OUTER JOIN statya ON statya.id_rashod=rashod.id WHERE statya.id=dogovor.id_statya ) AS 'dogovor.stat',
(SELECT statya.st FROM statya WHERE statya.id=dogovor.id_statya ) AS 'dogovor.stat_calc',
(SELECT user.user FROM user WHERE user.id=dogovor.id_user) AS 'dogovor.user_calc',
buh_doc.vid AS `buh_doc.vid`,
buh_doc.nomer AS `buh_doc.nomer`,
buh_doc.data AS `buh_doc.data`,
buh_doc.summa AS `buh_doc.summa`,
buh_doc.datapay AS `buh_doc.datapay`,
buh_doc.pay AS `buh_doc.pay`,
currency.curr AS `currency.curr`,
dogovor.usl_opl AS `dogovor.usl_opl`,
(SELECT block.block FROM block WHERE buh_doc.id_block=block.id) AS `block.block`,
(SELECT SUM(summa) FROM buh_doc
WHERE buh_doc.datapay={MonthCalendar1} AND block.block="В" AND buh_doc.pay="НЕТ") AS 'buh_doc.datapayB',
(SELECT SUM(summa) FROM buh_doc
WHERE buh_doc.datapay={MonthCalendar1} AND block.block="Н" AND buh_doc.pay="НЕТ") AS 'buh_doc.datapayH',
(SELECT SUM(summa) FROM buh_doc
WHERE buh_doc.datapay={MonthCalendar1} AND block.block="А" AND buh_doc.pay="НЕТ") AS 'buh_doc.datapayA'
FROM buh_doc
LEFT OUTER JOIN dogovor ON buh_doc.id_dogovor=dogovor.id
LEFT OUTER JOIN block ON buh_doc.id_block=block.id
LEFT OUTER JOIN currency ON buh_doc.id_currency=currency.id
WHERE buh_doc.datapay={MonthCalendar1} AND buh_doc.pay="НЕТ"
Не работают строки:
(SELECT SUM(summa) FROM buh_doc
WHERE buh_doc.datapay={MonthCalendar1} AND block.block="В" AND buh_doc.pay="НЕТ") AS 'buh_doc.datapayB',
(SELECT SUM(summa) FROM buh_doc
WHERE buh_doc.datapay={MonthCalendar1} AND block.block="Н" AND buh_doc.pay="НЕТ") AS 'buh_doc.datapayH',
(SELECT SUM(summa) FROM buh_doc
WHERE buh_doc.datapay={MonthCalendar1} AND block.block="А" AND buh_doc.pay="НЕТ") AS 'buh_doc.datapayA'
Поправьте меня, что я делаю не так.