I want to insert 1st two rows into Hierarchy table, then I would like to update Supervisor_2 to Supervisor_4. Here I don't want to use 'CONNECT BY PRIOR', as it take more time to execute (there are millions of records).SQL code for same.
My Form consists two Hierarchical trees. When I select a node from first tree and I press Move Right (>>) Button the selected node should move to the second tree. Similarly when I select a node from second tree and press move Left button(<<) it should move to the first tree.
I also want to know want to insert values Into New Table using node values from The Displayed Hierarchical Tree. How to retrieve values using populate_Group_from_tree and insert into table.
I am writing a SQL inside an application to retrieve data from a table for certain IDs or for All employees based on the user's input. I create a variable :SELECTION for the where clause. When I run the SQL, I receive the error of missing right parenthesis. There is no error if I hard code the IDs.
If the input is 'All' -> the variable :SELECTION is blank so if the SQL :
select name, address, ID from employee :SELECTION
will be
select name, address, ID from employee
If the input of users ID as '1234','9999' - > the :SELECTION = ' where ID in (:VARIABLEID)' :VARIABLEID = '1234','9999'
so the SQL: select name, address, ID from employee :SELECTION
should be
select name, address, ID from employee where ID in ('1234','9999')
However, I got the missing right parenthesis error. I suspect it is something with the single quote, so I tried different ways, but it is still not working.
1. For each of the Filename, if originating_site is not the same as receiving_site, it means that the file has been sent to receiving_site but has not been acknowledged received yet.
2. For each of the Filename, if originating_site is the same as receiving_site, it means the file has been sent and received by the receiving_site.
My task is to list out all the Filename per receiving_site that has been sent, but not received yet by the receiving_site. For example from the sample data above, I am expecting to see that siteA, fileB has not been received yet.
How can I do that? I had tried MINUS and NOT EXISTS command, but I am just not able to get the result that I want.
I have a "select ... into" in my PL/SQL and it doesn't retreive any data(I suppose just certain columns because if it doesn't retreive a row then the PL/SQL should throw an error) when there are many users accessing this table. It shouldn't be some table locking right? Because if it is, it should wait until the table is available then it will retreive data. Currently it just proceeds with the PL/SQL and selecting nothing in that query. what details should I look at to trace the cause of this problem.
I use the oracle 10g database.I am trying to insert and retrive the image.Inserting an image is done.but while retrieving an image iam getting an run time exception in java "java.sql.sqlexception:general error".i am not able to understand this.
The code to insert the image is FileInputStream fi=new FileInputStream(f); int size = fi.available(); System.out.println("j"+size); byte b1[] = new byte[size]; i=fi.read(b1,0,size); System.out.println("i"+i); st.executeUpdate("insert into image1 values('b1',"+k+")"); when i am retrieving the image i tried like this ResultSet rs=st.executeQuery("select imagecolumn from tablename"); here iam getting an exception as i named above.
I wanna have a form which has a list box which is including of all table names, by selecting the required table by user all column name and its related data should be retrieve.I have a cursor which give me all field names related to the table by I do not know how can I retrieve table data:
1. How to add item through block base on number of column which selected table has.
2. How to set value of table column in these mentioned column which added in the block.
base on performance it is better to retrieve data from view or mention the table names directly?
I have a select statement in from clause one of my table is view (which is having data collected from four tables) my question is whether performance of querry will be improved if i use directly all tables( four tables of a view) instead of a view
We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,
Hierarchy Table Parent Child Report1Table A Table ATable M Table ATable N Table NTable Z
Requirement :
From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this
-ParentChild 1Child 2 Child 3 -Report1Table ATable M -Report1Table ATable N Table Z
Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.
SQL Statements to create hierarchy table:
create table hierarchy (parent varchar2(20), child varchar2(20)); insert into hierarchy values ('Report1','Table A'); insert into hierarchy values ('Report1','Table B'); insert into hierarchy values ('Table A','Table M'); insert into hierarchy values ('Table B','Table N'); insert into hierarchy values ('Report2','Table P'); insert into hierarchy values ('Table M','Table X'); insert into hierarchy values ('Table N','Table Y'); insert into hierarchy values ('Report X','Table Z');
Approached already tried :
1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;
2)Using connect by path function : select * from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path from hierarchy start with parent='Report1' connect by prior child =parent) a where Leaf not in (0);
Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.
I have a table (Parent - Child).There is a requirement to maintain this table, thats the hierarchy of the oraganisation.So, every quater they will be updating the table. They will be importing the data through an excel and in that excel there are 3 action items,
For Update:What all validation can come for an updation of an hierarchical data in general.Like = how to derive the level value at database side when the id is updated to some other level.= How to maintain the relation. A -> B -> D ( A is the grand parent here).A -> Ceg: if B is updated as parent node of A, then we should throw error (cyclic data). Any more validations for hierarchical data
That�s generally the format the values would appear in the table if I just did a standard select. I want it displayed in a more hierarchical Parent � child way.
The format I need to get out is as follows: Lvl KeyParKey hasCh 1k101 2k34k10 2k22k11 3k56k220 3k109k221 4k61 k1090 3k67k220 2k24k10 1k200 1k301 2k13k30 2k52k30 2k35k30 2k13k30 1k401 2k11k40
I would like to display data using the Hierarchical tree up to 3 levels.I need to create all the nodes programatically.
Means Create the parent (Parent1) level nodes , then create the child (Child1) nodes and add them under the parent1.Similarly create child nodes (Child2) and add them under the parent (Child1).
I am interested about the fast way to access all data in physical block. what is the quick way to bring data blocks using the rowid, I found this script but soon as I can have faster access:
select * from table_name t WHERE ROWID between 'AAAUaOAAEAAHkJiAAA' and 'AAAUaOAAEAAHkJiAA8'; where 'AAAUaOAAEAAHkJiAAA' is the last element in the block and 'AAAUaOAAEAAHkJiAA8' is the first one
my question is can retrieve all the data in one block more quick than this query.
CREATE TABLE MAT (matrl varchar2(100), date_man date, weight number(10) );
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat1','12-DEC-10',100); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat2','13-DEC-10',200); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat3','21-DEC-10',300); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat4','26-DEC-10',400); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat5','22-DEC-10',500); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat6','02-DEC-10',600); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat7','23-DEC-10',700); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat8','07-DEC-10',800);
I am using LOV on some field to retrieve data, but sometimes it does not retrieve anything cause of unavailability of date its fine but i want to put N/A in that field when such condition is occurring .
select * from testing Quote: ID NAME REDATA -------------------- -------------------- -------------------- 1 ABC,DEF,GHI,LKJ 000001 2 MNC,GHI,CTF 000002
2 rows selected
select * from testing where name like ('GHI,TCF');
now my output should be as above because GHI is common in both the names.if any of the where condition string matches the Name field then I need that record to be retrieved.
,i have a table called travel detail from which i have retrieved few rows based on 1 condition by using LOV and on next few rows modification is allowed. The rows are getting modified but it is also allowing me to enter in next blank row and insert any data in that row.how can i retrieve only that data which is based on the given condition. Further blank rows should not b displayed.
I'm trying to retrieve data with multiple select statements. The query works fine for 1 account (segment6) but fails for more than account. Below is the query.
I have a detailed block with a user ID column. The user name is not available in the block. Therefore, I have created a non-database column to retrieve the user name into it.
Here is the code I've used.
PROCEDURE get_details IS
iLoop number := 1;
CURSOR c is
SELECT FULL_NAME FROM GRP_EMPLOYEE WHERE EMPLOYEE_NUMBER = :USER_ID;