Skip to content

Tag: oracle

Three-table join with NO EXISTS

I have three tables and would like to answer the following question in SQL: “Who has only certifications that do NOT have scores?” For instance, in the setup below, the query would return “John” only. Joana has the “AWS Certification”, which is in SCORE table (id 57). Marry has the “ITIL V3 Certification” which is not in the SCORE table,

How to insert data into table even if trigger fails?

Oracle 11.1 I have custom logging table where I insert data: I have a trigger that runs on a specific table which does some checkings. My problem is: when the trigger fails, I want to be able to log some data into the log_table. Trigger: The following code doesn’t work. I tried to use EXECUTE IMMEDIATE maybe to force it,

Select the Max row number for an account

I need to only pull the max row number for an account. I know it’s a grouping issue. Current data: ACCOUNT_UID ID NAME ACADEMIC_PERIOD CAT_BY_DATE CAT_DATE MAX_ROW abc abc Popeye 202190 CPT 9/15/2021 1 abc abc Popeye 202190 CSH 10/4/2021 2 I only need the second row. Current query: Answer You can try the following: The idea is to apply

How can i grant an execute statment

I want to grant TRIGGERS, FUNCTIONS and PROCEDURES to a role. I have the code to do it with procedure but with triggers and functions it not work. Answer User owns tables, functions and procedures. That user created a role and wanted to grant execute on various objects to the role. for triggers, it doesn’t make sense – they fire

How do I find data from this day exactly one year ago in oracle?

Merry Christmas, I am using the below query to get the prices, but my requirement is to get the data for last one year from trunc(sysdate). i have tried using DATEADD function but its gives me an error cast(p.asof as DATE) = cast(DATEADD(Year, -1, GETDATE()) as DATE) Can anyone please help, what all changes i require to get the desired

choose from a time period oracle sql

I have timestamp date format in column FIRST_DATE and i need to choose time period from a certain hour, for ex. all from 18:00 10.05.21 to 18:00 11.05.2021 the problem is that date column in timestamp format – FIRST_DATE: 10/05/2020 0:00:03,000000 TIMESTAMP(6) so i tried to use it: so with this way i was able to limit the start period