0

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;
1
  • 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.) – Abecee Nov 27 '19 at 23:25
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;
1
  • After reviewing the statement again I saw my error. Thanks for pointing that out. Always glad to learn something. – John K. N. Nov 28 '19 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.