I need to write a logic that will give me the data after finding the last Tuesday from the end_date column. I need a report with the logic as mentioned below: If today is 12th April i.e SYSDATE and day is Tuesday then the report should give me only today’s date data only. If suppose the date is 13th April
Tag: oracle
SQL query optimisation by replacing subquery
The following query works and gives me the expected result but i’d like to optimise it. Is there a way to avoid the subquery, using only the conditions in the main query to arrive at the same result: PS: In PLSQL, NVL function lets us substitute a value when a null value is encountered Thanks for any input Answer A
Loop custom type object ion procedure
I created the following type: Inside of a procedure then I accept this parameter and want to loop through it: I keep getting this error though: Answer It is possible that you want to create a collection type and then pass that collection to your procedure If that is not what you are looking for, please edit your question to
Is the Function in the GROUP BY Clause Necessary?
I’m an Oracle newbie just trying to learn. Are the following two queries equivalent in terms of their results? Query 1 Query 2: No TO_CHAR() in the GROUP BY Clause I’m using Oracle version 12.1. Answer Both queries won’t return the same result… unless customers.date includes only dates with no time part. The DATE type in Oracle includes the date
Get Current Week/Month Dates
I want to make function get dates of current week. I will pass week start date number dynamically.Week start date can change.It can be sunday or monday or any day.I shoud be able to pass week start date number dynamically. If i Call function GetCurrentWeekDates(weekStartDay NUMBER,callingday Date),i should get the result like the following GetCurrentWeekDates(1,’10/04/2022′) –1 is sunday Result should
How to change my name into columns using sql
What is the best query to split name into columns? ie. if my name is VELOCITY in one column. How can i split it as Answer If you just have a single row then: Which outputs: CH V E L O C I T Y If you have multiple input rows then, from Oracle 12, you can use a LATERAL
Repeating JSON Aggregated Array with Row Limits
I have an Oracle 19 database with a JSON aggregated array which I need to cut off and repeat after a set number of rows, for example: …etc. Here’s some basic SQL to illustrate how my current dataset is put together: I need to push customer data to an API endpoint. Pushing 1 customer record at a time is not
Found the symbol “CREATE” instead of
I don’t know why it gives me this error: Error(3,3): PLS-00103: Found the symbol “CREATE” instead of one of the following: ( begin case declare exit for goto if … I tried using the backslash, but it doesn’t solve the problem, any help? Answer You cannot use DDL statements in PL/SQL. You need to use EXECUTE IMMEDIATE and cannot use
SQL get every hour and minute of day
I would like to create a table with 2 columns – Hour and Minute. Table will store every combination of hour and minute in the day Example: A header Another header 0 1 0 2 0 3 … … 0 59 1 0 1 1 1 2 … … 23 59 I would like to populate the table using Oracle
Set output to 1 if 5 or more days have passed since the last eligible entry
I have the below table: I would like to implement this logic: the oldest line (S1) will have output 1. For the subsequent lines, if 5 or more days have passed from the previous entry with an output = 1, mark that output as 1 and continue evaluating. The expected output would look something like this (4th column included for