select subquery condition in where statement

Question

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;

• Abecee: Are you sure, you supplied the error message for the statement as provided here? Whilst @Vérace is correct for Oracle's handling of table aliases, the error message seems to point to the column name in the WHERE clause. (The statement as given, however, does not even include the WHERE due to the preceding semicolon. But the position line 43, column 16 doesn't match either one.)

Answer 1

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;

• John K. N.: After reviewing the statement again I saw my error. Thanks for pointing that out. Always glad to learn something.