I'm using Squirrel SQL, with the database in Sybase's SQL Anywhere 12. I'm trying to build a report that pulls data from multiple different tables and presents them in a 1 by n table - not best practice, but the only option I can think of.
But here's the problem. I'm trying to join Part A and Part B together, with a simple INNER JOIN of parta.Name = partb.Name. Parts A and B run fine individually, but once the JOIN occurs, wham, Invalid Use of Aggregate Function. After testing I've found that Part A is the problem. The code is below - I took out everything below this set of code and it still hit me with invalid aggregate use. I don't know what I'm doing wrong, especially since parts A and B both work in isolation! Does anyone have any thoughts?
SELECT * FROM
(
SELECT
'Company A' AS MSPName
,
-- COUNT ORDERS MADE BY MSP COMPANIES AFTER THEIR ENROLL DATES -- COUNT ORDERS MADE BY MSP COMPANIES AFTER THEIR ENROLL DATES -- COUNT ORDERS MADE BY MSP COMPANIES AFTER THEIR ENROLL DATES
(
SELECT
COUNT(DISTINCT order_id) AS CountOrdersAfterEnroll
FROM
reporting.msp msp
INNER JOIN
iquser.dm_order_d orders
ON
msp.ClientID = orders.client_id
WHERE
-- right company
msp.ClientName /*TARGETING*/ LIKE 'Company A%'
AND
-- right time
((date_filled IS NOT NULL AND date_filled >= msp.EnrollDate)
-- filled it
OR
-- we didn't fill it
(date_filled IS NULL AND order_create_date >= msp.EnrollDate))
AND
order_status_id IN (2, 5, 8, 9, 10, 12)
-- these are the only ones we care about
) AS CountOrdersAfterEnroll
-- a lot of other similar code
(
SELECT
CASE
WHEN CountFilledOrdersAfterEnroll = 0
THEN NULL
ELSE
SUM(CASE
WHEN field_value = 'More than 20'
THEN 21.000
ELSE
CAST(field_value AS DECIMAL(12, 4)) --12 digits long, 4 behind the decimal mark. I'm not sure how this handles mantissa length retention, though.
END) * 1.0 -- for floats, JIC the DECIMAL cast didn't work out?
/ --DIVIDE BY
CountFilledOrdersAfterEnroll
END
FROM
-- Q&A
iquser.custom_field CF
LEFT JOIN
--answers
iquser.custom_field_value CFV
ON
CF.custom_field_id = CFV.custom_field_id
-- order details for these Q&A
LEFT JOIN
iquser.dm_order_d orders
ON
CFV.order_id = orders.order_id
-- now Q&A from ONLY the MSPs
-- right company
INNER JOIN
reporting.msp msp
ON
msp.ClientID = CF.client_id
WHERE
-- right company
msp.ClientName /*TARGETING*/ LIKE 'Company A%'
AND
-- right time
((date_filled IS NULL AND order_create_date >= msp.EnrollDate)
-- we didn't fill it
OR
-- we did fill it
(date_filled IS NOT NULL and date_filled >= msp.EnrollDate))
AND
CFV.field_value != 'zShared Services Only - SSNA'
AND
CF.field_name /*QUESTION TARGETING*/ LIKE 'How many candidates did we extend offers to after the client had selected%'
) AS CandidatesExtendedPerFilled
) parta
LEFT JOIN
(
SELECT
'Company A' AS MSPName
,
--other code of PartB
) partb
ON
parta.MSPName = partb.MSPName