The employee table (EMP) specifies groups by department code, the sum of salaries by group, the average (constant treatment), and the number of people in each group’s salary, and is presented below, listed in department code order. I would like to modify the following SQL syntax to look up departments w…
Tag: oracle
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…
Convert from sql query condition to REGEXP_LIKE
I am converting an SQL query condition like: I tried to execute query in Oracle using condition belove but no result return: Result look like for example 169011310 so _ are numeric characters Answer The $ in regex means end of string, you need to remove it because % in TSQL means any text, any zero or more ch…
SQL- query processing-index nested loop join
when we are using Index nested loop join, is it index on joining column should be only for the inner table or it can be on the inner or outer table? Answer A nested loop join is looping through the outer table and then looking up a value in the inner table. The primary use of an index would be
HR SCHEMA: the year of most employees hires [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 last year. Improve this question IN the HR Schema in oracle how do I get the year in which the most employees were hired? Answe…
fixed number format with different lengths in Oracle
I need help with a Oracle Query I have a query: scenario 1: select to_char(‘1737388250’,what format???) from dual; scenario 2: select to_char(‘173738825034’,what format??) from dual; scenario 3: select to_char(‘17373882’,what format??) from dual; I need a query to satify al…
How to rewrite query NOT IN with join condition
Below query having high cost, dur to NOT IN (sub-query) I dont have idea how to rewrite the AND clm.column1 NOT IN(sELECT column1 FROM prod) with join condition. If have any idea, please let me know. Answer There are multiple issues in your query. I tried to resolve them as much as possible in following code:
Why can’t we update a view which has DISTINCT Clause in its definition? [closed]
Closed. This question needs to be more focused. It is not currently accepting answers. Want to improve this question? Update the question so it focuses on one problem only by editing this post. Closed 1 year ago. Improve this question I am not getting the logic behind the fact that a view can’t be updat…
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…