The following is the script I need to add to:
select sum(Audit_Count_Fails) Audit_Count_Fails,
sum(Total_Counts) Total_Counts,
Count_Date
from (select Count_Date,
Unit_QTY,
Counted_Qty,
Count_Type,
Total_Count_Fails,
case when Count_Type = 'A' then sum(Total_Count_Fails)
else 0
end Audit_Count_Fails,
Total_Counts
from (select to_date(to_char(ch.cntdte, 'mm/dd/yyyy'), 'mm/dd/yyyy') Count_Date,
ch.cntqty Counted_Qty,
ch.untqty Unit_QTY,
ch.cnttyp Count_Type,
case when ch.cntqty - ch.untqty = 0 then 0
else 1
end Total_Count_Fails,
case when ch.cntqty is not null then 1
else 0
end Total_Counts
from R09CABPRD.cnthst ch
where ch.cntqty is not null)
group by Count_Date,
Counted_Qty,
Unit_QTY,
Count_Type,
Total_Count_Fails,
Total_Counts)
I want to add another field to it (wh_id):
select sum(Audit_Count_Fails) Audit_Count_Fails,
sum(Total_Counts) Total_Counts,
Count_Date,
wh_id
from (select Count_Date,
Unit_QTY,
Counted_Qty,
Count_Type,
Total_Count_Fails,
case when Count_Type = 'A' then sum(Total_Count_Fails)
else 0
end Audit_Count_Fails,
Total_Counts
from (select to_date(to_char(ch.cntdte, 'mm/dd/yyyy'), 'mm/dd/yyyy') Count_Date,
ch.cntqty Counted_Qty,
ch.untqty Unit_QTY,
ch.cnttyp Count_Type,
ch.wh_id Site,
case when ch.cntqty - ch.untqty = 0 then 0
else 1
end Total_Count_Fails,
case when ch.cntqty is not null then 1
else 0
end Total_Counts
from R09CABPRD.cnthst ch
where ch.cntqty is not null)
group by Count_Date,
Counted_Qty,
Unit_QTY,
Count_Type,
Total_Count_Fails,
wh_id,
Total_Counts)
group by Count_Date
But I keep getting the following error when I run it:
Status 511 - Database Error: -904 - ORA-00904: "WH_ID": Invalid identifier
This type of script is a bit above my level at this time so I ma not sure what I am doing wrong. The reason for the add is I have three sites I am creating reports/views for and this is the only way I have a seperating them out.
TIA Lisa
