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…
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 retu…
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 …
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 …
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 th…
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…
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 …