1

I'm trying to create a DataSet for a Quicksight analysis. I'm using a custom query on a Postgresql Data Source.

Problem comes whenever I use Postgresql functions inside my query. The same query does, in fact, work on IntelliJ DataGrip and PgAdmin, for example, but I get a pretty unexpected error that states as follows:

QuickSight could not generate any output column after applying transformation. Check transform(s) and try again.

SELECT
    users.name AS "User",
    accounts.id AS "Account ID",
    groups.name AS "Group",
    account_balances.balance AS "Account Balance",
    (
        SELECT
            account_balances.balance
        FROM
            account_balances
        WHERE
            account_balances.account_id = (
                SELECT
                    accounts.id
                FROM
                    accounts
                WHERE
                    accounts.user_id = users.id
                AND
                    accounts.account_type_id = 11
            )
    ) AS "D Account Balance",
    (
        SELECT
            decimal_value
        FROM
            user_custom_field_values
        WHERE
            user_custom_field_values.field_id = (
                SELECT
                    user_custom_fields.id
                FROM
                    user_custom_fields
                WHERE
                    user_custom_fields.internal_name = 'capacity'
            )
        AND
            owner_id = users.id
    ) AS "Capacity",
    (
        SELECT
            decimal_value
        FROM
            user_custom_field_values
        WHERE
            user_custom_field_values.field_id = (
                SELECT
                    user_custom_fields.id
                FROM
                    user_custom_fields
                WHERE
                    user_custom_fields.internal_name = 'Turnover'
            )
        AND
            owner_id = users.id
    ) AS "Turnover",
    (
        SELECT value
            FROM
                 user_custom_field_possible_values
            WHERE
                id in (
                    SELECT
                        possible_value_id
                    FROM
                         user_enum_values
                    WHERE
                        owner_id in (
                            SELECT
                                user_custom_field_values.id
                            FROM
                                user_custom_field_values
                            JOIN
                                user_custom_fields ON user_custom_fields.id=user_custom_field_values.field_id
                            WHERE
                                owner_id = users.id
                            AND
                                user_custom_fields.internal_name = 'contractType'
                        )
                )
        LIMIT 1
    ) AS "Contract Type",
    (
        SELECT
            COUNT(id)
        FROM
            ads
        WHERE
            owner_id = users.id
        AND
            (ads.creation_date BETWEEN NOW() - interval '6 months' AND NOW())
    ) AS "ADs last 6 months",
    (
        SELECT
            SUM(transfers.amount)
        FROM
            transfers
        WHERE
            transfers.to_id = accounts.id
        AND
            transfers.chargeback_of_id IS NULL
        AND
            transfers.charged_back_by_id IS NULL
        AND
            (transfers.date BETWEEN NOW() - interval '3 months' AND NOW())
    ) AS "Sells last 3 months",
    (
        SELECT
            SUM(transfers.amount)
        FROM
            transfers
        WHERE
            transfers.to_id = accounts.id
        AND
            transfers.chargeback_of_id IS NULL
        AND
            transfers.charged_back_by_id IS NULL
        AND
            (transfers.date BETWEEN NOW() - interval '6 months' AND NOW())
    ) AS "Sells last 6 months",
    (
        SELECT
            SUM(transfers.amount)
        FROM
            transfers
        WHERE
            transfers.to_id = accounts.id
        AND
            transfers.chargeback_of_id IS NULL
        AND
            transfers.charged_back_by_id IS NULL
        AND
            (transfers.date BETWEEN NOW() - interval '12 months' AND NOW())
    ) AS "Sells last years",
    (
        SELECT
            SUM(transfers.amount)
        FROM
            transfers
        WHERE
            transfers.to_id = accounts.id
        AND
            transfers.chargeback_of_id IS NULL
        AND
            transfers.charged_back_by_id IS NULL
    ) AS "Sells",
    (
        SELECT
            SUM(transfers.amount)
        FROM
            transfers
        WHERE
            transfers.from_id = accounts.id
        AND
            transfers.chargeback_of_id IS NULL
        AND
            transfers.charged_back_by_id IS NULL
        AND
            (transfers.date BETWEEN NOW() - interval '3 months' AND NOW())
    ) AS "Purchases last 3 months",
    (
        SELECT
            SUM(transfers.amount)
        FROM
            transfers
        WHERE
            transfers.from_id = accounts.id
        AND
            transfers.chargeback_of_id IS NULL
        AND
            transfers.charged_back_by_id IS NULL
        AND
            (transfers.date BETWEEN NOW() - interval '6 months' AND NOW())
    ) AS "Purchases last 6 months",
    (
        SELECT
            SUM(transfers.amount)
        FROM
            transfers
        WHERE
            transfers.from_id = accounts.id
        AND
            transfers.chargeback_of_id IS NULL
        AND
            transfers.charged_back_by_id IS NULL
        AND
            (transfers.date BETWEEN NOW() - interval '12 months' AND NOW())
    ) AS "Purchases last year",
    (
        SELECT
            SUM(transfers.amount)
        FROM
            transfers
        WHERE
            transfers.from_id = accounts.id
        AND
            transfers.chargeback_of_id IS NULL
        AND
            transfers.charged_back_by_id IS NULL
    ) AS "Purchases",
    (
        SELECT
            account_limit_logs.credit_limit
        FROM
            account_limit_logs
        WHERE
            account_limit_logs.account_id = accounts.id
        ORDER BY
            account_limit_logs.id DESC
        LIMIT 1
    ) AS "Account Limit"
FROM
    users
JOIN
    accounts ON accounts.user_id = users.id
JOIN
    groups ON groups.id = users.user_group_id
JOIN
    account_balances ON accounts.id = account_balances.account_id
WHERE
    accounts.account_type_id = 4

Problem seems to be the - interval 'fooBar' part. Without that, the query goes smooth.

Anybody with a deep enough knowledge of AWS Quicksight and its secrets, care to help?

Even a different SQL solution to obtain the same result would be acceptable.

Thanks!

Andrea Carboni
  • 76
  • 6
  • 1
    See if it makes a difference by casting it as `'12 months'::interval` instead? Or try brackets to help guide it e.g. `(NOW() - '12 months'::interval)`? – 404 Sep 24 '19 at 10:07
  • @404 I just tried, good advice, but unfortunately didn't fix the problem (both solutions). What amazes me is that I'm getting that error with no details whatsoever. – Andrea Carboni Sep 24 '19 at 10:27
  • 1
    Can you try some simple queries to find out if it's really the `interval` bit that's causing it? For example try `SELECT interval '12 months';` to see if that works. If it does then it might be the arithmetic that's failing (I've seen manipulation of dates using intervals fail in some testing frameworks despite being valid sql) - does `SELECT NOW() - interval '12 months';` fail? Then if you confirm it's that, we can think of ways around it. – 404 Sep 24 '19 at 10:37
  • @404 sure thing! I just tried `SELECT interval '12 months';` and just as you said, it gives me an error. Different one, but it actually explains what it doesn't like: `None of the column types in your data source are supported` Which becomes the error on my first post if I run `SELECT NOW() - interval '12 months';`. I presume I'm stuck with a red light on Postgresql functions in AWS Quicksight – Andrea Carboni Sep 24 '19 at 11:24
  • 1
    See if this works: `SELECT now() + '-12 months';` - I've set it to a negative number as (on my local postgres) it works with addition but not subtraction. – 404 Sep 24 '19 at 11:59
  • @404 Man I owe you big time! – Andrea Carboni Sep 24 '19 at 12:53

0 Answers0