DECLARE L_DE_TGUP_ID BBP_ALLOC.DATA_ELEMENT.DATA_ELEMENT_ID%TYPE; L_ZERO_EXP EXCEPTION; L_DATA NUMBER; BEGIN SELECT DATA_ELEMENT_ID INTO L_DE_TGUP_ID FROM BBP_ALLOC.DATA_ELEMENT WHERE DATA_ELEMENT_CD = 'TGUP'; SELECT DECODE(UPPER('0.0028'),'',0,'0',0,'NULL',0) INTO L_DATA FROM DUAL; [code]......
Here the relationship between the source and target tables is one to one. but still i am getting "unable to get the stable set of rows from the source tables" error. Source query retrieves only 2 rows which are distinct. but still getting this error.
I am interested if there maybe exists any function that would return all source tables that are present in the given sql. For example function('select 'abc' from table_1, table2') would return a list containing 'table_1' and 'table_2'.
I have problem with merging rows into view. I have created "INSTEAD OF" triggers on the view for insert/delete/update of rows. However, the merge is not working and it needs rowid to work. Is there any way how to make MERGE working here ? I must use merge, because I am changing the structure of database used for large java application. And it uses tons of merge commands,changing them to insert/update is inefficient because of development-time and of course resulting execution-time.
create table val_00 ( id number(10), data varchar2(100), constraint pk_val_00 primary key (id) validate ) organization index; [code]....
What is the easiest way to add 2 rows together? I need this results set below as is except I need rows with activity codes 0 and 30 to be merged together into Activity Code 0. So, Regular hours would be 7466.75. Do I need to do a Union or is there a more elegant solution?
My current sql results set looks something like this:
Activity Code Activity Code Desc REGULAR OT NONPROD DEPARTMENT 0 0: Business As Usual 7409.25 305.5 1603.25 Information Technology 10 10: Special Projects 190.75 17 0 Information Technology 20 20: Strategic Initiatives 1067 52 0 Information Technology 30 0: Business As Usual 57.5 0 0 Information Technology
and I want my new results to look like this:
Activity Code Activity Code Desc REGULAR OT NONPROD DEPARTMENT 0 0: Business As Usual 7466.75 305.5 1603.25 Information Technology 10 10: Special Projects 190.75 17 0 Information Technology 20 20: Strategic Initiatives 1067 52 0 Information Technology
sample1 A B C D E F -------------------------- 1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3 4 4 4 4 4 4 5 5 5 5 5 5
sample2 G H I J ---------------- 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 5 5 5 5
consider the above tables what i want is some thing like this
output G H I J A -----------------
this can also be done through a select statement through choosing the columns which you want i don want thatbut what i want is columns of entire table sample2 and only one column in sample1
I am trying to insert records into target table from three source tables by using function in a package and I am getting error as follows.
SQL> create or replace 2 PACKAGE casadm.sis_load_cpl_sis_reb_pgm_hist 3 IS 4 /********************************************************************** ******************
[code]....
ERROR at line 1: ORA-06550: line 1, column 7: PLS-00221: 'FN_LOAD1T_CPL_SIS_REB_PGM_HIST' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored
I have two identical tables with same structure and have some duplicates between those two. Finally I want to combine togather and in a single table and knockout the duplicates.
SQL how can we do this in a simple way? each tables has around millions records.
I am trying to update multiple columns from one table based on the results of another table So I have 3 tables as follows
HISTORYSUMM_SNAPADM_CHOICE My SQL code is loosely SELECT SUM(H.HIS1), SS.SNAP1, AC.ADM1FROMHISTORY H, SUMM_SNAP SS,ADM_CHOICE ACWHERE H.HIS2=SS.SNAP2AND SS.SNAP3=AC.ADM2GROUP BY SS.SNAP1, AC.ADM1
This works, and I am able to SUM the column as I need with the right numbers. I altered the SUMM_SNAP table and now I want this summarized column to be in the table I tried using UPDATE, but there is no FROM clause to let me do the table join/group by
UPDATE SUMM_SNAPSET SUMM_SNAP.SNAP3=SUM(H.HIS1)FROMHISTORY H, SUMM_SNAP SS,ADM_CHOICE AC WHERE H.HIS2=SS.SNAP2AND SS.SNAP3=AC.ADM2 GROUP BY SS.SNAP1, AC.ADM1
The above is obviously wrong - but just trying to show whatI was thinking What would be the best method to get the numbers from the SUM into a table?
Issue: For this sql statement client is changing the date and this sql is running fine in development and taking time in production.So I created the sql profile and push it ot prodcution so for EFFDT <= '25-APR-2010' it was running fine as plan is same as development .....but then again client changed the EFFDT <= '28-AUG-2010' is changed then plan neglected my sql profile because of hardcoded value and so it has parsed the sql again.
How we can fix this plan ? there application is like that so they are goin to pass the hardoce value like this only.....so they can not use bind variable... they are going to fire the sql from one session can we set on the session level like cursor sharing or some hints to get the development plan *for proper formating see the attached file* Statement :
How to merge multiple rows into single row (but multiple columns) efficiently.
For example
IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5 23 asdc 1 Location USA NM ABQ Four Seasons 87106 23 asdc 1 Stats 2300 91.7 8.2 85432 23 asdc 1 Audit 1996 June 17 1200 65 affc 2 Location USA TX AUS Hilton 92305 65 affc 2 Stats 5510 42.7 46 9999 65 affc 2 Audit 1996 July 172 1100
where different attributes mean different thing for each Information_type. For example for Information_Type=Location
Attribute_1 means Country Attribute_2 means State and so on.
For example for Information_Type=Stats
Attribute_1 means Population Attribute_2 means American Ethnicity percentage and so on.
I want to create a view that shows like below:
IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime 23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200 65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100
SELECT MAX(fndattdoc.LAST_UPDATE_DATE ) as LAST_UPDATE_DATE, MAX(DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL)) as COMMENTS, MAX(fnddoc.description) as REASON FROM fnd_attachment_functions fndattfn, fnd_doc_category_usages fndcatusg, fnd_documents_vl fnddoc, fnd_attached_documents fndattdoc, fnd_documents_short_text st, fnd_document_categories_tl fl, WSH_NEW_DELIVERIES DLVRY [code]....
I have three tables, I have to merge those three tables, all three tables having same conditions and filter conditions(in each table one filter condition changed), I highlighted in the red difference the filter conditions in each table, finally my result should be 7 columns like
Then I issue the following query to retrieve data based on a specific criteria (Actually I have condensed the table and data because in Production, I have large number of rows; so for simplicity I am using 8 rows with one table)
criteria : I then want to retrieve the following:
for srs = 62, all values that is greater than 10,000
Answer: select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab
where decode(srs,62,ab.amt)>10000
Works like a charm and retrives the results.
Now comes the big issue
I want to retrieve the values for srs = 62 which is greater than 10,000 and at the same time I also want to retrieve the values for srs = 65 which is less than srs = 62.
Typically I wrote the query as:
select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab where decode(srs,62,ab.amt)>10000 and decode(srs,65,ab.amt)<decode(srs,62,ab.amt)
I should atleast get one row for srs = 65 which is id # 8 but it displays blank rows or "no rows returned".
I am actually preparing a ad-hoc report for the business analyst and stuck at this step due to which I am unable to proceed any further. I have used DECODE function because of the requirement specified by Business Analyst.
I have tried the following in the office:
using EXISTS operator = no luck using INLINE VIEW = no luck
I am unable to insert the rows into table after creation of trigger because Mutating error was getting.
SQL> desc test; Name Null? Type ----------------------------------------- -------- ---------------------------- EMP_NAME VARCHAR2(10) EMP_NO VARCHAR2(10)
SQL> select * from test;
EMP_NAME EMP_NO ---------- ---------- ORACLE PC729 JAVA PC047 C PC0729
SQL> create or replace trigger trig_test 2 after insert on test 3 for each row 4 declare 5 ename varchar2(10); 6 eno varchar2(10); 7 begin 8 select emp_name,emp_no into ename,eno from test 9 where emp_no ='1'; 10 insert into test2(emp_name,emp_no) values( 11 ename,eno); 12 end; 13 /
Trigger created.
SQL> insert into test values ('PRO','1'); insert into test values ('PRO','1') * ERROR at line 1: ORA-04091: table APPS.TEST is mutating, trigger/function may not see it ORA-06512: at "APPS.TRIG_TEST", line 5 ORA-04088: error during execution of trigger 'APPS.TRIG_TEST' SQL> spool off;
I am using the SQL-Developer to access and manipulate a database. I am not very sure about the format of the database (I'm new to databases), but I had to setup the TNS-folder.
Anyway, I guess the problem is the same for any database.
I am having a table with the BOM (bill of material) positions of certain articles and I want to change the BOM quantities of some of the articles. What happens is that I can only change some of the rows. For other rows I get the message like (it is in German, so I try to translate it):
"data was commited in another/the same session already. row cannot be updated"
This error message looks like there is somebody else locked on the database and manipulating it, correct? Is that possible to see somewhere which processes/people are currently accessing to the database?
I saw that there is one process/another database, which is having the authorization to access to the database. But where can I check if this process is accessing to the database?
BTW: I used to do this process before, and it worked. I had been able to manipulate arbitrary entries on the database. I guess that the process or the person, mentioned above, hasn't been accessing to the database at that time.
I have the task that I have to determine the number of parts that need to be produced based on the number of products sold for the day (each product consists of many parts).
I am using SQL 11g Express.
The report would look something like this:
{OrderDate PartID PartDesc NumOfParts(Total for that day) 10-24-2011 2001 12" X 12" Solid Shelf 108 10-24-2011 2003 12" X 24" Solid Shelf 32 10-24-2011 3001 96" Side Panel 50
[code].......
My issue is, I can't get the equation right to produce the total number of parts. I think I need to multiply ProductPart.NumOfParts by SUM(CustOrder.Qty) Group by CustOrder.SKU.
Below I have what the calculations should look like
i work on oracle 8i with around 950 tables in my database. when i export or import it gives me the no of rows exported / imported from each table. is it possible to take a print out of the no of rows in each table through a single query .
select count(*) from each table takes a long time , since there are 950 tables.
We have two tables, TableA and TableB that contain list of accounts and balances.The requirement is to compare the balances of accounts in both the tables, and if there is a difference, then record that difference with account number in another table.
Both TableA and TableB contain more than 10 million rows.What is the best way to do this task in PL/SQL? A join on TableA and TableB to know the differences has become very slow due to large volume.
Here the SUB1 and SUB2 are "tables" and are similar in their structure. The "Main_Table" will be update dynamically and the no of rows in this table will vary.
Now my question , i need to create a view which will have all the rows from these tables ,in the current case it is something like
create or replace view sample as select * from SUB1 union all select * from SUB2
How can this be achived. I tried as shown below:
spool file_to_exe.sql select 'select * from ' || AA ||' union all ' from Main_table; spool off
i will end up in a union all "extra" , if i do like this.
A block shouldn't have rows from multiple tables... Is that true? I read in one of the OTN thread (i don't exactly remember the thread name) that a block can have data from multiple tables. If it doesn't have, what's the table directory in block signifies?
