Is it possible by sql query to produce a report group by a name with count by status. Tables: Project (id, name, status_id, service_id) Status (id, name) Service (id, name) I need to make a report, count by status group by service: Columns results: Service name, status name 1 , status name 2, status name 3, t…
Tag: oracle
finding if a line starts with a specified string in a clob and then extract
I have a CLOB column that I want to search for a line that starts with ‘305’ then extract something from that line, some of my rows will have multiple lines that start with ‘305’ or ‘305 somewhere in the entire cell, so I’d only want to find the first line where it starts w…
Using Over Partition in sql compared with group by
Given the table creation code bellow, is there an alternative method(s) to display the same result to using group by and count(*)? And what’s the difference in this case? Answer This query puts the total for each colour on each row: Before window functions, a typical solution would be a correlated subqu…
Oracle error ORA-02270: no matching unique or primary key for this column-list
I got an error while creating this table. Here are the course and slide tables: What should I do ? Answer The foreign key should be in one definition, like this:
What is the most appropriate logic to add rows in table
I have following table structure (simplified to make essential things clear), which lists Top 3 bank customers in each category of loan and branch of the bank. SNO column is rank of the customer, value of which is up to 3. Loan Category SNO Branch Customer Name Amount Home Loan 1 abc Piyush 10000 Home Loan 2 …
Oracle SQL revenue YTD computation
I want to write an oracle SQL query to compute monthly YTD revenue (cumulative sum) for all possible combinations of the given dimensions. There are also some months where there are no transactions and hence no revenue, in this case the previous month YTD revenue must be displayed for that dimension combinati…
Display the total amount of each products below the table
I’m using itextpdf to display the sql query I was able separate the table according to PROD_NAME, and calculate the total amount of each products by getting the sum(amt) over(partition by prod_name) as total_amt from the sql query. Now, I’ve been trying to display the total amount of each products…
Trying to convert oracle sql to Sql Server date for a migration activity
Below is the oracle SQL query currently used in the existing system, I am trying to convert this SQL into a SQL Server query for the new system. I have been trying different format, but I am not successful and also since I am experienced in Oracle than SQL Server. Kindly help me in this regard. Answer Based M…
Using CASE with Table Join
I am using Oracle 11g. I have two tables: I want to display student_name, course, fee and a column with discounted fee where the fee is reduced by 10% if it is either ‘BIT’ or ‘MIT’. I’ve come up with following query but it gives an error: ORA-00923: FROM keyword not found where …
how to select set of records is ID is present in one of them
Here is the table where ORGID/USERID makes unique combination: I need to select all records (organizations and users) wherever USERID 1 is present. So USERID=1 is present in ORGID 1 and 2 so then select all users for these organizations including user 1 itself, i.e. Is it possible to do it with one SQL query …