I'm Trying to use Listagg function in oracle 11g for concatenating values from different rows,but i'm getting error as FROM KEYWORD NOT FOUND.
Query is:
select listagg(column_name,'') within group (order by column_name) "column_name"
from table_name;
using listagg function for more than 8000 char.I did the below sample SQL and in "e_orig" and "d_orig" for upto 4000 char it is working fine but I have to use it for more than 8000 char. and it is giving error,I checked the listagg function is having limitation of 4000 char.I tried but I am unable to achieve this.
select d.dname,d.loc,e.hiredate
,listagg(e.ename,',' ) within group (order by e.deptno) over (partition by e.deptno) as e_orig
,listagg(e.ename, ',') within group (order by e.sal) over (partition by e.deptno) as d_orig
from emp e, dept d
where e.deptno=d.deptno;
[code]....
CREATE TABLE TEST_TAB
(
A NUMBER(5),
B VARCHAR2(20)
) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Manoj' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Arun' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Varun' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Suresh' ) ;
[code].....
Query Output :
1Arun,Arun,Manoj,Manoj,Manoj,Suresh,Varun
2Kamlesh,Manoj,Manoj,Manoj,Suresh,Suresh
Expected Output :
1Arun,Manoj,Suresh,Varun
2Kamlesh,Manoj,Suresh
Expectation here is duplicate values should not be repeated.
I have two tables
Tab1 columns c1,c2,c3 data as
t1r1c1,t1r1c2,t1r1c3
t1r2c1,t1r2c2,t1r2c3
t1r3c1,t1r3c2,t1r3c3
Tab2 columns c1,c2,c4 data as
t1r1c1,t1r1c2,t2r1c3
i want to get results like below
t1r1c1,t1r1c2,t1r1c3_t1r2c3_t1r3c3
join columns will be c1 and c2 columns.
column1 column2 column3 column4
12 Mar-21-2005 BDW blah blah blah
11 Feb-07-2001 ZV ha ha ha
12 Jan-02-2002 YM zuck zuck zuckI want a view that has that data like this:
column1 column2
12 Mar-21-2005 - BDW - blah blah blah; Jan-02-2002 - YM - zuck zuck zuck
11 Feb-07-2001 ZV ha ha haCan you help with SQL ?
I tried to use this Oracle LISTAGG function in the SQL, but got a "string concatenation limit exceeded"
My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.
SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......
What will be the case when I need to concatenate for more number of values.
like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
View 4 Replies View RelatedI've generally operated under the assumption that if I have a query that executes properly, I should be able to create a view from that same logic. However, I've had more than one occasion where this seems to NOT be the case.
Today, I have a query using ListAgg that operates just fine when run interactively, but (in SQL Developer v 3.0.04) it gives ORA-24344 when I try to create a view using the exact same statement.
whether a function can return two values?
View 4 Replies View RelatedCREATE TABLE emp
(
code NUMBER(4),
name VARCHAR2(15),
salary NUMBER(5),
ovt NUMBER(4),
sal_mon DATE,
dept number (3)
)
[code]....
9 rows selected.
Required Output is
CODE NAME SALARY OVT SAL_MON DEPTMerge Codes
---------- --------------- ---------- ---------- --------- ---------- -----------
1068 ALI 18500 2385 31-OCT-11 1021068,1793
1908 ASGHAR 17000 1900 31-OCT-11 1021908,1802
1960 ZAHID 16000 775 31-OCT-11 1021960,1952
1188 RAZA 9500 2910 31-OCT-11 1111188
1275 RAHIL 15300 3480 31-OCT-11 1111275,1176
Merge 02 rows of same department having sum(salary) <=20000. select those rows who have lower salary.
I'd like to get all rows for one particular id. I have data stored in two different tables. I'd like the values to be "denormalized" such that for each unique date I want the values(washer_val, widget_val) for each id from each table to be displayed(or null if no data for that date).
I'm not stating the exact requirements correctly
EVENT_DATEWASHER_VALWIDGET_VAL
9/12/20131, 2 1, 2
9/13/20132
9/14/2013 1
I've created two different example views("tables") of my data with a "with" statement(washers, widgets). I tried to denormalize each set of data (washers_val, widgets_val) and then get all the unique dates (unique_dates). I then join the "views" and display all possible rows with their data. This is the type of output I want BUT is there an easier way to achieve it??
with washers as
(select 1 id, trunc(sysdate) event_date, 1 val
from dual
[Code]....
I want to get the values and put them into html template since i want to configure mime settings. My table's name is rawticket_voip and it has 150.000rows and 20 columns so instead of COL1, COL2 what should i write there? How can i get the spesific row's and column's value and how can integrate loops in this html?
'<html>
<table border="1">
<tr bgcolor="Light Blue">
<td>COL1</td>
<td>COL2</td>
[Code]...
I need to create a function where in data from 5 rows is clubbed into one row. Like this I have around 425 rows which should be clubbed to 85 rows. Requirement is similar to pivot but not exactly like a pivot as different columns need to be taken from those 5 rows. This is for reporting purpose in order to get data in the desired report format.
SQL mentioned below works fine. It does return data.When below code is used as a normal procedure with OUT parameter as Index by table of Record type code works fine. It returns data. Functionality is met. But when used as a pipeline function, it returns no data.
Below code gets compiled but returns nothing. I didn't find anything on Google or any website for same.
CREATE TYPE r_report_mth_rec_obj AS OBJECT (
acct_num VARCHAR2 (20),
acct_name VARCHAR2 (80),
fund_group VARCHAR2 (80),
fund_type VARCHAR2 (80),
share_class_code VARCHAR2 (10),
share_class_description VARCHAR2 (20),
curr_code VARCHAR2 (10),
[code]...
can i have a pl/sql function that can return multiple rows
may be the syntax will be like
create or replace function multiple() returns ...
begin
select candidateid from tbl_candidateinfo;
..code to return the result of above statement to calling program..
end;
and functions will be called as
select candidateid from .. where candidateid in( select multiple());
I need a function that should return output of this query
SELECT b.branding_code, c.name_desc
FROM
development.brandings b, godot.company c
WHERE b.company_id = c.company_id;
This above function return 30 rows and I am not giving any input
Function using cursor,pipeline
I want multiple values from a function. I want to use this function in a SQL query. Here i'm giving my try.
SQL> CREATE TABLE TEMP
2 (
3 ID NUMBER(1),
4 SAMPTYPE VARCHAR2(20 BYTE),
5 SALARY NUMBER(10)
6 )
7 /
Table created.
SQL> INSERT INTO TEMP VALUES(1,'ABC',10000);
1 row created.
SQL> INSERT INTO TEMP VALUES(2,'PQR',20000);
1 row created.
SQL> INSERT INTO TEMP VALUES(3,'JPD',5000);
1 row created.
SQL> COMMIT;
Commit complete.
[code]...
Here i get result as ABC*10000, but i want two separate values as ABC,10000. how can i do this via function.
There is a table with column holding 3 NULL values one each in every record. When a count function is applied to the column with a filter on NULLs,it returns 0 instead of 3.
But when count(*)is applied,expected result is returned. Would be interested in knowing about this strange behavior of NULL/Count.
I have input like below
ID | Name
--------------
1 | ABC, BCA, AAA, BBB (all in one column)
2 | ABC,DBA
and I want to get concatenated values separated
ID | Name
--------------
1 | ABC
1 | BCA
1 | AAA
1 | BBB
2 | ABC
2 | DBA
Consider the following (example)table.,
TABLE_A
------------------------------
ID DEPT CRS
------------------------------
1 CS CS_100
2 SCIENCE SCI_150
3 MATH MATH_400
4 HISTORY HIS_110
[Code]...
To display CRS from TABLE_A where DEPT = 'MATH' but in the following format.,
--------------------------------------------
NO DEPT CRS
--------------------------------------------
1 MATH MATH_400, MATH_550, MATH_230
--------------------------------------------
instead of.,
--------------------------
NO DEPT CRS
---------------------------
1 MATH MATH_400
2 MATH MATH_550
3 MATH MATH_230
---------------------------
I would like get rows into comma separated values expected output
rowvalue1,<space>rowvalue2,<space>rowvalue3,<space>rowvalue4,.....Example:
create table test1 (name1 varchar2(10));
insert into test1 values ('JOHN');
insert into test1 values ('YING');
insert into test1 values ('KAREN');
insert into test1 values ('PEDRO');
commit;
SQL> select * from test1;
NAME1
----------
JOHN
YING
KAREN
PEDROHow can I get this to printed as
JOHN, YING, KAREN, PEDRO
I have two tables which have identical schemas, one table (tbl_store) is used to hold the latest version, and the other table (tbl_store_audit) holds previous versions. When the latest record is updated, it is inserted into the tbl_store_audit table as a revision, and the updated details are used as the latest record.
For example: The latest version is held in tbl_store, however the tbl_store_audit may hold 5 records which are the past records used before changes were made - these are seen as revisions.
I want to be able to compare what has changed between each revision in the tbl_store_audit table. For example: Out of the 10 columns, the change between revision 1 and revision 2 was the size from XL to XXL. The change between revision 3 and revision 4 was the size XS to M and price 4.99 to 10.99, and so on.
Eventually i will create an APEX report that will show the user the revision number and what was changed from and to.
I seen in a previous post i need to note my oracle version: Oracle version 10.2.0.4.0
This is my table design:
REQUEST(R_ID,attr1,attr2)WIPS(R_ID,WIP,attr3,attr4)SHIPPING(WIP,attr5,attr6)
How do I limit my query to show the information from REQUEST but only where the all of the wips associated between REQUEST and WIPS are not in the SHIPPING table. For example, the SHIPPING table has all of the WIPS that have been shipped, I only want to show the REQUEST rows where all of the WIPS have not shipped.
I've used PIPELINED FUNCTION and I've no issues in using this. Just wanted to know is there a way so that I don't need to pipe each row separately and I can pipe a set of rows at once.
Like we use BULK COLLECT INTO to fetch multiple rows at once instead of fetching one row using SELECT INTO.
Below is the test case:
CREATE TABLE TMP_EMP
(
EMP_ID NUMBER(10,0),
EMP_NAME VARCHAR2(100),
DEPT_ID NUMBER(10,0),
SALARY NUMBER(14,0),
[code]....
I have a function that returns the total sum of an account. From reports I call the function passing the account code. The function sums the values for that specific account code and returns the value. In my function I have the following code :
where account_code = P_CODE.
Eg. The value of :P_CODE is 'CS'.
I now want to pass multiple account codes ('CS','TV',LJ') to the function. How do I change the IN clause in the function to accommodate multiple values.
I have tried using the instr function, but it does not work. eg. AND instr(o.ACCOUNT_CODES,','||P_CODE||',') > 0
I am an Oracle beginner and I am having some trouble with the following insert query.
I am inputting values into text boxes and then this is carried out as a trigger upon clicking a button.
INSERT INTO client VALUES(':student.txtclientid', ':student.txtclientname', ':student.clientaddress', 13564338);
INSERT INTO enrolment VALUES(':student.txtclientid', ':student.lstoccurrence', null, null);
The above text boxes are all working fine as I have viewed the values using the message command. My proplem is that if i leave the fields blank it inserts ':student.txtclientname' into the row, otherwise it returns "Could not insert record"
difference in the values that are returned?
select count(*) from aaa;
COUNT(*)
----------
1000001
select num_rows from dba_tables where table_name = 'AAA';
NUM_ROWS
----------
994202
How to hold the multiple rows values using array? And I have to pass this values to some other procedure.
Ex: SQL> select ename from emp;
ENAME
----------------------
Vetrivel
Dr.Venkat
Vinoth
Sudhakar
Sivaganesh
Senthil
I am trying to come up with a sql select statement that provides all rows for employees with 2 or more cities.
with sample_table as (
select 'John' name,'city' ValueType,'Toronto' Value from dual union all
select 'John' name,'city' ValueType,'Vancouver' Value from dual union all
select 'Susan' name,'city' ValueType,'Toronto' Value from dual union all
select 'Susan' name,'city' ValueType,'Seattle' Value from dual union all
select 'Susan' name,'age' ValueType,30 Value from dual union all
select 'Susan' name,'city' ValueType,'Atlanta' Value from dual union all
[Code]...
NAME VALUETYPE VALUE
----------- ------------- ------------
John City Toronto
John City Vancouver
Susan City Toronto
Susan City Seattle
Susan Age 30
Susan City Atlanta
David City Chicago
David age 35
David Status married
David City Dallas
The above code is just to describe the sample table and the desired result set. Please note that Mary is not on the result set since she has no city assigned to her. Also Julia is not on the result set since she only has one city assigned to her. The others are there because they had at least 2 cities assigned to them.
I need the sql syntax that would return this result set.
I have a large table with a few billion rows. I need to find ITEM_NAME values in ITEM_MASTER table containing " " (spaces) and replace them with other values. This has to be done within the same script. The replacement values could be any characters or numbers, but not spaces.
The whole reason for this table is testing big databases, so it does not matter what it contains. The reason I need to replace them is that this table will be dumped to a space delimited flat file and spaces within values are not acceptable.
I have written code as below. I want to insert all rows values fetched by cursor INTO table emp2 (blank as column as employees) . How Can I insert it.
what parameter or code I should used ?
I have marked with color row as below.
DECLARE
CURSOR C1 IS select * from employees;
C2 C1%ROWTYPE;
[Code]....