I want Hierarical query to display my Chart_Of_Account. I want to make a tree Form in 6i error i am getting is connect by nocycle prior account_code=parent_code * ERROR at line 4: ORA-00920: invalid relational operator
Table
Create Table Chart_Of_Account (Account_Code Char(19), Account_Title varchar2(70), Parent_Code Char(19)) insert into Chart_Of_Account ('DGHOA01010101000001','TEST','DGHOA01010100000000') insert into Chart_Of_Account ('DGHOA01010101000002','TEST1','DGHOA01010100000000') insert into Chart_Of_Account ('DGHOA01010101000003','TEST2','DGHOA01010100000000')
select -1,level,account_code||' - '||ACCOUNT_Title,'NULL',to_char(ACCOUNT_CODE) connect_by_iscycle from chart_of_account start with Substr(account_code,13,7)='0000000' connect by nocycle prior account_code=parent_code
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 want to display all records from table 1 (even the null values) that do not match records in Table 2. Below I am creating both tables and I am posting the result query I need.
------------------------------------ CREATE TABLE temp_table1 ( name VARCHAR2(12 BYTE), last_name VARCHAR2(12 BYTE), STATE VARCHAR2(2 BYTE), BIRTH_DATE DATE );
CREATE TABLE temp_table2 ( name VARCHAR2(12 BYTE), last_name VARCHAR2(12 BYTE), STATE VARCHAR2(2 BYTE), BIRTH_DATE DATE );
[code].....
The result query need to have 5 rows as shown below
NAME LAST_NAME STATE BIRTH_DATE ------------ ------------ ----- --------------------- john smith MA 12/1/1979 null null AZ null null null CT null null null MA null null null CT null
I am stuck somewhere in my project, situation is that we are trying to display data gathered from 3 different tables, we used cursors for this, here is the cursor declaration.
DECLARE procedure DocInfoCur(myWhere IN varchar2) IS
init_dept_id number; init_dept_name varchar2(40);
rcvg_dept_id number; rcvg_dept_name varchar2(40);
CURSORCUR_DOCINFO IS SELECT DISTINCT I.CTN, I.DIARYNUMBER, I.INITIATINGDATE, T.TYPENAME, I.INITIATINGDEPT, I.DOCUMENTSUBJECT, I.DESTINATIONDEPT FROM DOCUMENTINFO I, DOCUMENTTYPE T, DEPARTMENT D
[code]...
This displays all the records out there.
Now, I have a search form where I need to filter the same data with different parameters, I have compiled the entered data in one string, now i need to join that string at the end of where clause of my cursor, how can I do that, i have tried concatenating both but received compile error, is there a way out in the current scenario???
I have one RDBMS concept question.if i have 2 tables with 0 to many realtionship. can i create referential integrity constraint between them?
In other words how can i tell whether the records in table A(child table) are not orphan records and have 0 to many relationship with table B(parent table) ? Right not there is no constraint defined on both tables.
I've a table (RelationshipX) with two columns with following values. The table represents the relationships. When I run the following query, It will give me all the combination of relationships...however, I need to get 15 unique as defined below.
I run the following query to get below of all the combinaiton (note, I am opting out those six rows which matches to each other) select a.Col2 as Col1, b.Col2 as Col2 from RelationshipX a, RelationshipX b
where a.Col1 = 11060 and a.Col1=b.Col1 and a.Col2 <> b.Col2 order by Col1, Col2 [code]....
HOW can I modify my SQl so I get only 15 unique relationship records. (For example two UNDERLINE rows are technically same, and there are total 15 of them)???.
Just wanted to pose a question Can a (Unique and Not nullable)Alternate key of the parent table be part of child Table in an Identifying relationship i.e part of primary key of a child table????
Is there a rule that a primary keys of a parent table can be used to build a identifying relationship with its child?
I have a table which has 2 one to many relationships like a tree structure in the same table, is there a way that I can show the same on the form and be able to do the insert and update on the same table?
I have been told to create a query which should give out the same output as the UI of an exception management application . However , many referenced tables have one to many relationship . I created a query using a function which was giving the required output . But the performance of the query was not good as for each row . Is there a method to create a select using joins to show one to many relationship in a table .
the relationship on my script.What I want to do is make it possible to assign many courses to a student.But I have read a few books on foreign keys and primary keys, and I still dont get how to go on doing this....
This is how the table creation with the keys looks right now...
CREATE TABLE CAMPUS ( CAMPUSID NUMBER(4) NOT NULL, NAME VARCHAR2(80) NOT NULL, CONSTRAINT CAMPUS_PK PRIMARY KEY (CAMPUSID));
I have a DB that has one parent table with many child tables. I would like to delete all records(child - parent) based upon a particular "net_id". I have three versions that "seem" to work BUT I'd like to use "version2" because I want to go to the parent one time(for the parent delete I just made it simple for me) only and be able to delete the child record(s) from the child table(s). I want to achieve this w/o using triggers...
My question: Is version2 an "ok" way to delete child records or should I try another method(version1 or 3)??
So I bounce my instances and when I checked the parameter SGA_MAX_SIZE, Oracle automatically set a value of 2456M to it:
sga_max_size big integer 2456M sga_target big integer 0
Reading MOS ID 443746.1, there is no clear mention about this relationship. It seems that Oracle automatically set SGA_MAX_SIZE as 60% of MEMORY_TARGET.
In am running Oracle 11gR2 (11.2.0.2.3) under Red Hat Linux.
I have 3 tables (Prospect,customer,user) and I have a contact_dtls table .
In prospect table Prospect_id is the primary like this customer_id in customer table and user_id in the user table
in the contact_dtls table I have a column contact_id and other cols.The contact_id can be from prospect_id,customer_id and user_id in other words we can tell the prospect can be a contact or the customer can be a contact and the user also can be a contact also.
Now the question is "is it possible to maintain primary key and foreign key relationship with this design that means the prospect_id,customer_id and user_id would be the foreign keys to the contact_dtls table.
I have a form that contains a tab canvas. On the second canvas page I have two data blocks with a master detail relationship.The first block (master) is driven by a table (table_one) with the following fields:
The second block only shows error_code and error_code_desc. The point of this canvas page is to add/remove master records and associate detail records with a master. I never want to actually insert or remove records from table two; merely updating the sf_id column in table two.What I am trying to achieve is the following:
1. Create a master record with detail records at the same time. 2. Delete a master record 3. Add and remove detail records
An insert into the detail block is nothing more than associating a master record with a detail record by updating the sf_id column in table two. A delete from the detail block is nothing more than setting the sf_id column to null in table two. I am not actually adding or removing records in table_two.
I have tried overriding the commit on the second block to perform updates instead of Oracle actually trying to insert NEW records into table_two.
I'm designing a canvas which will open up on the click of a push-button from a form A. Now this form A has a data block(ie. single underlying table A).
I want to display data from two tables - Table B and Table C in this canvas in the form of a multi-record grid structure - one below the other i.e. table B data above and table C data below.
Now table B and table C share a PK-FK relnship between them. What I want is when a user clicks on a record in the table B above then the corresponding records in the table C should get displayed below(now there can be multiple detail records in table C for a single record in table B - PK-FK reln)
i'd written code something like this in the When-button-pressed trigger:-
c_where_clause := ....(query from Form A) set_block_property('block B',default,c_where_clause); go_block('block B'); go_block('block C'); execute_query; first_record;
(in addition to the above trigger, I have also included queries in the post-query trigger of the two tables B and C to get some data populated in the non-database fields in this canvas)
do i have to do add a piece of code to loop thru the records...like checking for last record,if not going to the next_record and so on for both the blocks?
I have a table which uses parent-child relationship to store the options available. I need a procedure to give me the full path given the id of a particular option.
I have two different functions. One of them uses the Oracle built in function and the other uses simple queries with a loop.The code of the functions are given below.
Now, the problem is with their "performance". The difference in their performance is significant. The function using the Oracle function takes more than 2 hours to run a query whereas the other function takes less than 2 minutes.
I am having trouble trusting the other function. No matter how many tests I perform on the output of both the functions, it always comes out to be the same.
Function 1 ===================== FUNCTION Gettree (opt_id IN NUMBER,i_app_id IN NUMBER) RETURN VARCHAR2 IS path VARCHAR2(32767); application_no NUMBER; BEGIN
I am working on a project, the insert statement is not functioning properly and I cannot find the error The error is in the insert statement for CustOrder. The error states that NULL cannot be inserted into CustOrder.CustID. I have declared CustID and its reference. I have looked over the code several times and I cannot find the problem.
If it makes a difference I am using APEX. The company has not database or program to work with a database so I am building everything from scratch.
declare l_CustOrderID number; l_CustID number := :P11_CustID; begin
Is there any relationship b/w tuning BUFFER CACHE and BUFFER BUSY WAITS?
1) Buffer Busy Waits are happening as the User process found the same Datablock is being used by another user in the BUFFER CACHE. 2) And also happens, when the server process found the same Datablock are being used in the Datafile.
>select level ,empno,ename,mgr from emp connect by prior empno=mgr start with mgr is null;
Output:- > LEVEL EMPNO ENAME MGR ----- ---------- ---------- ---------- 1 7839 KING 2 7566 JONES 7839 3 7788 SCOTT 7566 4 7876 ADAMS 7788 3 7902 FORD 7566 2 7698 BLAKE 7839 3 7499 ALLEN 7698 3 7521 WARD 7698 3 7654 MARTIN 7698 3 7844 TURNER 7698 3 7900 JAMES 7698
LEVEL EMPNO ENAME MGR ----- ---------- ---------- ---------- 2 7782 CLARK 7839 3 7934 MILLER 7782
Note:- I got only this that this query is alternative of self join and it is giving manager name along with mgr id for each employee but when it gives output i couldn't able to understand how to identify who is manager of whom. Tell and explain with clause in oracle , i only know it is alternative of inline view but i want to know how does it work and how to use 'WITH' in oracle query if possible.
