if i don't use the subquery for count(distinct *) gets the data correct but takes too long, if I use a subquery like this I get the same count for each row an should be different..how is the correct approach..
SELECT
ITEMS.ITEM_NUMBER,
ESIT.DESCRIPTION as DESC_ITEM,
(SELECT count( DISTINCT ASOC1.ORGANIZATION_ID)
FROM
egp_system_items_b ITEMS,
EGP_ITEM_ORG_ASSOCIATIONS ASOC1
WHERE ITEMS.inventory_item_id = ASOC1.inventory_item_id
) as NUM_UDN
FROM
egp_system_items_b ITEMS,
EGP_SYSTEM_ITEMS_TL ESIT
WHERE ITEMS.inventory_item_id = esit.inventory_item_id
AND esit.LANGUAGE in ('E')
GROUP BY
ITEMS.ITEM_NUMBER,
ESIT.DESCRIPTION
The table looks like this..
It should look like this
