Using Oracle 11gR2 on windows 7 client. I have a question on calculating sum() on multiple columns on different columns and store the results in a view. Unfortunately I could not post the problem here as it keeps on giving error "Sorry, this content is not allowed", without telling where or what it is! So I had to post it in the stack-overflow forum, here is the link: [URL] .........
View 6 Replies
How to merge multiple rows into single row (but multiple columns) efficiently.
For example
IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100
where different attributes mean different thing for each Information_type. For example for Information_Type=Location
Attribute_1 means Country
Attribute_2 means State and so on.
For example for Information_Type=Stats
Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.
I want to create a view that shows like below:
IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100
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"
I want to load single column data into multiple columns.
View 3 Replies View RelatedI have following data
select rowid,object_name,object_type from do;
ROWID OBJECT_NAME OBJECT_TYPE
------------------ ------------------------------ ------------------
AAA/wuAAHAAAW73AAA CON$ TABLE
AAA/wuAAHAAAW73AAB I_COL2 INDEX
AAA/wuAAHAAAW73AAC I_USER# INDEX
AAA/wuAAHAAAW73AAD C_TS# CLUSTER
AAA/wuAAHAAAW73AAE I_OBJ# INDEX
AAA/wuAAHAAAW73AAF I_CON2 INDEX
6 rows selected.
I want it in the following manner
select rowid,object_name,object_type from do;
ROWID OBJECT_NAME OBJECT_TYPEGROUP
------------------ ------------------------------ ---------------------------
AAA/wuAAHAAAW73AAA CON$ TABLE2
AAA/wuAAHAAAW73AAB I_COL2 INDEX1
AAA/wuAAHAAAW73AAC I_USER# INDEX1
AAA/wuAAHAAAW73AAD C_TS# CLUSTER1
AAA/wuAAHAAAW73AAE I_OBJ# INDEX1
AAA/wuAAHAAAW73AAF I_CON2 INDEX1
6 rows selected.
Here the GROUP is changing when the data type is changing and thus for same data type the group shall remain the same As of now this is achieved by - first selecting distinct object_type, then it's mod(rownum,<input variable) and this result of 'mod' is doing the grouping which is the retrieved along with rowid of all individual record
Present query is as following and it is not much efficient
SELECT DO.ROWID RWID, RID
FROM DO,
(
SELECT OT,CASE MOD(ROWNUM,:v) WHEN 0 THEN :v ELSE MOD(ROWNUM,:v) end as RID
FROM(
(SELECT DISTINCT OBJECT_TYPE OT
[code]....
I tried using sequence with cycle but it gave different results. Even I tried following but it did not gave satisfactory results
select d.rowid,d.object_type,x.x1 from do d,(select distinct object_type,mod(rownum,:v) x1 from do where
created>'01-jan-2008')x where d.object_type=x.object_type and created>'01-jan-2008';
In short the query needs Distinct with mod(rownum) and individual records in a single pass The mod(rownum) i.e. group shall change when the object_type changes but then shall remain constant through out the particular object_type.
how does this query execute? what kind of a query is this called?
mysql> select ename,(select dname from dept where deptno=e.deptno ) as dname -> from emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
I have a sql query as below :
select order_number,
(select decode(hcp.contact_point_purpose,'ABC',hcp.email_address,'CDE',hcp.email_address,null)
from hz_contact_points,
hz_parties hz
WHERE hz.party_id=hcp.owner_table_id) Email
FROM oe_order_headers_all h
WHERE h.order_number='102'
....................
..............
Actually the problem i am facing is the inner select query is returning multiple row , so my main query is erroring out, i need to capture the multiple row.
In the above example the inner decode statement returning two mail address, I need to capture that, but while executing the whole query it is erroring out as saying single query returns multiple values. capture multiple values
Actully i am updating two table of data.. but below error message came..
update (select ename, dname
from emp e, dept d
where e.deptno = d.deptno
and empno = 7788)
set ename = 'X', dname = 'Y'
/
Error at line 1
ORA-01776: cannot modify more than one base table through a join view
Table-Name
ID Status description Tracking ID
1 Strat Frog 1
2 Start Dog 2
3 Process Frog 1
4 Completed Dog 2
5 Start Rabbit 3
6 Error Frog 1
7 Stop Rabbit 3
8 Start Elephant 4
9 process Elephant 4
10 Start Human 5
11 Stop Human 5
12 Start Butterfly 6
13 completed Butterfly 6
14 start lion 7
15 error lion 8
16 complted lion 8
17 start tiger 9
18 error tiger 9
select * from Table-Name where datetime < to_date('2012/12/06:06:00:00', 'yyyy/mm/dd:hh24:mi:ss')
And datetime > to_date('2012/12/04:22:00:00', 'yyyy/mm/dd:hh24:mi:ss')And not description in (Select * from Table-Name where Status like ('%Complete%' or Status like '%stop%') and description in (Select description from Table-Name where Status Like '%start%'));
Result should be " Frog and Elephant and tiger"
Start of every record(descrpition --status is Start)
End of every record ( status is stop or done or completed)
status process is in btwn (their will be mulitple records with name s//y to process...ie. process 1 ...process 2...process 3 )
Note:
tracking IDs may change up on error
can we delete multiples table through the single query?
suppose we have 2 table first one is emp and second is client
i want delete all data from emp and client through the single line query
I want to insert multiple record in diff. table by using single query...
how can i di it suppose i hv 3 table table1, table2, table3 i want to insert 2 record on each table respectively..
But i want to do this task by using single query....how can i do it?
i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.
View 5 Replies View RelatedI have to write a sub query / build a logic for the below.
There are several accounts which should have a zero balance i.e sum of all the amoutns in that account should be zero. If they are non zero , i have to report which amounts make up non zero balance.
If i have amts as +20 , -20 , -30,-10 i.e the sum is -40 indicating a non zero amount. I need the entire details of the records which makes up non zero sum. So in above case details related to -30 aand -10.
I'm using a sum group clause on several fields at which sum is required to be checked ie. date , account , currency . query that will bring individual records that don't make the sum zero.
Is it possible to write a outer query which will bring individual records which don't sum up to zero.
I need to subtract multiple numbers to get the difference between each numbers, the amount of numbers to subtract between each others can vary between 2 and 10.
create table table_1 (
col1 varchar2(5),
col2 number(6,0),
col3 number(12,0)
)
insert into table_1 values ('AAA', 34379, 11111);
insert into table_1 values ('AAA', 39032, 11111);
insert into table_1 values ('AAA', 54337, 11111);
insert into table_1 values ('AAA', 78005, 11111);
insert into table_1 values ('AAA', 66793, 22222);
insert into table_1 values ('AAA', 74323, 22222);
insert into table_1 values ('AAA', 81426, 22222);
Expected Output:
col1 col2 col3
AAA -4653 11111
AAA -15305 11111
AAA -23668 11111
AAA -7530 22222
AAA -7103 22222
how to calculate the difference between multiple dates at the same time..
Select to_date('10/10/2011','mm/dd/yyyy')
- to_date('09/10/2011','mm/dd/yyyy')
- to_date('08/10/2011','mm/dd/yyyy') from dual;
Giving me an error...
ORA 00932 : inconsisten data types:expected DATE Julkian got DATE..
I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below
TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan
My desired result is to be able to show the output as follows
Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan
I have a table which contains two columns containing the range of some products. These range are alphanumeric and i want to calculate the count of this range by a query. .
test case:
CREATE TABLE MARRIAGE_FILE_NAME
(
START_SERIAL_NO VARCHAR2(10 BYTE) NOT NULL,
END_SERIAL_NO VARCHAR2(10 BYTE) NOT NULL,
CATCODE VARCHAR2(10 BYTE) NOT NULL,
[code]....
i have table name order table . in that i have column name install date,order date.
the difference bet ween order date and install date should be greate than or equal to 3 working days.
fri and sat are holidays.
i need one query to find the difference betwwen orderdate and install date is greate than 3 working days
I have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
I need to make the following transposition:
from this:
CUST_IDPLAN_IDNOREM_M4M ADD_M4M
6871 1231Yes Yes
68711232Yes NULL
into this:
CUST_ID PLAN_ID REM_M4M1 REM_M4M2 ADD_M4M1 ADD_M4M2
6871 123 Yes Yes Yes NULL
I've below tables.
create table tst_link (parent_pid number(5),
child_pid number(5)
);
insert into tst_link (1, 2);
[Code]....
For each parent pid i need to calculate total miles (including the child_pid also).
Required Output
PARENT_PID TOTAL_MILES
----------- ------------
1 48
4 100
8 125
Is there any limitations for the number of column defined inside the table?
View 3 Replies View Related i have 60 tables i want to create all columns in single table how ? and how to create master table
can u explain briefly what is master table? and why we are using master table in companies..
The challenge I am currently facing is finding the maximum value of two independent columns of a single table. Here is the structure of the table:
[FiscalYear] [AccountingPeriod]
------------ ------------------
[2012] ------------- [2]
[2011] ------------- [12]
[2012] ------------- [1]
What I need to be able to do is to:
1) Determine the maximum value present in column FiscalYear, and then the maximum value available for this FiscalYear under the column Accounting Period.
I can do this fairly easy on Microsoft SQL server, but so far was not able to do this easily on Oracle database. My other observation is that using the MAX function on Oracle is very slow (even with thse fields being indexed). Is it possible to run this query on Oracle with only one pass through the table where the returned result will show 2012 for FiscalYear, and 2 for Accounting Period?
I have a table as col_1 number,col_2 varchar2(10)
col_1 col_2
1 abcdefghijkl
what i want is i've to split
col_1 col_2
1 abcdefghij
1 kl
I have records:
owner company
A X
A Y
A Z
B X
B Y
C X
owner companyX companyY companyZ
A 1 1 1
B 1 1 0
C 1 0 0
How do I write the SQL?
I want to insert multiple records in a single row. Example: I have a below query
select '"'||c1||'","'||c2||'","'||c3||'"'from (select 'ABC' as C1,'ZYX' as C2,'TEST' AS c3from dual unionselect 'A1' as C1,'a2' as C2,'A3' AS c3from dual)
And I want to insert the above 2 records in table T1 as a single row as below:
"A1","a2","A3""ABC","ZYX","TEST" Column1"A1","a2","A3""ABC","ZYX","TEST"
How do I convert multiple rows into single row data. For example.....
I am looking for oracle query for below table output that would have a sql query output as query output below.
I was trying with connect by but was unable to prepare query.
Table output_
---------------------------
ID TYPE VALUE
---------------------------
1012 1 2
1012 6 77
1015 1 1
1015 6 78
1018 1 2
1018 6 79
Query output_
----------------------------
ID VALUE1 VALUE2
----------------------------
1012 2 77
1015 1 78
1018 2 79
I am facing some problem, while fetching the result that I want to. I have a table with name "test", there are two columns:
"id" type int
"text_data" type varchar2(2000)
Sample Data:
ID TEXT_DATA
------- ------------
10 Hi Deepak, My designation id is dsha21. Thanks Rohit
Now I tried to replace the value for "Deepak","dsha21" and "Rohit" using nested replace function and I succeded but that was for static. Now while creating SQL procedure where I am going to make the values of "Deepak","dsha21" and "Rohit" some static variables. I want to pass the values to be replaced with static parameter.
If I give you simple example of my requirement that would be example of a sms send to all customers by a telephone company. Content is same only the Name of customer is replaced everytime.
CREATE TABLE T1 ( id NUMBER,
START_date DATE,
end_date DATE,
end_date1 DATE,
end_date2 DATE,
end_date3 DATE,
LEVEL1 number
)
/
[Code]...
I have data in the first table as mentioned above I need to insert multiple rows into the second table for the same ID depends on the level, If it is level 1 then two rows for same ID first reocrd start_date as the start_date and end_date as end_date from the table t1 for second record start_date is end_date in t1 and end_date for this record is end_date1 column in table t1.
If the level is 3 then the table t2 should have four records for one id and the phase is the value for each record for one ID for example in level 3 we have 4 records for one id and phase should be 1,2,3,4.
