I am facing an issue with some data that start with a strange character before the number 5 how can I discover all of these characters and remove it what do you recommend to resolve this issue knowing that I get the data from a specific source so I can not change anything but I am trying to mask it
Tag: oracle
Altering Oracle Sequence with result of two subqueries
I am trying to alter a sequence with the result of two subqueries. I am new with Oracle and stuck with a syntax. If you can, please help me with this : Upon Executing this, I am getting an “invalid number” error. What am I missing here? Answer You’ll definitely want PL/SQL and dynamic SQL, b…
ORA-01861: the value does not match the format string
I have this External Table: I created this function to return the week number of the year If I only run the function it tells me that everything has been compiled. But when i run the SELECT it gives me the following error: [I enter the dates like this: ‘2020/01/01’ but sql-developer on the control…
Difference between Global temporary table (GTT) and collections
I wanted to know the difference between GTT and collections with scenarios where to use GTT and where to use collections. Answer Global Temporary Tables are permanent data structures. We can manipulate data in a GTT using SQL like any other table. What distinguishes them from regular heap tables is: Their dat…
Min & Max date before disruption
In Oracle, I have this table sample : +————+————+————+————+ | EMPLOYEENO | DATE_FROM | DATE_TO | REASON | +————+————+————+———…
Sort by status and effective date range
Before After Is there any way to make ‘Before’ to ‘After’? if a function or procedure is required, please write one for me. please help me drop table test; create table test (employee_code varchar2(8),…
PL/SQL No data found even there should be?
I’m currently learning PL/SQL atm and I have run into an issue with one of my homework questions. In the below code, I’m getting user input for a province and isolating select results using said province in the declaration of the cursor and trying to run the visitsandtotal procedure but all I̵…
Delete statement not working on query in oracle
I have a table BK_178_ABC. I want to perform delete statement on this table only when some condition is satisfied: My total count on this table is: select count(*) from BK_178_ABC; ==>22024727 I …
how to divide the amount for each record on specific condition
I have a table let’s assume X and I want to join it to another table Y this the content of X table Y If I made a relationship between X & Y tables the amount it will be duplicated, so if I used sum function the total will be wrong I decided to divide the amount between the stores
Query that will select a timeframe from specific date to today
I’m having issues in my WHERE clause selecting data from a specific day to today’s date. The day/time format in my date column is ‘7/2/2020 3:12:08 PM’. I’ve tested a couple options but keep getting this error – ‘literal does not match format string’. Any idea&#…