I have an oracle table which has id and order_id columns. Table have same order_id with different id’s. How can I write a select for group same order_ids, and show in one line which seperated with comma; Select result should be like that; Answer LISTAGG
Tag: oracle
Select configurable column names in Oracle SQL
I have a table with configurable column names which I can query like this: This is how ‘my_config’ tables looks like. My other table would look like this: However, I would be looking to select only those columns that the query above returns in my other table, kind of like the below: How could I ac…
How to delete more than one node with xml-query
with this code, I can remove one node (field_without_interest) of this xml element. But I would like to remove two nodes now( field_without_interest and PERSON_NUMBER). I have tried to place the for loop at differents places (You can see that in code. But I don’t think it useful to for to write every at…
Oracle SQL find columns with different values
I have two tables A and B both with some millions rows and around one hundred columns. I want to find which columns have different observations without the need of listing the names of all the columns. For example, suppose column ID is the primary key in both tables. And that table A is while table B is The r…
How to give access to columns to new user when current user does not see the tables?
Using SYSTEM user I create a new user. After that I try to give this user access to specific columns only in 1 table, but get the error that this table does not exist. Meaning that SYSTEM user does not have access to this table. Unfortunately, the user that has access to the normal production tables cannot ma…
Which metrics to compare when evaluating SQL query performance?
I recently watched an online course about oracle SQL performance tuning. In the video, the lecturer constantly compares the COST value from the Autotrace when comparing the performance of two queries. But I’ve also read from other forums and websites where it states that COST is a relative value specifi…
Is it possible to change the value of a column if it has more than one result (using GROUP BY)?
Is it possible to change the result only if there’s more than one? My query brings this result: Date Code Description Amount 01/01 1001 Produt A 1234 02/01 1001 Produt A 2345 03/01 1001 Produt A 3456 If I have only one produt, that’s fine. But if I have more than one, it shows like this: Date Code…
How to get list of ids into separate column
Problem statement: I have a table master_tab which consist of two columns e_id and owner_id. I want to list down the e_id’s which belong to owner_id 72 and 73. There is another condition that analysis_comp flag should be 1 in the table transaction_tab. My attempt: Tool used: SQL Developer(18c) Current O…
From keyword not found where expected error in oracle
Need to create a temporary table employee by inserting values from already created table person which belongs to people database but getting error from keyword not found where expected Answer You’d then use CTAS (Create Table As Select), not an invalid INTO clause; it is used for different purposes. Bas…
Need to get appropriate/expected row number in Oracle PLSQL
Getting the following output when i use the below query But the expected output should be as below (Refer RowNumber column) Answer First find “groups” each of those rows belongs to (partitioned by refnum and name) (that’s what the temp CTE does), and then apply dense_rank to fetch the final …