Oracle SQL - Multiple rows into one field
I have a table like this:
I need a query (no PL/SQL) that shows this output:
So for each Product ID I want the distinct values of Delivery Type in the field Delivery Type.
Is there a way to get this result through a "simple" query in Oracle?
I am using Oracle 11g.
Thanks in advance !
listaggafter getting the distinct delivery types per product id. (Note that there is a 4000 character limit for the aggregated string.)
select product_id,listagg(delivery_type,'/') within group (order by delivery_type) from (select distinct product_id,delivery_type from tbl) t group by product_id
See also questions close to this topic
Faster execution time for sql queries
Which of the two queries is less expensive when it comes to copying the table structure without having to copy the data
select * into temp.table_name from table_name_2 where 1=2
select top 0 * into temp.table_name from table_name_2
Which of the two queries is likely to get executed faster (assuming I list out all the column names instead of the asterisk :D ) ?
Data Returned When Both Table Have Data
I am working on a simple query where I've to show data from two tables. The condition is if the alloment table has data and the leave details has no data, then it should show data from the allotment table only. In my case, when both the tables have data, it shows up the data. Tried with this but returns when both the tables have data:
SELECT K.EMPNO, K.LV_NAME, K.ALLOTMENT, K.REMAIN, M.LV_FROM, M.LV_TO FROM LV_ADJ_DETAILS m LEFT JOIN TBL_LV_ALLOTMENT k ON M.EMPNO = K.EMPNO WHERE K.EMPNO = 'EMP00259' AND K.YEAR_NAME = '2018' AND EXTRACT(YEAR FROM M.LV_TO) = '2018';
Expected Output: For the year 2018
If both table have data -
EMPNO LV_NAME ALLOTMENT REMAIN LV_FROM LV_TO EMP00259 MLWP 0 0 4/22/2018 4/30/2018 EMP00259 Maternity Leave 103 103 5/20/2018 5/22/2018 EMP00259 MLWP 0 0 5/24/2018 5/26/2018 EMP00259 Maternity Leave 103 103 5/28/2018 5/30/2018
If one table has data - If no leave details like leave from and to
EMPNO LV_NAME ALLOTMENT REMAIN LV_FROM LV_TO EMP00259 MLWP 0 0 EMP00259 Maternity Leave 103 103 103
CREATE TABLE HRD.TBL_LV_ALLOTMENT ( EMPNO VARCHAR2(10 BYTE), LV_NAME VARCHAR2(30 BYTE), YEAR_NAME INTEGER, ALLOTMENT NUMBER, ENJOY NUMBER, REMAIN NUMBER, STATUS INTEGER ) CREATE TABLE HRD.LV_ADJ_DETAILS ( EMPNO VARCHAR2(20 BYTE), APP_NO VARCHAR2(10 BYTE), LEAVE_NAME VARCHAR2(30 BYTE), APV_DAYS NUMBER(3), LV_FROM DATE, LV_TO DATE, BALANCE NUMBER(3), COM_ID VARCHAR2(15 BYTE) )
Note: I am filtering with year in the
Statement.executeQuery() taking too much time. Is there any way to optimise this? I am using Athena DB
Statement.excecuteQuery() is taking too much time, no matter what the query is.
athenaQuery = "SELECT DISTINCT date " + "FROM sid.lvmh_shareofshelf_new_cat_all_dod where scope='PCD' and date!='' and country='" + countryName+ "' " + "and rname='" + rname + "' and top_category_lvmh='" + top_category_lvmh + "' ORDER BY date DESC"; stmt = conn.createStatement(); long startTime = System.nanoTime(); rs = stmt.executeQuery(athenaQuery); long endTime = System.nanoTime(); System.out.println("Total time taken : "+ (endTime - startTime));
Time taken was 2776930359 nano seconds (2.776930359seconds)
Check Constraint Seems to be Auto Rounding
I am relatively new to Oracle and seem to be doing something dumb. I'm trying to add a constraint that if my value TestLengthHours are more than 6, attempting to insert a new row should result in an error.
I've had some difficulty explaining what this is to Google and was hoping you fine folks could help tell me my silly mistake.
I'm nearly certain that this has something to do with rounding. If I make TestLengthHours 6.5 or higher, the insert fails as it is intended. If TestLengthHours is anything less than 6.5, it does not.
In my example, I alter the table, TESTTABLE, to add the constraint. I make sure that TestLengthHours <= 6.0 and that TestLengthHours is not null.
Then I attempt to insert a value that should fail because it has a TestLengthHours value of 6.1, which is more than 6, and thus should violate the constraint. Unfortunately, I'm doing something wrong and the row is being created successfully.
-- Constraint for TESTTABLE to make sure TestLengthHours is less than or equal to 6 ALTER TABLE TESTTABLE ADD CONSTRAINT valueTuest CHECK (TestLengthHours <= 6.0 AND TestLengthHours IS NOT NULL); -- The following insert should fail, but does not, even though 6.1 is more than or equal to 6.0 INSERT INTO TESTTABLE (ID, TestNumber, TestName, TestDescription, TestLengthHours, TestStatus) VALUES (16, 'SES-2216', 'Cool Name', 'Cool Description', 6.1, 'B'); -- The following /does/ fail. If TestLengthHours is 6.5 or higher, it fails as it should. INSERT INTO TESTTABLE (ID, TestNumber, TestName, TestDescription, TestLengthHours, TestStatus) VALUES (17, 'SES-2218', 'Cool Name', 'Cool Description', 6.5, 'B');
Now, I could just round TestLengthHours to the nearest whole number, but I don't think that would be the the proper way to address this problem.
Another thing that I tried was doing CHECK (TestLengthHours <= 6.0D) like Java does something similar to that, but I was unsuccessful with that attempt as well.
The data type of TestLengthHours is TestLengthHours NUMBER(4)
Oracle SQL: Count one value twice in a single column with different content
Hi everyone I have the next Table
Name | Gender | Count(Gender) BBC | M | 31 BBC | F | 1 BBC | B | 3 BBC | N | 160 M: Male F: Female B: Both N: Not Specified
I need to group this in only three categories. M, F, N.
How can I make a Case Statement that when the register is B the Count for Male and Female increments in 1 for both.
I need a table like this.
Name | Gender | Count(Gender) BBC | M | 34 BBC | F | 4 BBC | N | 160
I hope I explained myself well.
Thanks to everyone.
ORA-04088: error during execution of trigger - additional errors
I have a blank table for which I've set up a trigger:
CREATE OR REPLACE TRIGGER authors_bir BEFORE INSERT ON authors FOR EACH ROW begin if upper(:new.name) = 'TEST' then raise_application_error(-20001, 'Sorry, that value is not allowed.'); end if; end;
insert into AUTHORS VALUES (1, 'test', '1-Jan-1989', 'M');
Why am I getting ORA-06512 and ORA-04088 error messages in addition to the expected ORA-20001 error prompt?
Error starting at line : 5 in command - insert into AUTHORS VALUES (1, 'test', '1-Jan-1989', 'M') Error report - ORA-20001: Sorry, that value is not allowed. ORA-06512: at "RPS.AUTHORS_BIR", line 3 ORA-04088: error during execution of trigger 'RPS.AUTHORS_BIR'
Installing Oracle 11 G : xhost +
I am trying to install oracle 11g on CentOS 6.
When I prompt
xhost +with the root, I get the error:
No protocol specified xhost: unable to open display ":0.0"
The IP adress of my machine is 192.168.159.128. I have tried that solution
export DISPLAY =192.168.159.128:0.0
Please help me.
Delete data from table efficiently
We are using a tool in which we need to delete log tables.Right now,we are keeping 1 million rows and deleting rest of rows if any.This is time consuming and sometimes in production takes 12 hours to delete the data,which affects daily transactions.Is there any other way to delete log tables efficiently,without affecting daily transactions.
Lets suppose we want to keep 1 million rows:
Select query: Select Query:select min(date) from (select date from table order by date desc) where rownum <= 1000000 Delete Query: Delete Query:Delete from table where date > (result of select query)
Is there any way we can optimize these two queries?
Oracle 11g - REGEXP_REPLACE - Subexpressions/different matches
I'm working on a query that returns an object's DN:
My goal is to return this information in a "prettier" output akin to AD:
I've accomplished this in a clunky way:
REGEXP_REPLACE(REGEXP_REPLACE(TEST, '.*CN=(.+?),DC=.*', '\1', 1, 1, 'i'), ',OU=', '\', 1, 0, 'i') -- grab everything between CN= and DC=, replace with \'s -- || '\' || REGEXP_REPLACE(SUBSTR(TEST, REGEXP_INSTR(TEST, ',DC=', 1, 1, 0, 'i')+4),',DC=','.', 1, 0, 'i') -- grab everything after DC=, replace with .'s --
While that works I'm not thrilled with how overly complicated it is (and that it involves having to stitch two regex'd strings together).
I started clean and realized I was doing too much to get what I wanted and my starting point is now here:
REGEXP_REPLACE(test, '(,?(cn=|ou=)(.+?),)', '\3\')
I think I have a good understanding of how this one works but if I add an additional (...) it breaks what I already have working and returns the entire string. I've read that Oracle's regex engine is not as advanced as some others, but I'm struggling to grasp the order of how things are evaluated.
Example Input (can have multiple OUs/DCs):
The data coming in is dynamic and never a set number of OUs or DCs.