1

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
yjtan
  • 193
  • 1
  • 1
  • 6
  • where is the rest of the query? –  Apr 03 '14 at 19:15
  • The rest of the query is really long (like several hundred more lines) and features more of the same thing. The problem is in how I use SELECT foo, (SELECT bar) AS CountOrdersAfterEnroll and so on. But if you think it's helpful I can easily upload it? – yjtan Apr 03 '14 at 19:24
  • Just include your `FROM` and `JOIN` clauses so it will be easier to identify how you are connecting it to the rest of it. –  Apr 03 '14 at 19:46
  • Okay, I'll do that. There isn't a FROM on the inside of the outermost SELECT, though, it's a virtual table. – yjtan Apr 03 '14 at 20:06

0 Answers0