SQL & PL/SQL :: Merge Query / All Three Tables Having Same Conditions And Filter Conditions
Apr 20, 2011
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
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
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)
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) .....
In my Project, there are many queries have join conditions between Number and Vrahchar2. Will it give any wrong results or Invalid Number exception even the varchar2 column always contains the number data?
Example: Select * from Table_t1 t1, table_t2 t2 where t1.num_col = t2.var_col -- Here var_col always hold only numaric data.
I have a database block that refreshes using a timer. In short, I list all open work pieces for a certain garage, displaying route, run, on_time, of_time, location grouped by garage, on_time, off_time
I need to highlight every garage with a different color. I am highlighting the on_time every time the garage changes.
The problem is, every time I move down the records, instead of keeping the previous garage color, and highlight the new garage on_time with a new color, the whole on_time column will be set to the current garage color.
I need to break down the block by color so the user can distinguish between the different garages, and the start and end time. Here is my code, I am putting it in when_new_record_instance of the work_block:
IF :work_bLock.current_garage = 9006 THEN Set_Item_Property('work_block.on_time', BACKGROUND_COLOR, 'r191g223b191'); ELSIF :work_bLock.current_garage = 9002 THEN Set_Item_Property('work_block.on_time', BACKGROUND_COLOR, 'r255g228b196'); ELSE Set_Item_Property('work_block.on_time', BACKGROUND_COLOR, 'r255g250b250'); END IF;
I need the block to be 3 or more different colors depending on the garage.
Actually I have one query which contains two parameters from_Date and To_date.
if user pass the values for the parameter like 1-dec-2010 to 30-dec-2010 query will works fine and it fetches the values according to the query selected. but now I am going to add one more parameter, the value is 01-apr-2010 now my query should run for 01-apr-2010 to 30-dec-2010.
Here my question is how should I make this scenario with only one query and at the same time the query should work for to scenarios.
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 get no rows - why? Obviously the predicate 1=2. Except, equally obviously, I don't have that in the query. On checking the objects views I see that ccs.payment method is indeed a table within that schema not a view or anything like that and when checking through another user I can see it has data within.
The "problem" user can see the table exists, just cant ever query rows back. Another user can see the table exists and can get data back.
Like I say, likely a priv or similar issue but I'm damned if I've seen this before, I'd not expect a priv issue to cause a 1=2 filter appear in a plan and its not a view that I can tell.
I'm still digging at my end and this may be irrelevant but when I browse to the table in Sql Developer I can see the columns tab ok, on clicking the "data" tab it throws "ORA-00904: "ORA_ROWSCN": invalid identifier" out. Maybe nothing, maybe related.
Google results suggest that error in SQL Dev is due to implementations of policies/VPD presence on the table.
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?
