Home » SQL & PL/SQL » SQL & PL/SQL » trunc function and inner joins
icon14.gif  trunc function and inner joins [message #675063] Thu, 07 March 2019 05:16 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13781
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 #675067 is a reply to message #675063] Thu, 07 March 2019 10:00 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: trunc function and inner joins [message #675068 is a reply to message #675067] Thu, 07 March 2019 10:09 Go to previous messageGo to next message
cookiemonster
Messages: 13781
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.
Re: trunc function and inner joins [message #675069 is a reply to message #675068] Thu, 07 March 2019 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 66794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also, I didn't read the whole but:
case when (wk.sevendayavg+p.prev_day_avg)-m.visits < 0) "visit_over_thresh",
is most likely syntactically invalid (even if you add the missing bracket somewhere).

Re: trunc function and inner joins [message #675242 is a reply to message #675063] Mon, 18 March 2019 06:09 Go to previous messageGo to next message
rajmat483
Messages: 3
Registered: March 2019
Junior Member
Hi Guys,
Thanks for all your replies. Problem is that the requirement got changed and the above query is no more required. We created few materialised views and tables with some additional data and so we are now fetching the records from them.

Thanks a ton again for your replies.

I have a related issue which I have posted under the topic "Duplicate Insertion while executing Procedure".

Thank You!
Re: trunc function and inner joins [message #675243 is a reply to message #675242] Mon, 18 March 2019 06:32 Go to previous message
Michel Cadot
Messages: 66794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In short, you feedback here only because you have another problem you want we help you.

Previous Topic: Performance of Virtual column referencing another table!
Next Topic: TO_NUMBER specify the decimal separator within a query.
Goto Forum:
  


Current Time: Thu Jan 23 23:54:14 CST 2020