This discussion is archived
5 Replies Latest reply: Nov 26, 2012 12:26 AM by upen RSS

ORA-00998: must name this

upen Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    M- Thanks for your support
  • 5. Re: ORA-00998: must name this
    upen Newbie
    Currently Being Moderated
    M-Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points