Skip to content
Advertisement

Tag: oracle

Group By function

hope you all having a great day. I need some help with this query. Just to explain the situation, I have to send e-mails containing the data found in this select, but I have to group by customers (TBLOTE.CEMPRES) so it would build a single e-mail for each customer found. Example, if the query finds 3+ row for customer ´John

Padding zeros to the end of a string and incrementing them

I have a string that is up to 10 characters long (EX: 2021-00445) when it’s first created in SQL. I would like to pad it with a trailing dash(-) and three zeros (-000). I also want to have them incremented in the interval of 1, so if it has been incremented once, then the new value of the trailing zeros

Display the previous date for a user in an additional column

I have a list of users and a list of review dates corresponding to each user, the user can have multiple reviews relating to them. What I need to do is create an additional column that shows me the users previous review date, if they don’t have a previous review I need it to be null. An example of the

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,

Selecting Rows That Have One Value but Not Another

I need a get some rows of two tables with join that shoud have one value in a column (1407) but shouldn’t have other value (1403) These is the tables and the query: I need: Because MAM have both codes (1047 and 1043). Answer To rephrase, I think you mean “I want to return matching rows that have error code

Oracle SQL Count function

I am hoping someone can advise on the below please? I have some code (below), it is pulling the data I need with no issues. I have been trying (in vain) to add a COUNT function in here somewhere. The output I am looking for would be a count of how many orders are assigned to each agent. I tried

Insert Procedure showing compilation error (Oracle)

I am using Oracle 12c and a certain requirement needs me to prepare an insert procedure. I am not very familiar with procedures so would you please tell me what’s wrong? Table statement: Procedure: I am getting ora-00900 invalid sql statement and ora-24344 success with compilation error Answer I’d suggest you to set parameters’ names differently than column names; that

Oracle SQL – implement logic based on CASE expression result

I have following query which returns me of percentage of rows with at least one NULL in any of columns: Query works fine to me and I am getting valid result. But I need to do further action in tablec, based on percentage calculated in my SELECT statement. If percentage of rows with NULL is below 30% I need to

Nested SUM with OVER clause

I am trying to understand how nested SUMs with OVER clause work in SQL. Let’s say i have the following schema in my DB and suppose that i want to calculate the (number of deliveries of each region)/(number of deliveries of the state) on the same year. The query to do the calculation is: I can’t understand how the PARTITION

Advertisement