0

What's wrong on my code here? I am getting an error from Redshift when executing these lines.

SELECT
COUNT (
        DISTINCT (
            CASE
                WHEN EXISTS(
                    (
                        select
                            1
                        from
                            (
                                select
                                    ci.order_no,
                                    count(ci.booking_location_id) as cnt
                                from
                                    oms_core_inventory_booking_detail ci
                                group by
                                    ci.order_no
                            ) cibd
                            where cibd.cnt > 1
                            and co.order_no = cibd.order_no
                    )
                ) THEN co.order_no
                else NULL
            END
        )
    ) AS "SP受注数"
from
table co

And I am getting an error like this

This type of correlated subquery pattern is not supported due to internal error

Thank you for the incoming help.

Saleh

Saleh Manggana
  • 1
  • 1
  • As per first look - too many `select`'s! I'd suggest to use `DENSE_RANK()` to get the groups and counts inside each group, but i don't know if it's available in redshift. – Maciej Los Jul 21 '20 at 06:58
  • Please provide sample data, desired results, and an explanation of what you want to do. – Gordon Linoff Jul 21 '20 at 11:38

1 Answers1

0

Are you looking for the count of order ids that have more than one location id?

SELECT COUNT(*) as order_id_cnt FROM (
SELECT ci.order_no,
       COUNT(ci.booking_location_id) AS cnt
FROM oms_core_inventory_booking_detail ci
GROUP BY ci.order_no
HAVING COUNT(ci.booking_location_id)>1
)

Suggestions,

  1. Please format code before posting.
  2. The question is unclear, post couple of lines of representative dataset and what is the expected output.
Narmadha
  • 55
  • 1
  • 5