I want to select from a set of orders only those that are marked with certain types. Example: Let’s assume I have a table X. In my case I want to select only those orders that contain both A and B: My work so far: However, I don’t like this solution. Is there a better approach? Answer Use an analy…
Tag: oracle
output need to achieve by one query
I have table which consist data like below. I need output in pivot : I can achieve by separate queries easily but is it possible to get it done by one query? Answer You can use conditional aggregation such as in order to return results pivoted.
Provide an SQL in oracle The conflict column should be yes if same id is found in inc and dec list for each name else the conflict should show no [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 Answer You can use the analytical function as follows:
Oracle SQL select, compare 2 listagg’s values
I have two tables: one is TEXT_MSG with column ROLES, another one is USERS with column ROLES. Columns ROLES is listagg VARCHAR2, separator is : symbol. It is possible to write SQL statement with WHERE clause to get records from USERS table WHERE one or many roles from TEXT_MSG.ROLES equals role/s from USERS.R…
Using rownum in sub query on oracle 11
I have query like this.It is working properly on oracle 12. But I am using oracle 11.That query is not working on oracle 11.Therefore I changed my query like that It gives missing right paranthesis How can I solve this problem.Do you have any idea?Actually I use a variable instead on 10 number. Answer Syntax …
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
Can I allow only certain specific strings to be inputted for a table in Oracle 10G?
In this table, I want a constraint such that, name inputted should be only ‘trial’,’experiment’ or ‘test’. Answer You can add the CHECK constraint as follows: If you don’t want the null values in the name then you can also use NOT NULL constraint on the name column. W…
Syntax performance of INNER JOIN
Is the performance of both these examples the same? Example 1: Example 2: I am using example #2 at the moment since I am joining 15+ tables, each table with many unnecessary columns and many rows (1 million+) Answer Oracle is smart enough and does not take all columns from table 1 and join them with all colum…
Select a column from table return by function in Oracle
I have a function that returns a table of custom objects. I wish to select a certain column by name from the returned result. The table of objects The function When I call the function this way, I get a result with a single column called SERIAL_NUMBER However, I can’t do something like this Is there a w…
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…