I want to save the data I have after With Clause. Maybe saving into TEMP table or some other data set. WITH TASKLIST AS (SELECT * FROM IC_V_NEWSKUSTASKLIST WHERE MSTID IS NULL), RESULTS AS (SELECT *…
Tag: oracle
Average price based on dates
I am trying to calculate a weekly average, and add it as a new column along side what I already have. I have added week starting dates and week ending dates as I thought that would be useful (and how I found do it in a more familiar MS Excel environment). This code returns a table with 4 columns. I
How do I assign methods to a specific parameter using PIVOT in SQL?
We’re working on producing our data as a relational database/set and as a row-column dataset (so that the users of the data can use which format suits them best). Here’s a simplified table of our data:…
Issue in a function that converts a string into a valid date
I have been using this function for last few years that converts an input string into a valid date in decided format. The above query will return, 28-02-2002 00:58:15 However starting 29/02/2020, if the input string to the function changes to ‘20200229005947354241’, it returns a null! I am not sur…
Improve query performance and maintainability
I have a query like this: The output of the query will be something like this, basically a CODE and the respective COUNT, for example: After this first query, I have a foreach that will iterate the result of the query above. Inside the foreach, for each result of the query above, I want to get some informatio…
max, over partition by, oracle, take max rownum record
I am trying to pull the maximum rownum in a partition. I am getting the below error message, so I need help to fix my SQL Query. I added in a row number and a row number in a partition in my SQL query. Code is below. I want to take the maximum over this partition and have tried changing
How to merge two result columns into column with alternating results in oracle?
For a table with columns A and B, select * from table will give back A B A1 B1 A2 B2 A3 B3 Is there a way to query on this so that the result set is like this – Merged A1 B1 A2 B2 A3 B3 Appreciate any suggestions 🙂 Answer A union with some added spice to determine ordering would wo…
How to make pivot query that returns no rows return null instead. (Oracle SQL)
I have a query that is structured like this: For the case of it returning no values, I want it to return null-values instead of a empty row. How would I achieve this? I was not able to make commonly suggested solutions work, because of the pivot. E: The result I get: No row returned: The result I want: One
SQL and Oracle query to extract every thing before last two periods
I need to extract every thing before last two periods eg. Input: AA.BBB.12.11.cc Output: AA.BBB.12 Following is the sample query I am using but that returns only the characters before first period, but that is not I needed. Answer I would use REGEXP_REPLACE here: The regex pattern .[^.]+.[^.]+$ will match sta…
How to put Case in Where Statement for Oracle SQL
For the query below, I’m trying to pull a specific date range depending on the current day of the month. If it’s the 20th or less (e.g. “2/7/2020”) then I want the date range for January. Otherwise, I …