Hi, I am having an issue on my calculation subqueries with sum() and they give me the error in the subject line.
My queries is pasted below, your help will be highly appreciated.
SELECT
account.id AS accid,
account.accno,
person.fileno,
person.fileno_old AS oldfileno,
(
SELECT userpick.name
FROM ikat3dbanderson.userpick
WHERE userpick.id = person.title
) AS title,
(
SELECT ikatpick.name
FROM ikat3dbanderson.ikatpick
WHERE ikatpick.id = person.gender
) AS gender,
person.firstname,
person.initials,
person.lastname,
person.idno,
person.dob,
person.employer,
address.mail1,
address.mail2,
address.mail3,
address.mailpcode AS mailpcode,
address.email,
address.street1,
address.street2,
address.street3,
address.streetpcode AS streetpcode,
address.url AS website,
phone.phone1 AS WORK,
phone.phone2 AS home,
phone.phone3 AS cell,
phone.fax,
(
SELECT accgroup.name
FROM ikat3dbanderson.accgroup
WHERE accgroup.id = account.accgrp
) AS accgroup,
(
SELECT coalesce(sum(tranc.patportion + tranc.maportion) - sum(ISNULL(
(
SELECT sum(coalesce(allocations.patallocation, 0))
FROM ikat3dbanderson.allocations
WHERE allocations.totran = tranc.id
), N'0')) - sum(ISNULL(
(
SELECT sum(coalesce(allocations.maallocation, 0))
FROM ikat3dbanderson.allocations
WHERE allocations.totran = tranc.id
), N'0')) - ISNULL(
(
SELECT sum(crednote.amt)
FROM ikat3dbanderson.crednote
WHERE crednote.account = account.id AND crednote.provider IN
(
SELECT provider.id
FROM ikat3dbanderson.provider
WHERE provider.pcentre = 1
)
), N'0'), N'0')
FROM ikat3dbanderson.transactions AS tranc, ikat3dbanderson.person AS patient
WHERE
tranc.personid = patient.id AND
tranc.provid IN
(
SELECT provider.id
FROM ikat3dbanderson.provider
WHERE provider.pcentre =
(
SELECT provider.pcentre
FROM ikat3dbanderson.provider
WHERE provider.id = 1
)
) AND
patient.account = account.id AND
(ISNULL(tranc.delflag, '1899-12-31 00:00:00') < '1900-01-01 00:00:00')
) AS balance,
(
SELECT medaid.name + N' ' + maplan.name
FROM ikat3dbanderson.medaid, ikat3dbanderson.maplan
WHERE medaid.id = maplan.scheme AND maplan.id = person.medaid
) AS medaid,
(
SELECT maplan.name
FROM ikat3dbanderson.medaid, ikat3dbanderson.maplan
WHERE medaid.id = maplan.scheme AND maplan.id = person.medaid
) AS maplan,
person.mano AS medaidno,
col_colourcode.col_id,
col_colourcode.col_description,
col_colourcode.col_colour,
col_colourcode.col_fontcolour
FROM
ikat3dbanderson.account
LEFT JOIN ikat3dbanderson.col_colourcode
ON account.col_id = col_colourcode.col_id,
ikat3dbanderson.person,
ikat3dbanderson.address,
ikat3dbanderson.phone,
ikat3dbanderson.accgroup
WHERE
account.persid = person.id AND
person.address = address.id AND
person.phone = phone.id AND
account.accgrp = accgroup.id AND
person.acc_holder_flag = 'T' AND
upper(account.accno) LIKE upper(N'%%') AND
upper(person.firstname) LIKE upper(N'%%') AND
upper(person.lastname) LIKE upper(N'%%') AND
upper(person.idno) LIKE upper(N'%%') AND
upper(person.fileno) LIKE upper(N'%%') AND
upper(phone.phone1) LIKE upper(N'%%') AND
upper(phone.phone2) LIKE upper(N'%%') AND
upper(address.street1) LIKE upper(N'%%') AND
upper(address.mail1) LIKE upper(N'%%') AND
CASE
WHEN ('F' = 'T') THEN
CASE
WHEN (upper(
(
SELECT medaid.name + N' ' + maplan.name
FROM ikat3dbanderson.medaid, ikat3dbanderson.maplan
WHERE medaid.id = maplan.scheme AND maplan.id = person.medaid
)) LIKE upper(N'%%')) THEN 1
ELSE 0
END
ELSE N'1'
END <> 0 AND
CASE
WHEN ('F' = 'T') THEN
CASE
WHEN (FORMAT(person.dob, N'%d/%m/%Y') = '') THEN 1
ELSE 0
END
ELSE N'1'
END <> 0 AND
coalesce(account.col_id, -1) LIKE N'%' AND
account.active = 'T'
ORDER BY account.accno