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
ADVERTISEMENT
Mar 12, 2008
I have a table of N records with: Name SeqNo ID Col4 ... ColX
where Name and ID are non-unique, and SeqNo is a monotonic non-consecutive sequence 0 .. N that is unique within ID..I'd like to generate the following 'groups': For each record where SeqNo = 0, sorted by Name, create the 'group where ID is the same, ordered by SeqNo irrespective of the
values of any of the other columns. For instance, if the table contained:
NameSeqNoIDCol4 ... ColX
RPL2975...
TLM0444...
AAB2801...
AZZ0801...
ABA3444...
KTT4975...
ABA1801...
YHG0975...
DEF1444...
I'd like to generate:
NameSeqNoIDCol4 ... ColX
AZZ0801...
ABA1801...
AAB2801...
TLM0444...
DEF1444...
ABA3444...
YHG0975...
RPL2975...
KTT4975...
I got my desired results by brute-forcing via four sub-queries:
Sub-query 1 - Generate the sorted Names with SeqNo = 0
Sub-query 2 - Expand above with the additional columns,
maintaining original order
Sub-query 3 - For each of the records from sub-query 2,
generate the 'dependents' having the same ID
and SeqNo != 0
Sub-query 4 - Expand above with the additional columns,
maintaining original order of sub-query 1
Main query - Create UNION of 2 and 4, sorting by original
order and SeqNo
if there were not a simpler approach - after all, this must be a fairly common issue when generating BOMs.
View 4 Replies
View Related
Aug 16, 2013
I created a user and granted connect, resource to the user. but trying to select columns from the view USER_HISTORY$,it's showing no value, i tried to login the user several times, but it's displaying no value, any particular reason or any minor mistake?
View 1 Replies
View Related
Nov 1, 2011
We have a view that shows the all the data that we expect, so we created a report to show that data, the report worked, then all of a sudden the report stopped showing all the data. (we did have a server move)I checked the view and the all the data is there....
I have created a new report based on the view but it does the same thing, it doesn't bring all the data up (i get very old data), there are no conditions or anything.
other tables and views seem fine. i have recreated the view and i still have the same problem.
View 2 Replies
View Related
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
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
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
View Related
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
Feb 27, 2012
Im running the following query in DB - Prod2
select *
from t_act_rwrd_reimb_stay a
where reimbursed_amt < 0
and invoice_date = '01-JAN-1900'
and not exists ( select 'x' from t_act_rwrd_reimb_stay b
where a.GPM_ID_STAY = b.GPM_ID_STAY
and reimbursed_amt > 0 )
The above table t_act_rwrd_reimb_stay --> is from a view on the database Prod2 and this view is pulling the data from the database prod1 via dblink
When I run the same query on Prod1 it returns 3 rows. Is there any reason this is not fetching data on prod2 view? or whatz wrong with the above query
or for example if I run the above query as follows with the dblink "@prod1" it returns the data properly
select *
from t_act_rwrd_reimb_stay@Prod1 a
where reimbursed_amt < 0
and invoice_date = '01-JAN-1900'
and not exists ( select 'x' from t_act_rwrd_reimb_stay@prod1 b
where a.GPM_ID_STAY = b.GPM_ID_STAY
and reimbursed_amt > 0 )
View 10 Replies
View Related
Aug 1, 2013
I need all users to be able to view all records from table TABLE1. I would like to provide insert/ update/ delete privilege if the column "TABLE1"."UPD" = 'Y'. If TABLE1.UPD = 'N', then the users can only do select on the record. I don't know if the roles can handle based on row values.
View 5 Replies
View Related
Sep 14, 2007
I have set up a view that pulls news & events records.
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.category, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType
FROM(so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID
[Code].....
For events I want only the events that have a p.startDate equal to today. I have tried setting that line up a few different ways.
p.startDate = sysDate didn't provide any records for events
p.startDate = TO_CHAR(sysDate, 'MM/DD/YYYY') freaked the system out and nothing appeared for either parts of the union
how to output only events that are for today?
View 3 Replies
View Related
May 8, 2012
which is the fast way of inserting 60 millions of records from a view to a table.
method 1:
create table t_temp_table as select * from v_dump_data;
method 2:
through bulk collect
---Bulk_Collect With FORALL----------
DECLARE
TYPE srvc_tab IS TABLE OF t_temp_table%ROWTYPE;
l_srvc_tab srvc_tab := srvc_tab();
l_start_time NUMBER;
l_end_time NUMBER;
l_error_count NUMBER;
[code].....
View 4 Replies
View Related
Nov 13, 2009
One question reg Materialized views.
If as part of housekeeping of the Source database we delete some records (older records), will the materialized view also be updated with the deletion? I believe the answer is yes. In that case can we ensure that this delete does not happen?
Is there anyway we can prevent MView refresh from deleting the records that is once inserted even if we delete the same records in source DB?
View 3 Replies
View Related
Oct 13, 2012
Using Oracle 11g...We have a table in our database of data with the following information:
MASTER_RECORD,
MEMBER_RECORD,
BUSINESS_UNIT,
GENDER,
DOB (date),
age [at time of month_record],
MONTH_RECORD (date) [31-MON-YEAR for recorded active month]
The table has ~55 million records. Existing index is only on MASTER_RECORD.There is now a need to create a view which is an aggregate count of member records, grouped by business_unit,gender, age per year. eg:
business_unit, gender, age, month_record, num_of_members -> for every combination
unit5, F, 25, 31-JUN-2011, 622
unit3, M, 18, 31-MAY-2011, 573
The view can be created now, but, is not fast enough to be reasonably considered a view. This table is re-created every month from a procedure, so there is flexibility on how it is created. Use interval partitioning by year( something I have not experienced using), create an index on the month_record,then create view.
View 2 Replies
View Related
Feb 20, 2012
i have a view in a schema of my database 'vw_get_noti_calendar_data' and i want to select some field but it shows an error 'invalid number' and i have observed it but no effect,so where is the error.
my code is---------
SELECT *
FROM aepuser.vw_get_noti_calendar_data
WHERE TO_DATE (TO_CHAR (event_start_date, 'mm/dd/yyyy'), 'dd/mm/yyyy')
BETWEEN TO_DATE (TO_CHAR ('2/28/2012', 'mm/dd/yyyy'), 'dd/mm/yyyy')
AND TO_DATE (TO_CHAR ('2/28/2012', 'mm/dd/yyyy'), 'dd/mm/yyyy')
AND srnum BETWEEN 5 * 1 + 1 AND 5 * 1 + 5;
View 11 Replies
View Related
Jul 12, 2012
What is the default key created when a primary key is created in a table ?
View 1 Replies
View Related
Jan 20, 2012
is there possible to get those column names on which we have applied dml operations like insert, update,delete if yes,then how .
View 5 Replies
View Related
Aug 29, 2008
Im having a problem with writing an appropriate query for a report in my web application. I need it to extract data from three related tables:
CAR(
PK CAR_ID INT NOT NULL,
TYPE VARCHAR NOT NULL)
REPAIR_CENTER(
PK REPAIR_CENTER_ID INT NOT NULL,
NAME VARCHAR NOT NULL)
[code]...
I need the report to display only available cars. Available cars must have these characteristics:
1. if the CAR_REPAIR table is empty, displays all entries from CAR table...
2. if car has multiple entries in the CAR_REPAIR table display only the latest DATE_RETURN if its lower than todays date (SYSDATE), otherwise don't display that car...
3. don't display cars that are in the CAR_REPAIR table and have DATE_RETURN value of NULL
View 3 Replies
View Related
May 15, 2009
I have to reorganize one table that related to several other tables. The reorg is too slow when it runs on this table. I would like to create one image of the table and synch it with the original one in real time. So when I run the reorg, I will use the image table that does not constrained by indexes and other objects. Once the reorg is done, I would like to rename the table. how could I do the replication in real time?
View 2 Replies
View Related
Apr 13, 2012
how to delete a data which is related to many no of child table and according to setnull and casecad by using subprogram?
View 6 Replies
View Related
Sep 17, 2012
I studied the documentation and many websites about DBMS_JOB but I am still confused.How do I schedule a procedure to run i.e. every sunday 10:00 AM ?This is what I tried:
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job => X
[code]...
Is the Interval related to next_date ?
View 1 Replies
View Related
Oct 31, 2011
How to find out the tablespaces specific to application (i.e. not related to oracle like system, sysaux,temp,undo etc..)
View 1 Replies
View Related
May 9, 2012
OS: AIX 6.1 64bit
DB: Oracle 10gR2 (10.2.0.5.0)
What we did is..
1. Install patch 6613550 (./rootpre.sh)
2. Install 10.2.0.1 for AIX 64-bit
3. Install 10.2.0.5 Patch Set
4. Create Database
5. Created/Setup LISTENER and TNSNAMES (test connection successful)
After installation of database, everything went well until we tried to restart the server, what happened is that we cannot start the listener, the error was:
TNS-12555: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00525: Insufficient privilege for operation
IBM/AIX RISC System/6000 Error: 1: Not owner
What i did to resolve this problem is to change the owner of /tmp to oracle, after that i was able to start the listener. ($lsnrctl start) The problem is that our sys admin said that the /tmp directory is being used by the OS and it's owner must be root. But if i return it to root, the listener will not start again. (After server restart)
BTW, our /tmp has enough space so we did not perform the following steps from the installation guidelines:
$TEMP=/directory
$TMPDIR=/directory
$export TEMP TMPDIR
I was thinking if that we must make a temp directory for oracle and set it in the profile, or change the owner to root again and perform this:
$chown -R oracle:oinstall /tmp
$chmod -R 777 /tmp
View 2 Replies
View Related
Jan 26, 2012
I wanted to know how to find the links between cursors that have been created in v$sql after compiling a PLSQL procedure.
For example, if i compile the following procedure :
create or replace
PROCEDURE PROCEDURE1
AS
BEGIN
insert into t values(1,1);
END PROCEDURE1;
, the 2 following cursors are created in v$sql :
select SQL_ID, SQL_TEXT from v$sql
where sql_id in ('71pj8t5nz1d80','2s567zb6684sh');
"SQL_ID""SQL_TEXT"
"2s567zb6684sh""BEGIN PROCEDURE1; END;"
"71pj8t5nz1d80""INSERT INTO T VALUES(1,1)"
Thus, i would like to know how to find that the cursor 71pj8t5nz1d80 is called/linked by the cursor 2s567zb6684sh.
This would be useful for interpreting some sql statistics in v$sqlstats in where such cursors appear and related sql statistics are accounted twice.
View 1 Replies
View Related
May 29, 2012
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.
3. And at the end retrieve the table data.
View 1 Replies
View Related
Jan 11, 2013
Does the column V$SQL.CHILD_NUMBER related on number of current cursor only ? V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text enteredI see in V$SQL big numbers in V$SQL.CHILD_NUMBER ( HASH_VALUE and PLAN_ HASH_VALUE is in my quewry fixed ) , am I wrong that this column displayed not summary count of "objects"(in terminology of Steeve. URL....
View 0 Replies
View Related
Jun 5, 2013
in APEX 3 i always had a report showing all linked pages and components...
The Report was located here: Home>Application Builder>Application ID>Application Reports>Page Components>Related Pages and Components I have now updated to APEX 4 and i can't find this report anymore.
View 3 Replies
View Related
Apr 27, 2010
My problem consists in setting dynamically a data block related to the item chosen by the user.So I ask if it is possible, in forms 10g, to base dynamically a data block with different tables having different columns.
View 5 Replies
View Related
Feb 11, 2011
I have few queries on PGA memory management.Since these queries are based on 2-3 examples not exactly same by nature I am summarising it after my understanding for the same
As I understand many workareas can be allocated to a single sql statement and number and sizes of theses workareas is controlled internally by Oracle when Automatic Memory management (PGA_aggregate_target and workarea_size_policy=Auto are set) Since many sessions share the PGA memory, the amount of memory available to each session may vary and if less amount of memory is available for a session for sorting then TEMP tablespace is used
[1] Can we say paging happens and can be checked at this time?
[2] Is there a difference in handling memory while populating pl/sql tables?
As I have encountered ora-04030 while some our developers were populating pl/sql tables but never encountered this error for sorting, hash joins etc Though I don't remember the width of pl/sql table, I am sure the developer used 'LIMIT' clause during bulk collect and still faced the issue.
With a single session on the server, I noticed that the difference in values displayed issuing 'free' command in linux and output values from sesstat did not match at all while there wasn't any heavy OS process involved during the period. I was expecting 'used' and 'free' values displayed by free command (linux) will change and difference would be approximately equals 'before and after values of session pga memory.
[3] Isn't it expected to match?
[4] Can we say in dedicated server, at any moment of time, the SUM of 'session pga memory' represents all the memory used by Oracle SGA, at that point of time?
select sum(value)/1024/1024 "memory in MB" from v$sesstat where statistic#=20;
During one of the tests I got following output (divide value by 10 for my visibility and avoid formatting)
SQL> select a.name, to_char(b.value/10, '999,999,999') value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name like '%ga memory%'; 2 3 4
[code]...
The above query is showing above values even when the pl/sql block execution is completed 30 minutes back
[5] Do we call this as 'memory leak' where memory is not released even while some time has passed since session has done something?Of course I am not checking at OS level as mentioned in question [3] above the values won't match!
Still the output of free command for reference(After the pl/sql block executed)
SQL> !free
total used free shared buffers cached
Mem: 3016796 2999660 17136 0 4308 1173260
-/+ buffers/cache: 1822092 1194704
Swap: 1048568 636124 412444
--(After the pl/sql block executed)
SQL> select * from v$pgastat;
NAMEVALUEUNIT
aggregate PGA target parameter 524288000bytes
aggregate PGA auto target 456256512bytes
global memory bound 26214400bytes
total PGA inuse 17328128bytes
[code]...
[6] What could be the significance of negative values of 'session pga memory/max'?
Last We have an OLTP system and in the night we run batch processes in 2-4 sessions
Suppose I have 10 GB RAM and with PGA setting of 3.5 GB Now I want the batch process sessions to use max possible memory during nighttime and toggle the setting back in the morning
[7] With above settings (10 GB RAM and 3.5 GB PGA) how can I divide the memory among 4 sessions?
Shall I set 1) PGA_aggregate_target=0 2)Workarea_size_policy=manual 3) Sort_are_size 4) Hash_area_size
[8] What would be approx values for parameter 3 and 4? will it be straight 3.5 GB/ 4?
View 8 Replies
View Related
Jan 9, 2013
select * from V$VERSION
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
I have loaded into an Oracle table defined as XMLTYPE. I'm okay with PL-SQL and stored procedures, where I will ultimately do this processing once I get a handle on XML DB querying from an XMLTYPE table.
Based on reading the oracle documentation about XML DB, and more importantly, reading dozens of posts to this forum,I have been successful in loading the XML files into a SQL XMLTYPE table and doing simple queries against that table to retrieve some of its data so that I can then insert that data into other target oracle relational tables.
how to select parent/child data from XMLTYPE tables. I am able to follow the forum examples and can replicate the methods shown on the many example XML contents shown on this forum, but not against the XML that I have to process. I am wondering if my struggle is caused by my lack of knowledge, or by ill-formed XML content supplied to me by the educational vendor.The XML content has structured the XML content nodes in such a way that I do not seem to be able to apply the parent/child sql methods.I have been able to use for other XML examples I have tested against.
My XML file shown below represents High School Transcript data, for which I need to be able to parse out into my own oracle relational tables for that student, his personal info, and his course info, etc. i.e., for our example, which courses he has taken for which High School grade levels. The vendor-supplied XML seems to put the Courses and the High School grade level in "parallel nodes," instead of parent/child nodes, so I am struggling to be able to use SQL to differentiate which course the student took in NinthGrade versus TenthGrade.
-- WHat I would like to determine from a select statement:
LASTNAME GradeLevel COURSETITLE
=============================
Smith NinthGrade PHYS ED 101
Smith TenthGrade CALCULUS 201
Smith TenthGrade ZOOLOGY 202
(The data has been simpliied and masked, but is true to the content and is queryable).
select * from V$VERSION
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]....
View 3 Replies
View Related
