SQL & PL/SQL :: Where Conditions That Have Dependency Don't Get Executed In Order
May 26, 2010
While there's numerous QAs about inserting an image into the DB using the Data Block, how does one remove an image? Obviously there's the "UPDATE ... SET X = EMPTY_BLOB();", however, that kills the Data Block/Form's flow, and it doesn't update.
I've tried a few things:
* :CONTROL.IMG1 := NULL; (bad bind variable)
* :CONTROL.IMG1 := EMPTY_BLOB(); (some other error, probably as above)
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
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.
the literature equates dimension hierarchies with fuctional dependencies between the levels. I like to tst the strength of this assumption with the implementation of 'CREATE DIMENSION' which allows you to create roll-up hierarchies.
My question to put it simply is this: Given:
CREATE DIMENSION location_dim LEVEL location IS (location.loc_id) LEVEL city IS (location.city) LEVEL state IS (location.state) HIERARCHY geog_rollup ( location CHILD OF city CHILD OF state CHILD )
Can I insert the following rows into the dimension: loc_id, city, state 1, Epping, NSW 2, Epping, VIC
Please note that the two Eppings are different cities.
Given the roll-up hierarchy City -> State, will it require that for every city there can be only one state in which case the FD between City and State cannot hold. Or, is it that the roll-up hierarchy defined here has nothing to do with FD.
The second part of the question is if the answer to the above question is that the roll-up is not the same as FD, then is the ATTRIBUTE clause meant to define the n:1 (functional dependency) instead?
suggest on the dependenecy between the database service and the database instance?
srvctl start instance -i db1 will this start service as well for which the above instance is either preferred or available instance? will it start the listener as well?
srvctl stop instance -i db1 will this stop service as well for which the above instance is either preferred or available instance? will it stop the listener as well?
srvctl start service -d db -s dbserve will this start service as well for which the above instance is either preferred or available instance?
srvctl stop servive -d db -s dbserve will this stop service as well for which the above instance is either preferred or available instance?
srvctl start database -d db will it start instance, listener and service as well?
srvctl stop database -d db will it stop instance, listener and service as well?
It's been a while since I worked with SQL Plus . I am using Oracle 11g. We are working on a legacy data migration project. I have a table of records with circular dependency records. i am trying to identify the records. I have the foll. columns- Product,Source,target. I want o identify the records which form a loop. For e.g.
Source Target A B B C C D D A
Last record forms a loop-I need to identify these records. My query is below-
SELECT DISTINCT SOURCE,TARGET FROM RULESELIB WHERE CONNECT_BY_ISCYCLE=1 CONNECT BY NOCYCLE SOURCE=PRIOR TARGET;
I ran this query on 2 tables- one with 75000 records and the other with 25000 records. It works fine on the table with 75000 records completes within a minute but it does not complete on the other table. I can't seem to be able to figure out the issue with the query or is there something about the data that is causing this query to loop infinitely?
Name Null Type --------------------------- -------- ------------- RPTNO NOT NULL NUMBER RPTDATE NOT NULL DATE RPTD_BY NOT NULL VARCHAR2(25) PRODUCT_ID NOT NULL NUMBER
describe rptbody
Name Null Type ------------- -------- ------------- RPTNO NOT NULL NUMBER LINENO NOT NULL NUMBER COMMENTS VARCHAR2(240) UPD_DATE DATE
The fact is that we store some header in RPTHEAD and store real data in RPTBODY, the question is that if I use below SQL to query all data for a 'PRODUCT_ID'.
SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE FROM RPTBODY t0 , RPTHEAD rpthead WHERE ( t0.RPTNO = rpthead.RPTNO AND t0.UPD_DATE>=to_date('1970/01/01 00:00:00','YYYY/MM/DD hh24:mi:ss') AND rpthead.PRODUCT_ID IN ('4647') )
I do not want to have 'ORDER by' clause since data set is too large, the sorting takes long time, is there any way to get the result rows in the order sorted by RPTNO? We have the index for RPTNO on RPTBODY.
I have one table in database that contains 3 foreign keys to another tables(this three tables name are: manager,worker and employee). in each row only one foreign key is filled.I need to write one query that with attention which column of fk is filled in where clause specified condition is performed. I write simple query in jpa but doesn't work properly
select b from allEmployees b where b.manager.name= :name OR b.worker.name = :name OR b.employee.name= :name
You think i can do a from clause with conditions ??
The reason is that i need to retrieve fields from different schemas depending on a column in a common table
let s say the column CRITERIA_COL is in table Common If COMMON.CRITERIA_COL has value 1 then the select query should fetch results from the schema : SCHEMA1.MY_TABLE If COMMON.CRITERIA_COL has value 2 then the select query should fetch results from the schema : SCHEMA2.MY_TABLE If COMMON.CRITERIA_COL has value 3 then the select query should fetch results from the schema : SCHEMA3.MY_TABLE
Something like this:
Select my_Col1, my_Col2 from (case COMMON.CRITERIA when '1' then SCHEMA1.MY_TABLE when '2' then SCHEMA2.MY_TABLE when '3' then SCHEMA3.MY_TABLE )
but that is not working .By the way my query is not just that, it s a more complicated query, that s just the portion I am having trouble with .
I would like resolve an issue, I would like to know if is there any posibility to filter particular conditions using sql, the example that Im going to describe below shows these conditions:
create table t1 ( month number, club char(2), total_subs number )
I created a procedure with four in parameters and 1 out parameter is there in this i want to check if any parameters is null or any two parameters are null or any three parameters are null...like this i checked(16 conditions) for all combinations i put if conditions but can i use execute immediate instead of all 16 conditions?
I need to put amount '0' if the row exist but has no amount in my "where " conditions. the orginal commad is :
select t.aaa, count (t.bbb), sum (t.ccc) from nrb t where t.vvv IN ('3','4','5','6','D','E','F') and t.ddd like '50%' and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD') and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD') group by t.aaa order by t.aaa
and the result is : "result" tab in excel atached file.i need this result: "result 2" tab in excel atached file.
I am new to the forum as well as SQL programing and I need to have the following criteria writen in my WHERE so all three of these criterias are included in the same report (perhaps, each will have its own section).
I am trying to run an SQL query which refers/joins around 10 tables.In my case I would want to add an if-else or case condition in the WHERE clause of select query
For example
select a.column1, b.column1, c.column1, d.column1 from a, b, c, d WHERE a.column2 = b.column2 AND (if a.column3 = 10 then ( I want 2 conditions to be AND b.column2 = c.column2) added else after WHERE clause as AND...) AND b.column2 = d.column2 ) AND d.column2 = a.column2
Have 3 tables table1 (campaignid,promoflag) table2 (campaignid,projectid,campaigndesc) table3 (projectid,promoflag,projectstart,projectend)
I am to update table 1 promoflag with value from promoflag in table3
Update table1 set promoflag = table3.promoflag
I would like to make sure only appropriate record is updated therefore want to use where clause condition but the primary key for table1 and table3 are different, the only link can be found on table2.
I want to use condition where table1.campaignid=table2.campaignid and table2.projectid=table1.projectid
Have used the following without success:
Scenario 1
Update table1 SET promoflag = table3.promoflag FROM table1 inner join table2 on table1.campaignid = table2.camapaignid inner join table3 on table2.projectid = table3.projectID;
Error at line 1 ORA-00933: SQL command not properly ended
Scenario 2 with real table/column names
Update UA_CAMPAIGNEXTATTR SET CFPROMOTABLE = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUD_CFPROMOTABLE FROM UA_CAMPAIGNEXTATTR,UA_CAMPAIGN,LMUK_PROJECT_AUDIENCE_GRID WHERE UA_CAMPAIGNEXTATTR.CAMPAIGNID = UA_CAMPAIGN.CAMPAIGNID AND UA_CAMPAIGN.PROJECTID = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUDIENCE_ID; Error at line 2 ORA-00933: SQL command not properly ended
It appears to get block with the 'FROM' statement (was underlined in red)
with tmp_tbl as (select 'H1' as hh_id, 'C1' as cust_id, 2 as f_rnk, 'F' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100203','YYYYMMDD') first_dt from dual union select 'H1' as hh_id, 'C2' as cust_id, 1 as f_rnk, 'M' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100303','YYYYMMDD') first_dt from dual union select 'H1' as hh_id, 'C3' as cust_id, cast(null as number) as f_rnk, 'U' as gender, to_date('20130103','YYYYMMDD') as purch_dt, [code].....
Now i need to rank each cust_id in each hh_id based on below conditions.
1) If atleaset one cust_id in hh_id has f_rnk then gender 'F' with highest f_rnk (more then one F with same f_rnk then the one with oldest first_dt), if no 'F' then gender 'U' with highest f_rnk ((more then one F with same f_rnk then the one with oldest first_dt)), if no 'F' and 'U' then consider 'M' (more then one M with same f_rnk then the one with oldest first_dt).
2) If the above is not met (no cust_id in hh_id has f_rnk populated) then i've to rank based on purch_dt. Gender 'F' with recent purch_dt (if more than one F in household with same purch_dt then the one with oldest first_dt), if no 'F' then gender 'U' with recent purch_dt (if more than one U in household with same purch_dt then one with oldest first_dt), if no 'F' and 'U' then consider 'M' (more than one M in household with same purch_dt then the one with oldest first_dt).
3) If the above criteria is also not met, then rank based on gender_cd. Gender 'F' will have first preference then 'U' and then 'M'.
My output :
HH_ID CUST_ID F_RNK GENDER PURCH_DT FIRST_DT F_RNK_RANK PURCH_RANK GENDER_ONLY_RANK ----- ------- ---------- ------ ----------- ----------- ------------ ------------ ----------------- H1 C1 2 F 1/2/2013 2/3/2010 1 H1 C2 1 M 1/2/2013 3/3/2010 2 H1 C3 U 1/3/2013 4/3/2010 3 H2 C4 F 4/3/2013 10/2/2009 2 H2 C5 M 5/5/2013 8/8/2010 1 H3 C6 F 5/6/2008 1 H3 C6 M 7/8/2010 2
I've tried below query with one condition, but it's giving f_rnk_rank for all records. How can i include multiple conditions in the rank function.
with tmp_tbl as (select 'H1' as hh_id, 'C1' as cust_id, 2 as f_rnk, 'F' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100203','YYYYMMDD') first_dt from dual union select 'H1' as hh_id, 'C2' as cust_id, 1 as f_rnk, 'M' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100303','YYYYMMDD') first_dt from dual union select 'H1' as hh_id, 'C3' as cust_id, cast(null as number) as f_rnk, 'U' as gender, to_date('20130103','YYYYMMDD') as purch_dt, s hh_id, 'C5' as cust_id, [code]....
CASE WHEN (EP.ORDER_NUMBER IS NOT NULL AND ATD.IS_DISCONNECT_CREDIT = 1 ) THEN ......... ...... END
In my case when I club two conditions after WHEN in case statement , everytime the first condtion is satisfied (i.e EP.ORDER_NUMBER IS NOT NULL) it is entering into the loop.
Irrespective of using AND or OR after the first condition I am getting the same results.
I want to enter the CASE statement only when both the conditions (1st and 2nd )are satisfied
I need to be clear about what exactly difference when we put any condition in INNER JOIN and WHERE Clause. I tried both way and found same results. Even in Statistics Plan not much differences.
1. Here I am using location filter in Inner join condition -
"SELECT I.*, Gl * From Sc1.Item I Inner Join Sc1.Part P On P.Part_Id = I.Part_Id Inner Join Sc1.Location Gl On Gl.Location_Id = I.Location_Id And Gl.Location_Id In ( 1767, 1747,202,1625) Inner Join Sc1.Condition C On C.Condtion_Id = Gl.Condition_Id Where I.Inactive_Ind = 0 And I.Condition_Id != 325
2. Here I am using location filter in Where clause
SELECT I.*, Gl * From Sc1.Item I Inner Join Sc1.Part P On P.Part_Id = I.Part_Id Inner Join Sc1.Location Gl On Gl.Location_Id = I.Location_Id Inner Join Sc1.Condition C On C.Condtion_Id = Gl.Condition_Id Where I.Inactive_Ind = 0 and I.LOCATION_ID in ( 1767, 1747,202,1625) And I.Condition_Id != 325.
I have a requirement where i need to retain latest 3 records based on creation date for each customer_id and delete the older records. The customer_ id or contract_number data in the test table are not unique.
Sample Table Script:
CREATE TABLE TEST ( CUSTOMER_ID VARCHAR2(120 BYTE) NOT NULL, CONTRACT_NUMBER VARCHAR2(120 BYTE) NOT NULL, CREATION_DATE DATE NOT NULL ); [code]...
Scenario 1 Query should check for priority record(25), if the start_date and end_date of that priority record is the max in that group, records will not have any split.output will be the same.
DC Store St Date End date Priority 955 3 1/1/2010 12/31/9999 25 966 3 4/5/2011 10/10/2011 50 977 3 10/12/2011 12/12/2012 100
output
DC store St Date End date Priority Rank 955 3 1/1/2010 12/31/9999 25 1 966 3 4/5/2011 10/10/2011 50 2 977 3 10/12/2011 12/12/2012 100 3
Scenario 2 If priority record is not covering the max range, then split the records as shown below,
1. during the time period 1/1/2011 & 4/30/2011 there were no other DC for that store so rank would be 1
2. the next range would be 5/1/2011 to 6/29/2011 we have 2 records in service so the record with low priortiy would be ranked 1 and second priority would be ranked 2
3. similarly, for 6/30/2011 to 10/1/2011 we have 3 records in service and it will be ranked accordingly on the priority.
DC Store St Date End date Priority 966 3 6/30/2011 10/1/2011 25 955 3 5/1/2011 11/30/2011 50 977 3 1/1/2011 12/31/2011 100
output
DC store St Date End date Priority Rank 977 3 1/1/2011 4/30/2011 100 1 955 3 5/1/2011 6/29/2011 50 1 977 3 5/1/2011 6/29/2011 100 2
[code]....
Scenario 3 This works similar to scenario 2
DC Store St Date End date Priority 966 3 2/1/2011 12/31/2011 25 955 3 1/1/2011 12/31/2012 50 977 3 5/1/2011 06/31/2011 100
output
DC store St Date End date Priority Rank 955 3 1/1/2011 1/31/2011 50 1 966 3 2/1/2011 12/31/2011 25 1 955 3 2/1/2011 12/31/2011 50 2 977 3 5/1/2011 6/30/2011 100 3 955 3 1/1/2012 12/31/2012 50 1
Note: Number of records in the input can vary and ther can be duplicates in the date interval
I am adding condition in color to my existing query but my query taking too long time after that. This condition calculates last 3 month end dates from given COB (20110516).
SUMMARY is my summarized table and COB is my reference table
Without adding condition in query it gives INDEX (RANGE SCAN)--PK_SUMMARY(INDEX(UNIQUE)) After adding condition in query it gives INDEX(FAST FULL SCAN)--PK_SUMMARY(INDEX(UNIQUE))
how can i keep RANGE SCAN even after adding my new conditions?
SELECT distinct COB.COB_ID_C, SUMMARY.SUMM_COB_ID_C FROM
I putted in a table "conditions" some rules (if conditions) and I want to read and execute those conditions in another table "list_parameters" in pl/sql procedure.
conditions : ID||||||||||||||RULE 1-----------(param1 = F) 2-----------(param2 is null) .....