I would like to change a table that has a number of values per week, to having all values in one row per week. But when I use listagg() I run out of space. I can’t change the maximum string size (ALTER SYSTEM SET max_string_size=extended SCOPE= SPFILE), and I don’t want to truncate the resulting string. However I could just
Tag: listagg
find only the duplicate values in listagg
I am having some duplicate rows in my table. I wanted to find those duplicates. After i have done listagg on the column rp_num my table will have duplicate value like this : SELECT distinct vt….
merging start and end date cycle dates
I have the below table. CUST_ID START_CYCLE END_CYCLE WORKER CUST_SUB_ID CUST_SUB_TYPE 101 1/1/2019 1/31/2019 ABC123 134 HIGH_SUB 101 2/1/2019 4/30/2019 ABC123 136 …
How to use Replace/Substitution function in PL SQL
I’ve a doubt regarding replacing / substituting values in PLSQL. I’ve used listagg to segregate n number of values with each and every value being splitted by comma delimiter. For instance when executing the above query it returns 7digit alpha numeric values. For example ABCD123,EFGH456,IJKL789 After storing the above values in a variable. When trying to replace the comma (,)
Change WM_CONCAT function to LISTAGG
I used WM_CONCAT in my sql query. The database has been updated to version 12c and WM_CONCAT doesn`t work. Query had value: Select r.product_id, count (1) how_many, wm_concat (distinct r.number) …
Why I’m getting ORA-00979 while trying to use LISTAGG and LEFT JOIN?
I’m trying to group my results so, instead of looking like this: id | nome | bairro ——————— 1 . |Test 1 | bairro 1 1 . |Test 1 | bairro 2 2 . |Test 2 | bairro 3 It looks like this: …