SQL & PL/SQL :: Error For Creating Tree Structure - Not Showing All Records

Mar 1, 2013

I have one table test_pc :

EVENT_IDPARENT_EVENT_ID
2001
2002 2001
1001 2002
1002 1001
1004
1003 2005
1006

Scripts are mentioned below :

create table test_pc
(event_id number(10), parent_event_id number(10));
insert into test_pc values(2001,null);
insert into test_pc values(1006,null);
insert into test_pc values(1004,null);
insert into test_pc values(2002,2001);
insert into test_pc values(1001,2002);
insert into test_pc values(1002,1001);
insert into test_pc values(1003,2005);

In order to derive parent child relationship I have ran the following query :

select *
from test_pc
start with parent_event_id is null
connect by nocycle prior event_id = parent_event_id;

It is giving all the records except the last one (i.e. event_id = 1003 and parent_event_id = 2005), because the parent_event_id does not exists in the table.

But my requirement is to show all the record, i.e. if the parent_child relationship is present then show accordingly and also show the rest of the records where the parent_child does not exists even the parent_event_id exists.

View 5 Replies


ADVERTISEMENT

Structure In Tree Query

Sep 8, 2012

Explain the following query to me:

in particular Lpad (' ', Level-1)*3.

select lpad(' ' , (level-1)*3) || emp_last_name employee
from emp
connect by supervisor_emp_id = prior emp_id
start with supervisor_emp_id is null;

View 1 Replies View Related

PL/SQL :: Copying A Tree Structure

Oct 4, 2012

I have a hierarchy tree i query using connect by. The primary key is the up level to other parts. I need to copy the entire tree structure as a new structure.

This will mean renaming the IDs to new unique ID yet keeping the up levels correct.

i.e.

ID DESC UPLEVEL
1   TOP
2   desc2           1
3   desc3           1
4   desc4           3
5   desc5           4
6   desc6           1so if i coped this i would expect
7 TOP
8 desc2 7
9 desc3 7
10 desc4  9
11 desc5 10
12 desc6 7

View 5 Replies View Related

Forms :: How To Create Tree Structure In 6i

Apr 5, 2011

I want to know how to create tree structure in forms6i. This should be created as " Control-block". For example when user expand department it shows all department names in college as sub tree. Note: I have no table for department..Just display manually..

View 1 Replies View Related

SQL & PL/SQL :: Hierarchy Query And Tree Structure

Jun 26, 2012

How to create one complex query. here what I have in my table.

DELIVERABLESPRECURSORS
------------ -----------
4 15
15 332
15 26
26 332
29 4
29 15
224 26
224 274
224 259
259 29
274 15
288 259
288 26
288 274
916 27
917 27
918 27

I need a query which run against this table and bring me the following result.

4 > 15 > 332
15 > 332
15 > 26 > 332
26 > 332
29 > 4 > 15 > 332
..... and so on

In theory, I need query which will track DELIVERABLES'S PRECURSOR and PRECURSOR's of there PRECURSOR'S.

View 16 Replies View Related

Application Express :: Tree View On Application Builder Not Showing

Jul 16, 2012

I have some applications on Apex public workspace (apex.oracle.com), and suddenly the Tree View, on Application Builder stopped showing any content. If I select "Component View", I see the page components, but if I switch to tree view, it shows me only some empty frames.

View 2 Replies View Related

SQL & PL/SQL :: What Should Be Order Of Columns While Creating B-tree Non-unique Index

Jul 19, 2012

What should be the order of columns while creating b-tree non-unique index.

Low cardinality first
or
Hight cardinality first

View 15 Replies View Related

Application Express :: Creating Tree With Multiple Tables?

Jun 20, 2012

I want to do a tree view based on the following tables in APEX:

create table plattform (
id number,
name varchar2(200))
create table environment (
id number,
plattform_id number,
name varchar2(200))

[code].....

View 4 Replies View Related

Forms :: Viewing Records From A Tree?

Mar 1, 2012

i've got problem when viewing my records from a tree. When i click a data from a tree it displays 2 times. and when i try to click another data my previous data was gone.

my screenshot is attached

this is my
DECLARE
mytree ITEM;

[code].....

View 37 Replies View Related

SQL View Showing Non-related Records?

Jun 27, 2011

I'm trying to get a list of values from a table, and I am just not seeing the answer -I have a table with 4 records (say A, B, C, and D), each record has a GUID to uniquely identify it.I have a 2nd table that holds the relationships (parent-child).

At this point, I have 2 rows:
A>B
A>C

So, A is a parent to B, and A is a parent to C (D is not related to anything at this point)

