I have a dataset of different transaction ids of type 1,2,3,4,5,6,7 as well as many other columns What I’m trying to do is create different scenarios such as Contains transactions 1 only Contains 1,5 and 7 I’ve started off with a CTE called ALL_CONTRACTS that contains transaction ids of type 1,2,3,4,5,6,7 What’s happening is other contracts that contain a 2,3,4,5,6,7
Tag: oracle
Group By Month between two dates
I have a table that lists all employees and their respective start and end dates I want to be able to count the number of active employees in each month. Is there a way to do this via a single query (eg groupBy) rather than generating multiple queries for each month? As an example, the table above should return: Dec
How to convert varchar2 input parameter to timestamp in stored procedure
We are using a proprietary product to develop our application. This product is capable of connecting to Oracle DB and executes SQL queries and stored procedures. However, today we found a weird issue that this product is not able to execute a stored procedure if there is a timestamp input parameter in stored procedure. We are now trying to change
How to Format phone number like 999-999-9999×1234567890 to 999-999-9999×12345 in oracle sql
If there is x in the given phone number, then after truncating there won’t be more than 5 digits after x. If there is no x in the phone number, then after truncating it will same Here is the possible situations How can I achieve this in oracle SQL using regexp_substr or any other methods Answer You can use (quick)
How to fetch single records using case statements
I want to fetch single record from the table test_table if any one of the value is null in the column e_value then it should print No else Yes My Attempt: Current Output: Expected Output: Answer you can use GROUP BY and COUNT: The count will return number of non-null values db<>fiddle here
translate query from Oracle to Postgres
I need to translate this query from Oracle to Postgres: can someone help me? thanks in advance for your attention and support Answer In postgres the operateur ~ performs a regex comparison as thus replaces the Oracle function regexp_like(). Your query therefore becomes. I would like to alert your attention that the test not upper(location) ~ ‘^[0-9]{1,5}.* [a-z]’ will always
Is there a way to terminate the insert query in a trigger?
I wonder is it able to terminate or stop the Insert if it hits the exceptions. The trigger coding will be: Answer This is how I understood it; though, it is not clear what UPDATE TABLE in your code represents. Update which table? How? With which values? Anyway: sample data: Trigger: Testing:
Optimizing select union select oracle
I had an interview recently and the recruiter told me to make a query on a table USERS with two fields (name, age) which should return a list with two columns | NAME | MAJOR OR MINOR | My response was this : Then, he told me that is correct, but we can do better! So, my question is: How
selected splitted value in oracle
I have two columns A, B in oracle where A value has values like that xx-target-xx xx any data but target is exists i neet to return only ‘target’ from text i tired this but the result returns xx not target Answer Use REGEXP_SUBSTR. You want the second string of any characters except the minus sign: Using INSTR and SUBSTR
ORA-00937: not a single-group group function Workaround
This is my query which produces this error: ORA-00937: not a single-group group function Expected result for the last column – first order of every customer: I know that I have to use GROUP BY clause to handle this error, but it seems rather inappropriate to group by all the other columns.. I know that this is a limitation in