I have sample Query with XML like below: Output: I am trying to fetch the values below within the query or a new query: Is there any way to do that? Any help / direction appreciated. Answer You just have to complete your first XMLTABLE query (h) in this way:
Tag: oracle
Oracle complex query with hierarchy
Following is my Sample data1 Expected Output1 Here column “CP” and “NNP” are hierarchical (i.e.) we will start with query like below, for a particular NP and then get the corresponding MC and DS values using the below logic first look at value A from column CP and then see if column NN…
How to Join two tables on two separate variables with one variable having the same name
This is what I have so far. I’m trying to join these two tables and if I do not use a using() function, then SQL tells me there is an invalid relational operator just simply using an ON. However using a “using” also throws an error that I did not end it properly. Any help would be nice, than…
running sum or cumulative frequency using subquery
Below is the output of cumulative frequency according to datewise, which I want to change into the subquery. The output is also correct but I want to do it by using a subquery. OUTPUT Answer You would use window functions: Not only is this simpler to write, but it should be significantly faster — and no…
One value for a group of columns in sql
I have the columns like this, all columns coming from three different tables. i want the columns like Can you help me with this? Thanks Answer i am just enhancing Tim answer by adding lag() window function DEMO
Getting an element and the next from a table
I have a table with ids, cities and some sequence number, say: I want to get the city that comes after Rome for the same id, in this case, I can order them by doing something like: I get: and, I want to get How do I proceed? Answer Hmmm . . . I might suggest window functions:
How to query data in Oracle SQL that gives the max value of a column and the corresponding columns for that row?
I have a Oracle SQL query that I’m trying to get the MAX value of t4.workers column along with its corresponding columns in that row, but I only want to see one row for each project along with the max workers and the max date of the row with the max workers. Example Data: project date vendor workers 401…
Common Data Type in Oracle for reusability
I have data type with fixed column data type and size. For example, If you see above I have CREATE_USER char(36) in almost all tables. I want to make it replaceable. So that in future I can change the type/size. For example, I call it CREATE_USER myDataType, so that in future I need to replace it only one pla…
sum values based on 7-day cycle in SQL Oracle
I have dates and some value, I would like to sum values within 7-day cycle starting from the first date. this is my input data with 4 groups to see what groups will create the 7-day cycle. It should start with first date and sum all values within 7 days after first date included. then start a new group with
How to show the most recent value for each row [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 1 year ago. Improve this question This is my current query. I used an inline view to only show the top 5 results for the compan…