Answer With what you posted so far, could be something like this (presuming that last line’s IN_WFID represents some kind of a parameter):
Tag: oracle
SQL query base on three relation with restriction
Customer (CustomerID, FirstName, LastName, Email, PostCode) Salesman (SalesmanNo, empName) Account (AccountNo, SalesmanNo, CustomerID, AccountType) How do I achieve this: Get the name of customer purchase from both Sean and Jean, i believe with the below nested query statement I get the customerID who purchas…
Derive results if the result contains all values from another table
I am trying to get all the names of the customer who have purchase all the treatment packages (A101, A102, A103) The following is an illustration of the tables I have: Customer CustID Surname Given …
SQL in Oracle HR Schema
I have made a query in Oracle HR schema to see the following information: The city where the department is located The total number of employees in the department However, the query cannot be …
What are Oracle’s old-syntax join equivalents of these queries?
What are the equivalent joins written in the Oracle’s old join syntax of these queries? SELECT first_name, last_name, department_name, job_title FROM employees e RIGHT JOIN departments d ON(e….
Get yearly average of per id
This code gets the number of ids that that show up for each month with in the year. This is the result i get: I want to get the yearly average of each ID, and the overall count. The results will look like this: Answer You can add another level of aggregation: Notes: you don’t actually need a CTE to
Extracting multiple values from BLOB as XML
I have an XML like this in a BLOB column: As you can see the year can be either in the event level or at country level. There can be multiple events and multiple countries per event. This whole XML is stored in a BLOB column in Oracle. I need to extract the value of the year or better check
Extract XML sqlQuery Issues in R – Querying Clob Column
I have a oracle DB table called CRS.CRS_FILES, with a column called FILE_DATA -inside that CLOB column is a large XML string. Here is the first few lines of it: it is set up with the following Xpath I want to query: I am not sure what I am doing – I know that sqlQuery when passing SQL queries there
Query with offset returns overlapping data sets
Initial attempts at getting a very simple pagination, using fetch n rows and then a subsequent call with offset, gives overlapping entries in Oracle. I was expecting the following to give me two unique sets of results. 1-100 and then 101-200 of the results that would have been returned if the first line had b…
Row for each date from start date to end date
What I’m trying to do is take a record that looks like this: and change it to look like this: it can be done in Python but I am not sure if it is possible with SQL Oracle? I am having difficult time making this work. Any help would be appreciated. Thanks Answer connect by level is useful for these