Select subquery condition in where statement

oracleselectsubquerywhere

I am getting this error:

ORA-06550: line 43, column 16: ORA-00904: "STOCK_BALANCE": invalid identifier

…for the following query:

select PARTY_ID,
       PARTY_NAME,
       IT_ID,
       IT_ID || ' - ' || IT_DESC || ' - ' || IT_BRAND,
       (select SUM(BALANCE)
          FROM (
            select 
             WEAVING_YARN_TR.TR_ID,
             WEAVING_YARN_TR.GP_NO,
             WEAVING_YARN_TR.YR_ID,
             WEAVING_YARN_GATEPASS.TO_FROM as PARTY,
             WEAVING_YARN_GATEPASS.GP_DATE, 
             WEAVING_YARN_TR.BAGS_IN,
             WEAVING_YARN_TR.BAGS_OUT,
             NVL(WEAVING_YARN_TR.BAGS_OUT,0)-NVL(WEAVING_YARN_TR.BAGS_IN,0) as BALANCE
            from WEAVING_YARN_TR,WEAVING_YARN_GATEPASS
            where WEAVING_YARN_TR.GP_NO=WEAVING_YARN_GATEPASS."GP_NO." and WEAVING_YARN_TR.YR_ID=WEAVING_YARN_ITEMS.IT_ID and WEAVING_YARN_GATEPASS.TO_FROM=WEAVING_PARTIES.PARTY_ID

          UNION ALL

          select 
           null as TR_ID,
           WEAVING_WARP_SET.SET_ID,
           WEAVING_WARP_SET.YARN_ITEM,
           WEAVING_WARP_SET.PARTY,
           WEAVING_WARP_SET.RECEIVED_DATE,
           WEAVING_WARP_SET.TOTAL_BAGS_CONSUMED,
           null as BAGS_OUT,
           NVL(null,0)-NVL(WEAVING_WARP_SET.TOTAL_BAGS_CONSUMED,0) as BALANCE
          from WEAVING_WARP_SET
          where WEAVING_WARP_SET.YARN_ITEM=WEAVING_YARN_ITEMS.IT_ID and WEAVING_WARP_SET.PARTY= WEAVING_PARTIES.PARTY_ID
          ) t
        ) AS STOCK_BALANCE
FROM WEAVING_PARTIES
  CROSS JOIN WEAVING_YARN_ITEMS;
WHERE STOCK_BALANCE is not null

Even when I remove the AS as suggested by Vérace I still receive an error message:

...
    from WEAVING_WARP_SET
    where WEAVING_WARP_SET.YARN_ITEM=WEAVING_YARN_ITEMS.IT_ID and WEAVING_WARP_SET.PARTY= WEAVING_PARTIES.PARTY_ID
    ) t

    ) STOCK_BALANCE

    FROM WEAVING_PARTIES
    CROSS JOIN WEAVING_YARN_ITEMS
....

Error message:

ORA-06550: line 41, column 19: ORA-00904: "STOCK_BALANCE": invalid identifier

Verace Still getting this error. ORA-20999: Failed to parse SQL query!

ORA-06550: line 41, column 19: ORA-00904: "STOCK_BALANCE": invalid identifier

select 
         PARTY_ID,
         PARTY_NAME,
         IT_ID,
         IT_ID || ' - ' || IT_DESC || ' - ' || IT_BRAND,
    (select 

     SUM(BALANCE)

    FROM (

    select 
     WEAVING_YARN_TR.TR_ID,
     WEAVING_YARN_TR.GP_NO,
     WEAVING_YARN_TR.YR_ID,
     WEAVING_YARN_GATEPASS.TO_FROM as PARTY,
     WEAVING_YARN_GATEPASS.GP_DATE, 
     WEAVING_YARN_TR.BAGS_IN,
     WEAVING_YARN_TR.BAGS_OUT,
     NVL(WEAVING_YARN_TR.BAGS_OUT,0)-NVL(WEAVING_YARN_TR.BAGS_IN,0) as BALANCE
    from WEAVING_YARN_TR,WEAVING_YARN_GATEPASS
    where WEAVING_YARN_TR.GP_NO=WEAVING_YARN_GATEPASS."GP_NO." and WEAVING_YARN_TR.YR_ID=WEAVING_YARN_ITEMS.IT_ID and WEAVING_YARN_GATEPASS.TO_FROM= WEAVING_PARTIES.PARTY_ID

    UNION ALL

    select 
     null as TR_ID,
     WEAVING_WARP_SET.SET_ID,
     WEAVING_WARP_SET.YARN_ITEM,
     WEAVING_WARP_SET.PARTY,
     WEAVING_WARP_SET.RECEIVED_DATE,
     WEAVING_WARP_SET.TOTAL_BAGS_CONSUMED,
     null as BAGS_OUT,
     NVL(null,0)-NVL(WEAVING_WARP_SET.TOTAL_BAGS_CONSUMED,0) as BALANCE
    from WEAVING_WARP_SET
    where WEAVING_WARP_SET.YARN_ITEM=WEAVING_YARN_ITEMS.IT_ID and WEAVING_WARP_SET.PARTY= WEAVING_PARTIES.PARTY_ID
    ) t

    ) STOCK_BALANCE

    FROM WEAVING_PARTIES
    CROSS JOIN WEAVING_YARN_ITEMS

    WHERE STOCK_BALANCE is not null;

Best Answer

You can't reference a column alias on the same level where you introduced it. You need to wrap your query into a derived table. Note that the columns in the SELECT list of your sub-query are pretty useless as you are only using the balance column in the outer query:

select *
from (
  select PARTY_ID,
         PARTY_NAME,
         IT_ID,
         IT_ID || ' - ' || IT_DESC || ' - ' || IT_BRAND,
         (select SUM(BALANCE)
          FROM (
            select NVL(WEAVING_YARN_TR.BAGS_OUT,0)-NVL(WEAVING_YARN_TR.BAGS_IN,0) as BALANCE
            from WEAVING_YARN_TR,
                   WEAVING_YARN_GATEPASS
            where WEAVING_YARN_TR.GP_NO = WEAVING_YARN_GATEPASS."GP_NO." 
              and WEAVING_YARN_TR.YR_ID = WEAVING_YARN_ITEMS.IT_ID 
              and WEAVING_YARN_GATEPASS.TO_FROM = WEAVING_PARTIES.PARTY_ID
            UNION ALL
            select NVL(null,0)-NVL(WEAVING_WARP_SET.TOTAL_BAGS_CONSUMED,0) as BALANCE
            from WEAVING_WARP_SET
            where WEAVING_WARP_SET.YARN_ITEM = WEAVING_YARN_ITEMS.IT_ID 
              and WEAVING_WARP_SET.PARTY = WEAVING_PARTIES.PARTY_ID
           ) t
          ) AS STOCK_BALANCE
  FROM WEAVING_PARTIES
) x  
  CROSS JOIN WEAVING_YARN_ITEMS
WHERE x.STOCK_BALANCE is not null;
Related Question