I have the following Type Address and PhoneNumber: CREATE TYPE Adress AS Object (Street varchar2(50), PostalC number, Ville varchar2(50)); CREATE TYPE PhoneNumber AS Object (Ind varchar2(3), PhNumber …
Tag: plsql
OBIEE generated query using irrelevant fact tables in its select
In an analysis, we are counting number of all loaned items by their interest rate. It uses one fact table F1 and three dimension tables D1, D2 and D3. F1 is joined to all these three dimension tables. Therefore, as I see it there shouldn’t be any complicated query generated to get the result. However, w…
Oracle: how to get unique combinations of 2 sets
sample data: Data looks like group: 1 set: 1,2,3 I want to get all combinations of this 2 tables but UNIQUE ( 1,2,3 it’s same as 1,3,2), so output would be like this: I tried using connect_by_path, but it get’s me or same combinations (like 1-132) or some combinations are missing. Any ideas? Answe…
Is there a way to loop through selected columns in plsql
I have a table TestTable with columns of col_test1, col_test2, col_test3 … and I want to create a loop that accesses each of these columns individually and find the max value and place it in the variable made in the declare block and simply dbms.out.put it. The output I get is just the string ‘col…
Replacing a subquery in a PL/SQL trigger
I have 2 tables. One called Employee and the other Employee_jobs. One of the fields in the Employee table is job_name. The Employee_jobs table contains 2 fields, job_name, and Emp_count (number of employees per job). What i’m trying to do is whenever i perform an operation on the Employee table then it …
joining same table multiple times in Oracle
Hi I am tuning one legacy code. we have below 2 tables in a big query. I have seen in from clause first table has been used thrice and it has been outer joined with same column with same table. Can above 3 lines be handled using fnd_currencies table only once. is there any smarter way to do that? Answer
Generating dynamic data using join select query in Oracle
We have a select query containing around 10 tables. using that query we are getting just 50 record. We need to get 50k record. actually we dont have functional understanding to generate data into these tables. Is there any possibility, we can create insert query to insert data into these 10 table using that s…
PL-SQL query to calculate customers per period from start and stop dates
I have a PL-SQL table with a structure as shown in the example below: I have customers (customer_number) with insurance cover start and stop dates (cover_start_date and cover_stop_date). I also have dates of accidents for those customers (accident_date). These customers may have more than one row in the table…
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 …
Is there a algorithm to resolve credit rating?
I need to implement a logic in either Java OR SQL for the following. There are credit ratings from numbers 1 to 11. And corresponding to each rating say x, I have 3 grades, effectively :x+, x and x-. Difference between each grade is termed as a step. For example, 2 and 2- is single(1) step,2+ and 2- is double…