I have a table that stores the effective date column in order. In some cases as mentioned below I effective date is NULL so in that case I am supposed to pick date from previous column and add 1 day and show as effective date. It works fine when there is a date in previous row but when 2 or
Tag: oracle
How can I calculate the median of all rows?
What I’m trying to accomplish: I’d like to calculate the median number of rows for the last 49 days for each store and hour of the day (0 – 24 hours). What I’ve done so far: My current SQL is below. I’…
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 user-defined Function’s IF statement to compare, something like this: where in_store_id is the
How to order by with union in SQL to make first select query value first?
In this query: First select query ( SELECT NM, DEP_CD FROM EMP WHERE DEP_CD=’1100′ and (SELECT COUNT(1) FROM BBS_TABLE WHERE UP_DEP_CD = ‘1100’) > 0) always returns 1 result. I want to put this one first in the result values, but there are no regular rules for results so I couldn’t use order by. How can I put the first
Oracle – update the same count of duplicates between two tables
I have two tables UPD_REF and UPD_TO There is no key and we can have duplicates in both tables UPD_TO or UPD_REF. I need to update the same count of rows on UPD_TO corresponding to UPD_REF. If I do a simple update or merge like this: I have all the matching values updated: But I need to get that: Any
How to insert a Number value which is a difference between field 1 and field 2 of another table in Oracle?
I have a table name ITEM that consist of +——-+———–+————–+ | Item | PrevValue | CurrentValue | +——-+———–+————–+ | ItemA | 2 | 10 | | ItemB | …
How do i create a view table to show current number of record of the same employee and the total count? SQL ORACLE
How do i create a view to show the number of total records by name? CREATE VIEW ViewTest AS SELECT First_Name || ‘ ‘ || last_Name As EMP_NAME, to_char(READ_DATE, ‘YYYY-MON’) As MONTH FROM …
How i want make 3 rows of the same primary key into 1 row by checking and choose its value using case
For example here, from this table key | status 1001 | A 1001 | D 1001 | C the hierarchy will be C>D>A If the the stats contain C as the value, the person status will become C in one row. …
Fetch a set of words(char and non char seperated by space) from a sentence appearing prior to a particular words in Oracle Sql
I want to split a sentence and fetch a subset of words seperated by space , prior to a specific word in Oracle SqL. I tried regexp substring, instring but couldn’t arrive at solution. Source …
How to retrieve the last part (i.e. a number varies in length) of a text column of which’s length varies in Oracle and store this in another column?
I am trying to get the last number from a Oracle SQL column called Description and store it in another column called Thickness. As you see, the length of the description varies as well as the length of the number at the very end. It is sometimes a double such as 1.5, sometimes an integer like 3. I have tried