trunc function and inner joins [message #675063] |
Thu, 07 March 2019 05:16 |
|
rajmat483
Messages: 3 Registered: March 2019
|
Junior Member |
|
|
Hi!
I have recently started working on Oracle SQL. I am trying to execute the below code but getting errors in select statement:
insert into cntrl_tbl
(
select m.contact_date, m.ineractivechannelname, m.interactionpointname, m.cbu, m.segment, m.channel_type, wk.sevendayvisitavg "VISITS_7_DAY_WEIGHT",
p.prev_day_avg "VISITS_PREV_DAY_WEIGHT", wk.sevendayacceptavg "ACCEPTS_7_DAY_WEIGHT", p.prev_day_accepts_avg "ACCEPTS_PREV_WEIGHT",
case when (wk.sevendayavg+p.prev_day_avg)-m.visits < 0) "visit_over_thresh",
case when (wk.sevendayavg+p.prev_day_avg)-m.visits > 0) "visit_under_thresh",
case when (wk.sevendayacceptavg+p.prev_day_accepts_avg)-m.accepts < 0) "accepts_over_thresh",
case when (wk.sevendayacceptavg+p.prev_day_accepts_avg)-m.accepts > 0) "accepts_under_thresh"
from mccm_ops_monitoring m
JOIN
(SELECT
trunc((AVG(visits)*60)/100) as "sevendayvisitavg",
trunc((AVG(accepts)*60)/100) as "sevendayacceptavg",
interactivechannelname,
interactionpointname,
channel_type,
cbu,
segment
FROM
mccm_ops_monitoring
WHERE
contact_date >= trunc(SYSDATE) - 7
AND contact_date < trunc(SYSDATE)
GROUP BY
interactivechannelname,
interactionpointname,
channel_type,
cbu,
segment
where contact_date = trunc(sysdate)
)wk
on m.interactivechannelname = wk.interactivechannelname,
m.interactionpointname = wk.interactionpointname,
m.cbu = wk.cbu,
m.segment = wk.segment,
m.channel_type = wk.channel_type
JOIN
(
SELECT
trunc((AVG(visits)*40)/100) as prev_day_visits_avg,
trunc((AVG(accepts)*40)/100) as prev_day_accepts_avg,
interactivechannelname,
interactionpointname,
channel_type,
cbu,
segment
FROM
mccm_ops_monitoring
WHERE
contact_date >= trunc(sysdate)-1
and contact_date <trunc( sysdate)
GROUP BY
interactivechannelname,
interactionpointname,
channel_type,
cbu,
segment
)p
ON m.interactivechannelname = p.interactivechannelname,
m.interactionpointname = p.interactionpointname,
m.cbu = p.cbu,
m.segment = p.segment,
m.channel_type = p.channel_type
);
ORA-00904: "P"."ACCEPTS": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Please advise on where I am going wrong.
Thanks!
|
|
|
Re: trunc function and inner joins [message #675064 is a reply to message #675063] |
Thu, 07 March 2019 05:30 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That means that the thing with alias of p doesn't have a column called accepts.
Which is true. It's also true that there is no reference to p.accepts in the posted code.
So that code isn't throwing that error.
|
|
|
|
Re: trunc function and inner joins [message #675068 is a reply to message #675067] |
Thu, 07 March 2019 10:09 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Some other points - you've got lots of of mismatched brackets. e.g.
case when (wk.sevendayavg+p.prev_day_avg)-m.visits < 0) "visit_over_thresh",
That's got one open bracket and two close brackets. All the case lines are like that.
This:
on m.interactivechannelname = wk.interactivechannelname,
m.interactionpointname = wk.interactionpointname,
m.cbu = wk.cbu,
m.segment = wk.segment,
m.channel_type = wk.channel_type
is meaningless. You don't separate clauses in a join or where clause with commas, you seperate them with AND or OR as appropriate.
|
|
|
|
|
|