i have a select query with four table its generating around 650 rows. and i need to update these 650 rows only.
for example
update ps_po_lining b
set y.recv_req = 'N'
where recv_req in
[Code]....
this query runs but its updating 6000 rows. which is not right. we need to update what ever the select query is retrieving.
I need to view the rows of the result of a select query in table format in GUI application.I have used XMLELEMENT to somewhat match the scenario to display as ','(comma) separate values as b belwo
SELECT RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
',')
AS empid,
RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),
[code]...
But the case is I need to display the value in table format Horizontally as below
EMPIDemployee nameDEPID
778
278CLARK10
397MILLER
934KING
[code]...
In sql plus How do I get the number of distinct rows of a certain value?
for example
select group_number from records group by group_number
How would I query for the total number of group_numbers in this query?
I need to get the super set(parent) record from a relational table. Following is the structure of the table,
create table relation (data_field1 number(20),data_field2 number(20),data_field3 number(20),
primary key (data_field1,data_field2,data_field3) );
insert into relation (data_field1, data_field2, data_field3) values (-10,123,334);
insert into relation (data_field1, data_field2, data_field3) values (200,123,334);
insert into relation (data_field1, data_field2, data_field3) values (300,123,334);
insert into relation (data_field1, data_field2, data_field3) values (400,123,334);
[code].....
Here '-10' will be considered as parent(super set) for all other values. If i have records like below,
-10 200 300
100 200 300
123 200 300
521 201 300
Output should only contain two rows. A super set row and other distinct row(second column value is not equivalent to super set).
I want to get top two rows based on ACCT_UNIT & order by status_date, if there is only one row on acct_unit, get one row. IF more than two rows available, want to get the top two rows based on status_date.
SELECT ACTIVE_STATUS, ACCT_UNIT, DESCRIPTION, DIRECTOR, DIRECTOR2, STATUS_DATE, OBJ_ID, STATUS_FLAG, SUR_KEY
FROM STSI
i want the 10 most recent mission date from mission table . i have tried using rownum but could not get it. TOP does not work
select * from
(select length(code_name) leng from missions
where length(code_name) >7
order by mission_date)
where rownum = 10
;
this does not work ::: no rows are selected and i do rownum = 1 then gives me one record and when i do rownum>=1 gives me all records
There are two tables like I posted below.I want a SQL query which selects all the rows from TABLE A which are not present in TABLE B. Also the select statement should pick all those rows which has updated value of COL_A2 in TABLE A.
TABLE A
COL_A1COL_A2
AAAMOBILE
BBBTABLET
CCCDESKTOP
DDDUNKNOWN
TABLE B
COL_B1COL_B2
AAAMOBILE
BBBUNKNOWN
CCCDESKTOP
The select statement should return following rows from TABLE A
COL_A1COL_A2
BBBTABLET
DDDUNKNOWN
select the rows from a table with the same particular field in PL/SQL. Actually I don't want to write two loops one inserted into another.
View 7 Replies View RelatedWe were trying to insert approx 76 million records worth of approx 4 GB in a table when the insert operation failed with the archive log error.
The database was hanged, we re-started the database and currently there are no records in the table but when we are firing a select
select count(*)
from table
It's taking approx 2 mins to come out with the result.
We checked and found that there are no locks on the table currently.
what do we need to do to get the performance back
I have a table with name SQL_STATEMENTS and it has 1 column, Column name is STATEMENT_DESC
The contents of rows in Table above are:
select * from emp;
select * from dept;
Can I write a PLSQL block that can select these 2 rows from the table and execute them?
I am facing the following issue while generating xml using a sql query. I get the below given table using a query.
CODE ID MARK
==================================
1 4 2331 809
2 4 1772 802
3 4 2331 845
4 5 2331 804
5 5 2331 800
6 5 2210 801
I need to generate the below given xml using a query
<data>
<CODE>4</CODE>
<IDS>
<ID>2331</ID>
<ID>1772</ID>
</IDS>
<MARKS>
[code].....
NOTe: IDS which are distinct needs to be displayed. ALL MARKS should be displayed though there are duplicates.
Im generating some xml from a table which works fine. Here is an example of what I'm doing.
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
create table "VTEST"(
"PROJECT_NUMBER" VARCHAR2(50 BYTE),
"ORDER_NUMBER" VARCHAR2(50 BYTE),
"PART_NUMBER" VARCHAR2(50 BYTE),
[code]....
so each section would list its unique values.
How to find display the o/p like manager name under dependent employess same like parent child relation ship on noraml emp table:
sample o/p:
name job
xx manger
yy sales
yy1 sales
aa manager
rr marketing
rr1 marketing
There are 4 rows in table with stat_flag 'Updated Record' and stat_date with todays date.
stat date has date & time both, for that reason just trying to format with yyyy.mm.dd
I am getting zero rows as result.
where STAT_FLAG = 'Updated Record' and to_date(stat_date,'yyyy.mm.dd') = to_date(sysdate(),'yyyy.mm.dd')
create table a(sourcerow number(2), test_level number(2), dpn varchar2(1), qty number(5)); T
he insert scripts are as follows:
insert into a values(1,3,'Y',5); insert into a values(2,2,'Y',4); insert into a values(3,3,'N',3); insert into a values(4,4,'Y',3); insert into a values(5,1,'N',6); insert into a values(6,2,'N',5); insert into a values(7,2,'Y',4); insert into a values(8,3,'N',2); insert into a values(9,4,'Y',2); insert into a values(10,1,'Y',3); . SQL>select * from v$version; Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
My logic should have the output as follows without 5th column: sourcerowleveldpnqtyremark13Y522Y4This row in main table shouldn't be displayed in the query result as it has
dpn='N'44Y3*3=9In this row qty=9 will bedisplayed.
It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.this row will not be displayed as dpn='N'this row will not be displayed as dpn='N'72Y4*6=24in its previous row level value is same so it will check the previous to previous row where level is 1(parent of current row) and dpn='N' ,then it will multiply the qty of that row with current row and display the value in qty column.this row will not be displayed as dpn='N'94Y2*2=4In this row qty=4 will be displayed.It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.101Y3It will not check for the previous rows as level 1 doesn't have any parent.
Can we create a table from a Select query ?
View 5 Replies View RelatedI have a table A on dev with definition as TAble A(address,name) and the same table on Prod is defined as Table A(name,address).
my question is Ihave one package in that am trying to insert into this table as follows:
INSERT INTO A
SELECT b.name name,
a.address address,
[Code]....
so the query works on Prod but fails on Dev because column order is different.
I have 2 solutions:
1. I can mention column names in insert line and modify the query but tomorro some body changes again the definition of table A I need to change the query, so do I have solution in oracle sql that can handle the column order without specifying the column names in insert line.
so tomorrow On prod column order and on Dev column order is different though my sql should successfully execute.
How does one select * from one table without selecting * from other tables that are included in a query? For example, if in the query below I want to view all fields in some_table, but not the fields from other_table, how do it?
select *
from some_table st,
other_table ot
where st.id = ot.id
Lets say I have a table in ORACLE database like:
ACC_ID | ACC_AMT
111 | 10000
111 | 12000
111 | 14000
222 | 25000
222 | 30000
333 | 18000
333 | 27000
333 | 13000
333 | 15000
I want to get the output as:
ACC_ID_1 | ACC_AMT_1 | ACC_ID_2 | ACC_AMT_2 | ACC_ID_3 | ACC_AMT_3
111 | 10000 | 222 | 25000 | 333 | 18000
111 | 12000 | 222 | 30000 | 333 | 27000
111 | 14000 | null | null | 333 | 13000
null | null | null | null | 333 | 15000
I need each different ACC_ID with ACC_AMT in different columns. The table may have other different ACC_ID also, but I will fetch only what I need. What is the best way to do this?
So far I have tried this:
SELECT
(CASE WHEN ACC_ID=111 THEN ACC_ID END) AS ACC_ID_1,
(CASE WHEN ACC_ID=111 THEN ACC_AMT END) AS ACC_AMT_1,
(CASE WHEN ACC_ID=222 THEN ACC_ID END) AS ACC_ID_2,
(CASE WHEN ACC_ID=222 THEN ACC_AMT END) AS ACC_AMT_2,
(CASE WHEN ACC_ID=333 THEN ACC_ID END) AS ACC_ID_3,
(CASE WHEN ACC_ID=333 THEN ACC_AMT END) AS ACC_AMT_3
FROM <TABLE_NAME>
But I am not getting the desired result.
I have 12 different tables
Record_jan
Record_feb
.
.
.
Record_nov
Record_dec
Based on the month i need to do some i/o operations from these table.
For eg: If it is january then Record_jan table should be accesed or for nov Record_nov table should be accesed.
If I try to store the table name in some variable and then give the variable name in place of table name then it gives error.
var:=Record_month;
Select * from var;
this gives error.
Here is my case,
create table t (id number,row_id number primary key ,value number);ID row_id value
1 1 10
1 2 11
1 3 1
2 4 11
3 5 11
3 6 12
4 7 12
4 8 12
4 9 13
4 10 11
4 11 10Requirement is
1) To get all the ID that have the value 10
2) The row_ids for the ID I got in I
Can I do this with Hierarchical query. If not which one of below will be fast?
select * from t where id in (select id from t where value=10);
or
select T2.* from t T1, t T2 where T1.ID=t2.id and T1.VALUE=10;
I have billion of rows so I am looking for either Hierarchical query to solve it or some other way . All the three column the index.
We are trying insert records from a select query into temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?
It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.
Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.
I have a table that contains a CLOB column with pseudo-XML in it. I want to keep this data in an XMLType column so that I can leverage some of Oracle's built-in XML features to parse it more easily.
The source table is defined as: CREATE TABLE "TSS_SRM_CBEBRE_LOGS_V" ( "INCIDENT_ID" NUMBER, "EVENT_TYPE" VARCHAR2(100 BYTE) NOT NULL ENABLE, "EVENT_KEY" VARCHAR2(100 BYTE), "CREATION_DATE" TIMESTAMP (6) NOT NULL ENABLE, "CREATED_BY" VARCHAR2(100 BYTE) NOT NULL ENABLE, "LOG_MSG" CLOB);
The target (for testing this problem) table is defined as: CREATE TABLE "TESTME" ( "LOG_MSG" "XMLTYPE") My query is: insert /*+ APPEND */ into testme ("LOG_MSG")select XMLTYPE.createXML("LOG_MSG") as LOG_MSG from "TSS_SRM_CBEBRE_LOGS_V" b; In SQL*Developer, my error is: Error report:SQL Error: No more data to read from socket In SQL*PLUS and Toad, my error is: ORA-03113: end-of-file on communication channelProcess ID: 13903Session ID: 414 Serial number: 32739
I have emp table
Empno from_date to_date ScaleBonus_pct Salary
101 31/01/2010 14/02/2010Grade120,10,10,15,25,205000
Reqd. o/p
Emp nofrom_dateScaleEach_WeeK_Sal
10131/01/2010Grade11000(from 20 %X5000
1017/2/2010Grade1500(from 10 %X5000
10114/02/2010Grade1500(from 10 % X5000
10121/02/2010Grade1750(from 15 % X5000
10128/10/2010Grade11250(from 25 % X5000
1017/2/2010Grade11000(from 20 % X5000
we have to split the date by 7 days,should be on Sunday...and if we split by 7 days we have upto 31/01,07/02 and 14/02...but we have to generate 6 days because 20,10,10,15,25,20 (since comma separated value is 6 ).
generating an xml file using the below data.Table name is T_Data having 4 columns as given below with some data.
Region Divison District Store
---------- ----------------------- ----------- -----------
Northwest Northern California San Jose SJStore1
Northwest Northern California San Jose SJStore2
Northwest Northern California North LA LAStore1
Northwest Northern California North LA LAStore2
Northwest Northern California North LA LAStore3
I want to generate an XML File using SQL/XML Functions and the XML File should look like as given below.
<Region>
<Region Name>Northwest</Region Name>
<Divison>
<Division Name> Northern California </Division Name>
<District>
[code]...
I need to generate a document that include fields provided in following.
CREATE TABLE t
(x_customer_po VARCHAR2(20 BYTE),
x_accessory_code VARCHAR2(3 BYTE),
x_install_loc HAR(3 BYTE));
INSERT INTO apps.T
VALUES('N45882','D0C','MFG');
INSERT INTO apps.T
[code]...
How can I get the output as formated XML?
--output:
<OrderAcknowledgments><Sender ID="999999999" Name="Cars"></Sender><DateTime>2011-11-27T22:00:14.3</DateTime><Acknowledgments><Acknowledgment><Vehicle><CustomerPONum>N45882 </CustomerPONum><Options><Option><Code>D0C</Code><InstallLocation>MFG</InstallLocation></Option><Option><Code>R0B</Code> <InstallLocation>MFG</InstallLocation></Option><Option><Code>LK2</Code><InstallLocation>MFG</InstallLocation></Option><Option ><Code>K0B</Code><InstallLocation>MFG</InstallLocation></Option></Options></Vehicle></Acknowledgment><Acknowledgment><Vehicle ><CustomerPONum>N45883</CustomerPONum>
[code]...
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 have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.
For example:
select
x,y,z
from
mytable@dblink
returns 788,324 rows
while
select
x,y,z
from
mytable
returns 712,102 rows
It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.
in my sql we can use limit to select first 4 rows in the table then next 4 rows ,can oracle do that ?
I have fifty rows inside the table
select * from student where rownum between 0 and 4 order by stuid ->it work
select * from student where rownum between 5 and 9 order by stuid -> did not work
what is the correct way to do it ?
I am trying to find records in table A that don't have any corresponding records in table B
select a.ID,a.templatenames from cbe a where not exists (select 1 from cbe_child b where a.ID=b.cbe_id)
Both tables have about 100 mil rows. And there is no Parent/Child relationship here, A.ID is a PK column.how can i write this select to perform better.