Now I want to change the deptno from 10 to 20 and 20 to 10 in dept table . So the ouput will be like
deptno| dname|dseq| 20 | CS | 1 10 | SC | 2
when i am deleting that row I am getting error "ORA-02292: integrity constraint (ROP_FK) violated - child record found".IF i am trying to update i am getting unique key contractin failure.
(Even I cannot update the rest of the column values in dept table as it is also having foreign key dependency) i need to put a sql script in production so that it will interchange.
I have two primary key values based on that I would need to query at least 25+ tables(each of these tables might have both primary key columns or either one to query) to extract data for my 150 columns and load it onto my target table.
In my table ,data type of one among 10 columns is defined as varchar2(10).I need to check that column should accept only numeric value(0 to 99) or alphabetic value(a to z or A to Z) .It should not accept Alpha-numeric values.I tried like this
select c3 from demotab where to_number(c3) not between ascii('a') and ascii('z') ;
but I got error like 'Invalid Number'.how to implement this thro sql query.
I have table called test script for table is given below
create table TEST ( col1 Number );
[Code]...
For these values Query is not returning values(3 and 4). So i want generic query to get this result. I am working on it but not able to generate proper query.
I have a form in which the user selects values above from a LOV and then selects a run query button which then uses the values selected in the LOV to refine the query and output the appropriate values.
What i have done in the pre-query trigger is: (the block i am querying is called cars where the block i am selecting values from the LOV is called selection)
:cars.make := :selection.make; (repeated for other fields)
this worked fine untill i introduced a from and to date in my form in which i passed the data through to the query like this:
declare v_dates_between varchar2(1000); begin v_dates_between := 'reg_date between ''' || :selection.from_date || ''' and ''' || :selection.to_date; Set_Block_Property( 'cars', DEFAULT_WHERE, v_dates_between ) ;
Now i have added the above bit of code the from and to date work fine, but when a car make is selected it still bring back every car make and not just the one selected.
is it possibly because i am setting the where clause in the block property? perhaps i need to now incoperate my other selections into this where clause? are the two blocks exclusive to eachother? and if i put the whole code into the block property I assume i will get problems. E.G. the user leaves a selection criteria blank (if they want to query all car models they wouldnt select one from the list) i assume the query would only return back values which contain no car model which would be 0 records.
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production version
I am having the data in following table -
drop table stud_fact; create table stud_fact(stud_NM, LVL_CD,ST_DT_DIM_KEY,OVRNK) as select 'ABG Sundal','H','20110630','175' from dual union all select
I have a table and data as below.I need to select all the records if value have both 'M' and 'D'.If there is only 'M' or 'D' then select should not pull any records.
WITH data as ( Select '1' id, 'M' value from dual union all Select '1' id, 'M' value from dual union all Select '1' id, 'D' value from dual union all Select '1' id, 'D' value from dual ) select value from data group by value
I tried below query but it is not working.
WITH data as ( Select '1' id, 'M' value from dual union all Select '1' id, 'M' value from dual union all Select '1' id, 'D' value from dual union all Select '1' id, 'D' value from dual ) select * from data group by value having sum(count(distinct(value))) > 1
I have a requirement like getting list of values from one table and inserting them into another table.I have tried with sub querying but didn't worked out because the select query is returning multiple values.
how to proceed further and the ways how can I write this requirement.
I have a table with multiple rows for the KEY attribute(its not a primary key) and a Rank for each row.
I want a query which fetches one row per KEY attribute.The row with lesser Rank should be considered. But in-case if the value is null for any column the value for next Rank should be considered.
WITH TMP_TBL AS ( SELECT * FROM ( SELECT 'A' DUN,'1' RNK,'A21' col1,NULL col2,'A41' col3,NULL col4 FROM dual UNION ALL SELECT 'A','2','A122','A23',NULL,NULL FROM dual UNION ALL SELECT 'A','3','A32','A33',NULL,'A35' FROM dual [code].......
DUN is the KEY attribute . RNK is the Rank for each Row. COL1... COL4 are data attributes
I want this to be done with SQL only. So I tried various ways but none were successful.Finally I created a Multi Row function row_nvl and it worked.
SELECT DUN, row_nvl(rownvl_param_type(RNK,col1)), row_nvl(rownvl_param_type(RNK,col2)), row_nvl(rownvl_param_type(RNK,col3)), row_nvl(rownvl_param_type(RNK,col4)) FROM TMP_TBL GROUP BY DUN
But I don't think my manager will allow me to deploy a Multi Row function .
I dont want to print the repeated value(NAME) of C1 multiple times as below.
C1C2C3C4 NAMEJOHN10ABC SMITH30DEF ROBERT60XYZ
I could do it using the below query using union with the rownum.
select * from ( select rownum rn, c1,c2,c3,c4 from table_new ) where rn =1 union select * from ( select rownum rn, decode(c1,null,null),c2,c3,c4 from table_new ) where rn between 2 and 3
Is there any other way of displaying using a single sql query.
When I execute the query, it returns the data (approx - 40,000 rows) in 1 min.But when I try to insert this data into another table (or create a table of this data) it takes me about 2 hours.
Tried using Materialized view, its again the same the refresh takes 2 hours.Basically here, what I am trying to do is the data from the above query is used to update the values in another table.What ever the procedure I am trying it takes 2 hours.
I need to get multiple code values and put it into a variable which later need to pass into the where clause of an sql. But i am not getting any results even i pass those values in the variable of an where clause: below is my Procedure:
declare TYPE crMain_record is RECORD ( v_code dummy.v_code%type, n_no dummy.n_no%type,
[Code].....
END;"lv_character" is going to hold the multiple code values which i need to pass into whare clause of the above SQL: the totlal number of these mulitipe codes can be more then 50..
And lv_character values are commung from a setup table lv_character varchar2(32767):= '('||''''||'COMMIS'||''''||' , '||''''||'AGY BUILDING BENS'||''''||')'; --And lv_character values are commung from a setup table.where "lv_character" holdes multipe code values... And lv_character values are commung from a setup table and upper(d.v_code)in lv_characterif the
I don't understand why the custid is the same for each customer, and why it's selecting every customer and not just those with more than 150 gallons ordered.
For this one use the oil tables that you set up and use a subquery. Select the minimum average fall use from the house table. Then show all customers whose number of gallons delivered times two is greater than the minimum.
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE i_e NUMBER(10); BEGIN FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL) LOOP FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
I am searching the simplest way for ad hoc MINUS.I do:
SELECT * FROM uam_rss_user_XXXXXXX WHERE host_name IN ('XXX0349', 'XXX0362', 'XXX0363', 'XXX0343', 'XXX0342', 'XXX0499', [code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
I created a package with some types, and every was compiling fine. However, when I ran the new function, I got an error: ORA-21700: object does not exist or is marked for delete
After a little research, I realized that the types would have to be declared outside the package. As soon as I did that, I suddenly started getting the "not enough values" errors on all my types. I compared the number of columns being returned, and the number of columns in the type, and they match.Here is my type code:
CREATE OR REPLACE TYPE TSA_CUSTOM.Lost_Plan as object ( LP_Key number, -- The member key of the plan that is going away LP_Type varchar2(20), LP_Dept varchar2(12), LP_SubDept varchar2(12), LP_Class varchar2(12), LP_VendorName varchar2(50) [code]...
I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a
select 'date_on' to add a field called date on populated by 'date_on' (the name of the column in the first query)
however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.
