[SQL] How can I filter the result by a value in my script?

I'm trying to get a register in Oracle DB when I try to do some joins and to the table AD_ORG SUC (have the "suc.value" which is the stores that have the registers) returns me 0 rows. Now my script is like this (it gives me all the registers which are 3 and the count of total stores that got the planimetry:

WITH BRANCHES_BY_ASSORTMENT AS (
SELECT ZI.SGR_ZONE_IMAGE_ID ASSORTMENT_ID,
MZ.SGR_MASTER_ZONE_ID MASTER_ZONE_ID,
COUNT(*) TOTAL_INTRODUCTIONS
FROM ADEMPIERE.SGR_ZONE_ORG ZO
JOIN ADEMPIERE.SGR_MASTER_ZONE MZ
ON MZ.SGR_MASTER_ZONE_ID = ZO.SGR_MASTER_ZONE_ID
JOIN ADEMPIERE.SGR_ZONE_IMAGE ZI
ON ZI.SGR_ZONE_IMAGE_ID = ZO.SGR_ZONE_IMAGE_ID
JOIN ADEMPIERE.AD_ORG SUC
ON SUC.AD_ORG_ID = ZO.SGR_AD_ORG_ID
JOIN ADEMPIERE.AD_ORGINFO SUCINF
ON SUCINF.AD_ORG_ID = SUC.AD_ORG_ID
JOIN ADEMPIERE.AD_ORGTYPE SUCTYPE
ON SUCTYPE.AD_ORGTYPE_ID = SUCINF.AD_ORGTYPE_ID
WHERE ZO.ISACTIVE = 'Y'
AND MZ.ISACTIVE = 'Y'
AND ZI.ISACTIVE = 'Y'
AND SUC.ISACTIVE = 'Y'
AND SUC.AD_CLIENT_ID = 1000000
AND SUC.ISSUMMARY = 'N'
AND SUCTYPE.DESCRIPTION = 'SUR'
AND SUCINF.SGR_ORG_FORMAT IN ('F', 'E')
GROUP BY ZI.SGR_ZONE_IMAGE_ID, MZ.SGR_MASTER_ZONE_ID
), PRODUCTS_BY_ASSORTMENT AS (
SELECT ZI.SGR_ZONE_IMAGE_ID ZONE_IMAGE_ID,
ZI.FORMAT FORMAT,
COUNT(P.M_PRODUCT_ID) TOTAL_PRODUCTS
FROM ADEMPIERE.SGR_ZONE_IMAGE ZI
JOIN ADEMPIERE.SGR_MASTER_ZONE MZ
ON ZI.SGR_MASTER_ZONE_ID = MZ.SGR_MASTER_ZONE_ID
JOIN (SELECT 'D' AS zona_format, 'A' AS producto_format
FROM dual
UNION ALL
SELECT 'D', 'B'
FROM dual
UNION ALL
SELECT 'D', 'C'
FROM dual
UNION ALL
SELECT 'D', 'D'
FROM dual
UNION ALL
SELECT 'C', 'A'
FROM dual
UNION ALL
SELECT 'C', 'B'
FROM dual
UNION ALL
SELECT 'C', 'C'
FROM dual
UNION ALL
SELECT 'B', 'A'
FROM dual
UNION ALL
SELECT 'B', 'B'
FROM dual
UNION ALL
SELECT 'A', 'A' FROM dual) jerarquia
ON ZI.FORMAT = jerarquia.zona_format
JOIN ADEMPIERE.M_PRODUCT P
ON P.SGR_FORMATTYPE = jerarquia.producto_format
JOIN ADEMPIERE.M_PRODUCT_CATEGORY SF
ON SF.M_PRODUCT_CATEGORY_ID = P.M_PRODUCT_CATEGORY_ID
JOIN ADEMPIERE.SGR_MASTER_ZONE MZ2
ON MZ2.SGR_MASTER_ZONE_ID = SF.SGR_MASTER_ZONE_ID
WHERE ZI.ISACTIVE = 'Y'
AND MZ.ISACTIVE = 'Y'
AND P.ISACTIVE = 'Y'
AND SF.ISACTIVE = 'Y'
AND MZ2.ISACTIVE = 'Y'
AND P.SGR_FORMATTYPE != 'L'
AND MZ.SGR_MASTER_ZONE_ID = MZ2.SGR_MASTER_ZONE_ID
GROUP BY ZI.SGR_ZONE_IMAGE_ID, ZI.FORMAT
) SELECT SEC.VALUE SECCION,
SEC.NAME CATEGORIA,
ZI.FORMAT FORMATO,
ZI.BODIES CUERPO,
I1.AD_IMAGE_UU IMAGEN_URL,
I1.CREATED IMAGEN_FECHA,
NVL(TBBA.TOTAL_INTRODUCTIONS, 0) LOCALES,
NVL(TPBA.TOTAL_PRODUCTS, 0) ESTADISTICOS
FROM ADEMPIERE.SGR_ZONE_IMAGE ZI
JOIN ADEMPIERE.SGR_MASTER_ZONE MZ
ON MZ.SGR_MASTER_ZONE_ID = ZI.SGR_MASTER_ZONE_ID
JOIN ADEMPIERE.M_PRODUCT_CATEGORY SEC
ON SEC.M_PRODUCT_CATEGORY_ID = MZ.SGR_SECTION
LEFT JOIN ADEMPIERE.AD_IMAGE I1
ON I1.AD_IMAGE_ID = ZI.IMAGE
LEFT JOIN ADEMPIERE.AD_IMAGE I2
ON I2.AD_IMAGE_ID = ZI.FLAT
LEFT JOIN BRANCHES_BY_ASSORTMENT TBBA
ON TBBA.MASTER_ZONE_ID = MZ.SGR_MASTER_ZONE_ID
AND TBBA.ASSORTMENT_ID = ZI.SGR_ZONE_IMAGE_ID
LEFT JOIN PRODUCTS_BY_ASSORTMENT TPBA
ON TPBA.ZONE_IMAGE_ID = ZI.SGR_ZONE_IMAGE_ID
WHERE ZI.ISACTIVE = 'Y'
AND MZ.ISACTIVE = 'Y'
AND SEC.VALUE = :section
AND MZ.NAME = :zone
ORDER BY 3, 4, 6