I would like to be able to go into the D record and see that it is NOT related to A (A would be the parent in this instance) - the trick however is I only want 1 instance of A to show up.attempted to use some inner/outer join qualifications and I just cannot seem to get the data to display properly.

View 1 Replies View Related

Precompilers, OCI & OCCI :: Receiving Records Of Table In Array Of Structure

Mar 12, 2009

sample code in OCI in C for receiving records of table in array of structure? Or dynamically storing the result-set in an array..using array of pointers to structure..

View 6 Replies View Related

Forms :: Showing Number Of Records Affected (saved)

Jul 31, 2010

We are building a custom form extension with our EBS R12 where against certain parameters we are inserting and updating records in a table through a PL/SQL procedure.This PL/SQL doesn't use a commit exclusively, instead the user must click the Save button after verifying the data (new records are inserted, existing ones are updated and after the insert and update routines, execute_query is fired to fetch the latest data)

Everything works fine, however, when the user clicks on the Save button form status bar says "FRM-40401: No changes to save"

We would like to alter this message with our own custom message stating how many records were inserted and hows many were updated (We have counters counting this information)

Under which particular trigger we should write the code to enhance this requirement?

View 2 Replies View Related

Forms :: Vertical Scrollbar Not Showing All Records When Scroll Down?

Sep 14, 2011

Vertical scrollbar not showing all the records when I scroll down.

Using Oracle forms 10g , operating system windows.

I have two fields with number of items dispayed = 15.

I have a vertical scroll bar with them. There are 34 records in the table but the scrollbar only shows 15 records.

View 22 Replies View Related

Forms :: Show Organization Structure In Top Down Organizational Structure?

Sep 3, 2012

I am struck in a requirement to show the organization structure in a Top Down Organizational structure.

Is there any way we can make the well structured report OR form?

View 4 Replies View Related

Forms :: Data Entering Form Not Showing Any ERROR Messages / SAVE RECORD Message

Jan 15, 2013

I developed a form in forms9i, at the time of data entering form is not showing any ERROR messages or SAVE RECORD message and when I press exit button it is asking "DO YOU WANT TO SAVE THE CHANGES YOU HAVE MADE".

I have checked my PRIMARY KEY field and there is no mistake and value is populating at PRE INSERT.

View 2 Replies View Related

Forms :: Creating Alert After Records Retrieved From Query

Jun 26, 2013

I want to raise a alert after the records retrieved from a query. i.e, if the query fetches 0 records then the alert show be displayed. Is is possible? If yes tell me the trigger to be used to raise such kind of alert.

View 4 Replies View Related

Forms :: Data Have Been Displayed Normally / Tree Data Is Hidden With No Error

May 10, 2010

i'm use oracle form builder 6i when i use tree item to display data

the data have been displayed normally but at runtime when i resize the window the 'tree data' is hidden with no error.

View 1 Replies View Related

SQL & PL/SQL :: Error With Creating Job

Sep 18, 2010

I am writing the following PLSQL code-

BEGIN

