Hello,
I'm using 11g database.
I have a table called company_product this table has a recursive behavior, it has a column called parent_prod so i can list a product as a child to another product.
Another table called ret_price_list_items where i could reference a product and specify it's retail price.
For the first table company_product i need to get the number of childs for every product if it has childs , the way i was doing it was this:
SELECT
P.COM_PROD_ID,
P.PROD_ID,
GET_COM_PROD_NAME(P.COM_PROD_ID) PROD_NAME,
P.MESURE_ID,
P.PURCH_PRICE,
MD.MESURED_ID,
MD.NAME,
nvl(MD.FACTOR,1),
P.PARENT_PROD,
(SELECT COUNT(*) FROM COMPANY_PRODUCT PS WHERE PS.PARENT_PROD = P.COM_PROD_ID) HAS_CHILDS
FROM
COMPANY_PRODUCT P LEFT OUTER JOIN MESURE_D MD
ON P.MESURE_ID = MD.MESURE_ID AND
MD.BASE = 1;
This was working fine, but now i wanted to create a view to link both company_product and ret_price_list_items so i could get all information in one view:
SELECT
P.COM_PROD_ID COM_PROD_ID,
P.PROD_ID DOMAIN_PROD_ID,
GET_COM_PROD_NAME(P.COM_PROD_ID) PROD_NAME,
P.MESURE_ID,
P.PURCH_PRICE,
MD.MESURED_ID BASE_UNIT_ID,
MD.NAME BASE_UNIT,
NVL(MD.FACTOR,1) BASE_UNIT_FACTOR,
NVL(P.PARENT_PROD,0)PARENT_PROD,
(SELECT COUNT(*) FROM COMPANY_PRODUCT PS WHERE PS.PARENT_PROD = P.COM_PROD_ID) HAS_CHILDS,
RPLI.RPL_ID,
RPLI.RPLI_ID,
RPLI.PROD_ID,
RPLI.RET_UNIT,
RPLI.RET_UNIT_FACTOR,
RPLI.RET_PRICE,
RPLI.MARKUP_PCT,
RPLI.PROD_MESURE,
RPLI.VAT_PCT
FROM
COMPANY_PRODUCT P LEFT OUTER JOIN MESURE_D MD
ON P.MESURE_ID = MD.MESURE_ID AND
MD.BASE = 1 ,
RET_PRICE_LIST_ITEMS RPLI
WHERE
P.COM_PROD_ID = RPLI.PROD_ID;
But now the HAS_CHILDS column return 0 for the products that have childs.
What am i doing wrong here?
Thank you
Gado