Skip to Main Content

Berkeley DB Family

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-00905: missing keyword

User_OTC1HAug 9 2021

I am new to ORACLE and I am struggling with the appropriate condition keywords. Tried different approach but still same error.
Please help here
select (a13.RPRT_CALL_LTRS || (' - ' || a13.SGNL_TRNSMSN_TYP_CD)) RPRT_CALL_LTRS,
a15.DYPT_LBL DYPT_LBL,
CASE WHEN a12.SRVC_MRKT_NM = 'HOUSTON DMA' THEN 'Houston DMA' ELSE a12.SRVC_MRKT_NM END SRVC_MRKT_NM,
a14.RPRT_PRD_STRT_DT RPRT_PRD_STRT_DT,
max(a14.RPRT_PRD_ABRV) RPRT_PRD_ABRV,
a11.DEMO_ID DEMO_ID,
max(a16.DEMO_NM) DEMO_NM,
a12.GEO_TYP_SHRT_DESC GEO_TYP_SHRT_DESC,
a14.RPRT_PRD_STRT_DT RPRT_PRD_STRT_DT0,
a14.RPRT_PRD_END_DT RPRT_PRD_END_DT,
sum((a11.AVG_QHR_CNT * 100.0)) WJXBFS1,
sum(a11.AVG_QHR_RTG) WJXBFS2,
sum(a11.AVG_QHR_SHR) WJXBFS3,
sum((a11.CUME_CNT * 100.0)) WJXBFS4
from SRVC_RPRT_PRD_MRKT_EST_MD_CRDT_FACT_V a11
join MTR_MKT_SMPL_GEO_DEMO_UE_FACT_V a12
on (a11.SRVY_PRD_SRVC_MKT_GEO_KEY = a12.SRVY_PRD_SRVC_MKT_GEO_KEY)
join AUDIO_MD_CRDT_DIM_V a13
on (a11.AUDIO_MD_CRDT_KEY = a13.AUDIO_MD_CRDT_KEY)
join MTR_MTH_SRVC_RPRT_PRD_DIM_V a14
on (a11.SRVC_RPRT_PRD_KEY = a14.SRVC_RPRT_PRD_KEY)
join DYPT_DIM_V a15
on (a11.DYPT_ID = a15.DYPT_ID and
a11.DYPT_KEY = a15.DYPT_KEY)
join DEMO_DIM_V a16
on (a11.DEMO_ID = a16.DEMO_ID)
where (a11.AGGRGT_ESTMT_TYP_DESC in ('SINGLE')
and a12.GEO_TYP_SHRT_DESC in ('METRO')
and a11.DEMO_ID in (42, 598, 39, 35)
and a13.SGNL_TRNSMSN_TYP_CD not in ('AM', 'FM')
and a11.DYPT_ID in (84)
and case when CAST( To_Date('01/01/2000', 'mm/dd/yyyy') AS DATE)=CAST('20000101' AS DATE)
then a14.RPRT_PRD_END_DT = (select max(rpd.RPRT_PRD_END_DT) from MTR_MTH_SRVC_RPRT_PRD_DIM_V rpd
where rpd.SRVC_CD='PPM' and rpd.RPRT_PRD_TYP_SHRT_DESC='MON' and rpd.RPRT_PRD_END_DT <= current_date -9)
else a14.RPRT_PRD_STRT_DT = CAST( To_Date('01/01/2000', 'mm/dd/yyyy') AS DATE) end)
group by (a13.RPRT_CALL_LTRS || (' - ' || a13.SGNL_TRNSMSN_TYP_CD)),
a15.DYPT_LBL,
CASE WHEN a12.SRVC_MRKT_NM = 'HOUSTON DMA' THEN 'Houston DMA' ELSE a12.SRVC_MRKT_NM END,
a14.RPRT_PRD_STRT_DT,
a11.DEMO_ID,
a12.GEO_TYP_SHRT_DESC,
a14.RPRT_PRD_STRT_DT,
a14.RPRT_PRD_END_DT

Comments

MartinBach-Oracle Jan 8 2025

Hi Salomon,

please have a look at this blog post written by @ulrike-schwinn-oracle :

https://blogs.oracle.com/coretec/post/easy-sql-statement-tracking-in23c

I hope this answers your question, if not, please shout!

- Martin

Solomon Yakobson Jan 8 2025

@martinbach-oracle - No, it doesn't answer my question. Article you pointed to shows uses:

SQL> alter system set sql_history_enabled=true scope=both;

And in my post I said “Works fine when enabled on system level”. My question was about

SQL> ALTER SESSION SET SQL_HISTORY_ENABLED = TRUE;

where I showed SQL history was NOT captured even though it should be based on SQL_HISTORY_ENABLED:

Modifiable **ALTER SESSION**, ALTER SYSTEM

SY.

MartinBach-Oracle Jan 8 2025

As per the article I shared the situation is as follows at the moment

  • You must enable SQL history PDB-wide (only a DBA can do that) so there's a certain level of control over the feature
  • Your session has access to the SQL history
  • If you don't want to record anything, set sql_history_enabled to false.

I'm currently assessing if that's intended behaviour (in which case the documentation should be amended) or a feature not working as it should (in which case it needs fixing). The parameter is indeed session-modifyable, but not in the sense you expected.

We'll keep you posted.

- Martin

Solomon Yakobson Jan 8 2025

Do you mean it must be enabled on system level and not on session level and all session can do is disabe it for the session?

SY.

MartinBach-Oracle Jan 14 2025

Yes,

that's correct as of Oracle Database Free 23.6.

- Martin

Solomon Yakobson Jan 14 2025

Thanks Martin, I hope this will be added to 23AI docs soon.

SY.

1 - 6

Post Details

Added on Aug 9 2021
1 comment
425 views