DBMS_SCHEDULER.CREATE_JOB
(
job_name=>'TRIAL JOB',
job_type=>'PLSQL BLOCK',
job_action=>
[code]........

The new table used above has the following structure

Name Null? Type
STAMP TIMESTAMP(6) WITH TIME ZONE

However I get the following error on execution and am not able to make out why?

BEGIN
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 3

View 4 Replies View Related

Error While Creating Catalog DB

Aug 6, 2010

error while creating a catalog db

error creating db
ORACLE error from recovery catalog database ORA-01552: cannot use system rollback segment for non-system tablespace 'TS'
ORACLE error from recovery catalog database:ORA-00942:table or view doesnt exist

RMAN-00571:--------------------------------------------------------------------------------------------------
RMAN-00569----------------ERROR MESSAGE STACK FOLLOWS-------------------------------------------
RMAN-00571:---------------------------------------------------------------------------------------------------
RMAN-06433:error installing recovery catalog

View 1 Replies View Related

SQL & PL/SQL :: Error In Creating DBMS_SCHEDULER Job?

Feb 25, 2013

I have create the following procedure to create individual jobs dynamically on each schema.

CREATE OR REPLACE PROCEDURE APSP.JOB_TEST_RAM
AS
V_SQL VARCHAR2 (1000);
BEGIN

[Code]....

While executing this procedure I am getting the below error.

PLS-00103: Encountered the symbol "APPS_XX" when expecting one of the following:

View 5 Replies View Related

Error While Creating Table In Oracle 10g?

Aug 19, 2008

If i create a table in Oracle 10g it givea me the follwoing Error.. what couls be the error...

ORA-00911: invalid character
create table shopper
(
cShopperId varchar2(30) primary key,
cPassword char(20) not null,
vFirstName varchar2(20) not null,

[code]....

View 1 Replies View Related

Error Creating View With Union

Jan 14, 2011

i have a union all query (two quiries i suppose) that runs fine and returns the results i am after.... when i try to use this query to create a view I get

SQL Command: CREATE OR REPLACE FORCE Failed: Warning: execution completed with warning

CREATE OR REPLACE FORCE VIEW "schema1"."name_of_view" ("column1", "column2", "column3") AS
SELECT b.column1, a.column2, c.column3

[Code].....

View 4 Replies View Related

SQL & PL/SQL :: Creating Custom Error Message

Apr 24, 2013

I am trying to create my own error message but when ever i used sp_addmessage it gives error sp2-0734 unknown command biggning... what to do?

View 12 Replies View Related

Error In Creating Password File

Dec 2, 2010

I am creating prim and phy standby on same WIN machine. I have done with all settings. I can do TNSPING to both oracle_sid.I am trying to connect as sysdba to UP the phy standby. But it gives below error:

Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

So i tried creating password file for phy standby. But gives below error:

Unable to find error file %ORACLE_HOME%\RDBMS\opw<lang>.msb

I am running it from proper dir.

View 4 Replies View Related

SQL & PL/SQL :: Error In Creating Materialized View

Oct 1, 2012

Am trying to create a materialized view as below

SQL> CREATE MATERIALIZED VIEW emp_view_73 BUILD IMMEDIATE REFRESH COMPLETE ON COMMIT
2 AS
3 SELECT em.employee_id,
4 em.employee_fname
5 ||' '
6 ||em.employee_lname employee_name,
[code]....

ERROR at line 12: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

View 37 Replies View Related

SQL & PL/SQL :: Getting Error While Creating Backup Table?

Oct 17, 2013

While creating temp backup table it shows error ORA-00904 'invalid identifier'

CREATE TABLE xxom_valvelink_vlvs_temp AS
SELECT msie.inventory_item_id,FDFC.application_column_name,
flv.tag,

[Code]....

View 10 Replies View Related

SQL & PL/SQL :: Getting Error While Creating Interval Partition

Sep 19, 2012

I have this requirement to pull the ACTIVE days that a participant holds true in a given month for a specific position he/she holds.The three date ranges here are: LOA dates(leave of absence), XFER dates(TRANSFER in/out of the position),Quality compliance(QUAL start/end dates).So,

INACTIVE DAYS = [(LOA + XFER + WW) - OVERLAPPING DAYS OF (LOA+XFER+QUAL)]
CREATE TABLE PAYEES
(
RUN_PERIOD DATE NOT NULL,
PAYEEID VARCHAR2(20 BYTE) NOT NULL,
LOA_START_DATE DATE,
LOA_END_DATE DATE,
[code]....

I am able to pull out the no of days on a individual basis(count of days for Xfer/LOA/Qual) but I am really not sure as to how should i go about determining the overlapping days between 3 date ranges to determine the ACTIVE/INactive days.

View 4 Replies View Related

SQL & PL/SQL :: (error) PLS-00103 / Creating Package

Jul 19, 2012

When trying to create package I receive this error...[Error] PLS-00103 (8: 16): PLS-00103: Encountered the symbol "." when expecting one of the following:;

pointing to "." in the last line "END nondrg_prod.Readmissions;"

CREATE or replace PACKAGE nondrg_prod.Readmissions AS
TYPE Readmits IS REF CURSOR RETURN READMISSIONS_DATA_MH%ROWTYPE;
PROCEDURE open_rdmh (rdmh IN OUT rdmhCurTyp);
END Readmissions;

[code]....

View 14 Replies View Related

Creating Sql Table Getting Error ORA-00907?

Jul 30, 2012

creating a sql table im getting error " ORA-00907: missing right parenthesis"

CREATE TABLE Campaign
(campaign_id NUMBER(5) PRIMARY KEY,
Account VARCHAR(20) NOT NULL,
SalesLead ID,
SCLead ID,

[code]....

the problem is "ID" and "ID+R", i need these to conect with other sql tables

View 7 Replies View Related

PL/SQL :: Error While Creating View From One Instance To Another?

Aug 22, 2012

H want to Create a view which reside in one instance for example xxx Instance . I want to create the same view which reside in xxx instance to another instance for example YYY Instance How to acheive this ?I tried desc the View take the view script from XXX instance and copied the view script to YYY instance when I compiled Iam getting an Error

For Example My view in XXX Instance
CREATE OR REPLACE FORCE VIEW xxx.sample
(
First_name,
last_name
)
as
     select "F_NAME",
             "L_NAME"
     FROM  EMP;

When try to copy and compile the same view in my YYY Instance i am getting error

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved