5 Replies Latest reply: Nov 26, 2012 2:26 AM by upen RSS

    ORA-00998: must name this

    upen
      Please any one help this

      create table wip as
      (select sum(unwt * cmremqty) as totalwt, sum(cm_remamt), 'X' customer from monthlyreport where ItemTypeNm like 'Work In Process%' and itemcd not like 'T%'
      union
      select sum(unwt * cmremqty) as totalwt, sum(cm_remamt), 'Y' customer from monthlyreport where ItemTypeNm like 'Work In Process%' and itemcd like 'T%'
      union
      select sum(unwt * cmremqty) as totalwt, sum(cm_remamt), 'Z' customer from monthlyreport where ItemTypeNm like 'WorkInProcess%' and itemcd like 'ZZ%')

      Regards,
      upendran
        • 1. Re: ORA-00998: must name this
          Nimish Garg
          try this
          create table wip as
          (select sum(unwt * cmremqty) as totalwt, sum(cm_remamt) col_name, 'X' customer from monthlyreport where ItemTypeNm like 'Work In Process%' and itemcd not like 'T%' 
          union
          select sum(unwt * cmremqty) as totalwt, sum(cm_remamt) col_name, 'Y' customer from monthlyreport where ItemTypeNm like 'Work In Process%' and itemcd like 'T%' 
          union
          select sum(unwt * cmremqty) as totalwt, sum(cm_remamt) col_name, 'Z' customer from monthlyreport where ItemTypeNm like 'WorkInProcess%' and itemcd like 'ZZ%') 
          • 2. Re: ORA-00998: must name this
            upen
            M-Thanks for your reply

            now I am getting "ORA-00904: "CUSTOMERNM": invalid identifier"

            create table wip as
            (select sum(unwt * cmremqty) as totalwt, sum(cm_remamt), customernm, 'X' from monthlyreport where ItemTypeNm like 'Work In Process%' and itemcd not like 'T%'
            union
            select sum(unwt * cmremqty) as totalwt, sum(cm_remamt), customernm, 'Y' from monthlyreport where ItemTypeNm like 'Work In Process%' and itemcd like 'T%'
            union
            select sum(unwt * cmremqty) as totalwt, sum(cm_remamt), customernm, 'Z' from monthlyreport where ItemTypeNm like 'WorkInProcess%' and itemcd like 'ZZ%')

            Regards,
            upendran
            • 3. Re: ORA-00998: must name this
              Nimish Garg
              remove comma
              create table wip as
              (select sum(unwt * cmremqty) as totalwt, sum(cm_remamt) customernm, 'X' from monthlyreport where ItemTypeNm like 'Work In Process%' and itemcd not like 'T%' 
              union
              select sum(unwt * cmremqty) as totalwt, sum(cm_remamt) customernm, 'Y' from monthlyreport where ItemTypeNm like 'Work In Process%' and itemcd like 'T%' 
              union
              select sum(unwt * cmremqty) as totalwt, sum(cm_remamt) customernm, 'Z' from monthlyreport where ItemTypeNm like 'WorkInProcess%' and itemcd like 'ZZ%') 
              • 4. Re: ORA-00998: must name this
                upen
                M- Thanks for your support
                • 5. Re: ORA-00998: must name this
                  upen
                  M-Thanks