I have dates and some value, I would like to sum values within 7-day cycle starting from the first date. this is my input data with 4 groups to see what groups will create the 7-day cycle. It should start with first date and sum all values within 7 days after first date included. then start a new group with
Tag: oracle12c
Loop through JSON array in PLSQL 12.1
I have JSON array inside varchar DECLARE JsonArray varchar2(1000); arrayCars varchar2(1000); BEGIN JsonArray :={“Cars”: [{“name”:”Honda”, “color”:&…
Oracle : replace string of options based on data set – is this possible?
I have column in table looking like this: Every option has a number of value. There is a table – let’s call it option_set and records look like I want to replace option name to number in 1st table, if exists of course, if not exists then =0. I have done this in PLSQL (get the pattern, go through e…
OBIEE generated query using irrelevant fact tables in its select
In an analysis, we are counting number of all loaned items by their interest rate. It uses one fact table F1 and three dimension tables D1, D2 and D3. F1 is joined to all these three dimension tables. Therefore, as I see it there shouldn’t be any complicated query generated to get the result. However, w…
How can I select a nested json object with a group by clause in oracle sql from a table?
Let’s say I have the following statement: WITH t AS ( SELECT ‘A’ AS level_0, ‘A1’ AS level_1_1, ‘object_1’ AS level_1_2, ‘A11’ AS level_2_1, ‘B11’ AS level_2_2 FROM dual UNION ALL SELECT ‘A’ AS …
How to tokenize semicolon separated column value to pass to IF statement in a function in Oracle DB
I have a table called ‘config’ and when I query it in following manner: SELECT value FROM config WHERE property = ‘SPECIAL_STORE_ID’ its response will be: 59216;131205;76707;167206 //… (1) I want to tokenize the above values using semicolon as the delimiter and then use them in a…
SQL count products
I have table: name product john beer john milk john tea john beer emily milk emily milk emily tea john beer i need select from this table, when output will be: name …
ORA-00932: inconsistent datatypes: expected – got CLOB – while using clob in regexp_substr function
I am trying to use CLOB variable in regexp_substr function as below clob_variable is of type CLOB and contains ~ separated ids. While executing the update statement I am getting below error: ORA-00932: inconsistent datatypes: expected – got CLOB Can we use CLOB with RegExp? If not, is there any way to c…
Oracle SQL Query for Distributing Quantity
I have one table called INVENTORY which has data: ITEM_NUM SUB_INV LOT_NUM QUANTITY LOT_EXPIRY_DATE 101 22 A1 10 9/29/2019 101 22 A2 14 9/28/2019 Let’s say,…
Combining MAX and SUM in an Oracle ROLLUP
Here’s my SQL: The output looks like this: I was expecting it to look like this: In other words, I was expecting the rollup to sum up the maximum weights of each food instead of taking the maximum of all the maximums in the food-type category. I do have a solution of sorts, but it means having to add an