The question is: How can I filter by suc.value = :storeCode? Because if I do this joins:

LEFT JOIN SGR_CLUSTER_ZONE CZ
ON MZ.SGR_MASTER_ZONE_ID = CZ.SGR_MASTER_ZONE_ID
LEFT JOIN SGR_ZONE_BRANCH ZB
ON CZ.SGR_CLUSTER_ZONE_ID = ZB.SGR_CLUSTER_ZONE_ID
LEFT JOIN AD_ORG SUC
ON SUC.AD_ORG_ID = ZB.SGR_AD_ORG_ID
WHERE ZI.ISACTIVE = 'Y'
AND MZ.ISACTIVE = 'Y'
AND SEC.VALUE = :section
AND MZ.NAME = :zone
AND SUC.VALUE = :storeCode
ORDER BY 3, 4, 6


Got me 0 rows as results. | seccion | formato | cuerpo | Fecha | imagenFecha | locales | estadisticos | | -------- | -------- | -------- | -------- | -------- | --------| -------- | | | | | | | | | And if I try without the part "SUC.VALUE = :storeCode" I got the same 3 results as always. | seccion | formato | cuerpo | Fecha | imagenFecha | locales | estadisticos | | -------- | -------- | -------- | -------- | -------- | --------| -------- | | 03 | D | 1 | 2025-08-13 | Cell 5 | 188 | 56 | | 04 | D | 2 | 2025-08-14 | Cell 5 | 18 | 32 | | 05 | E | 3 | 2025-08-13 | Cell 6 | 96 | 54 |

And if in case I put SUC.VALUE in BRANCHES_BY_ASSORTMENT and try to do the where part got one but the counts of stores turned from 188 to 1 (I think cause the one I type).

The result I want to get is:

seccion formato cuerpo Fecha imagenFecha locales estadisticos
03 D 1 2025-08-13 Cell 5 188 56

Continue reading...