How Many Conditions Can Will Be Applied In Where Clause
Oct 3, 2013How many conditions can will be applied in where clause and what is the maximum length for where condition?
View 6 Replies
ADVERTISEMENT
SQL & PL/SQL :: From Clause With Conditions
Apr 2, 2013You 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 .
SQL & PL/SQL :: Adding Conditions In Where Clause
Oct 20, 2010I 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
PL/SQL :: Difference - In Conditions (Join And Where Clause)
Sep 19, 2013I 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.
SQL & PL/SQL :: Conditions Of Clause IF / PLS-00382 Expression Is Of Wrong Type
Jun 4, 2010I 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)
.....
list_parameters :
id_task|||||param1|||||param2|||||param3|||||param4
--x-----------F---------Z----------NULL-------NULL
--y----------- ---------A----------K-----------L
.........
How can I use the conditions of clause IF from table "conditions"? Is it possible to do:
CURSOR cur_rules IS select * from conditions;
BEGIN
FOR c1_cur in cur_rules
[Code]....
SQL & PL/SQL :: Merge Query / All Three Tables Having Same Conditions And Filter Conditions
Apr 20, 2011SELECT
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
LAST_UPDATE_DATE, COMMENTS, REASON, CORRECTD_ACTUAL_DELIVERY_DATE, LAST_UPDATE_DATE, CORRECTD_PROMISE_DATE, LAST_UPDATE_DATE
One Query In 3 Conditions?
Jun 25, 20130 down vote favorite
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
PL/SQL :: Filtering Particular Conditions
Jun 8, 2012I 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
)
[Code]....
month sum(total_subs)
------------------------
1 21
2 13
3 89
4 6
5 7
therefore I would like exclude the total_subs for the club c2 in the months (1,2,3) and obtain the next result
month sum(total_subs)
-------------------------------
1 15
2 10
3 69
4 6
5 7
SQL & PL/SQL :: Execute Immediate Instead Of All 16 Conditions?
Aug 21, 2013I 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?
View 2 Replies View RelatedSQL & PL/SQL :: Put 0 If Row Exist But Has No Amount In Where Conditions
Jul 28, 2012I 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.
SQL & PL/SQL :: Multiple Conditions In WHERE Section
Nov 5, 2012I 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).
1. PCT_To_Goal < 60
AND (round ( (opened_period / expected_Goal ), 2 ) * 100) >= 100
2. opened_period >= 3 and number_to_date = 0
3. PCT_To_Goal < 30
AND (round ( (opened_period / expected_Goal ), 2 ) * 100 > = 50
AND round ( (opened_period / expected_Goal ), 2 ) * 100 <= 100)
Cleanup Of Applied Archived Logs?
Jan 8, 2013Is there a way to automate deletion of applied archived logs after rollforward?
I'm thinking of crontab, and a script referring to the alert log to get the archived log filename.
Setup:
OS: HP-UX B.11.31 you ia64
DB: Oracle 10g 10.2.0.4.0 (mounted)
Patch 13923804 Warnings When Applied
Jul 26, 2012I applied this patch in our development environment. After validation, I need to apply the same in production, training and publication environments. The detailed information follows :
PowerPC_POWER5 / aix 5300-12
OPatch utility used : 11.2.0.3.0
OUI version : 11.2.0.2.0
Prereq "checkConflictAgainstOHWithDetail" passed.
Composite patch 13923804 successfully applied.
OPatch Session completed with warnings.
Stderr output:
ld: 0711-224 WARNING: Duplicate symbol: p_xargc
ld: 0711-224 WARNING: Duplicate symbol: p_xargv
ld: 0711-224 WARNING: Duplicate symbol: .kghalf
ld: 0711-224 WARNING: Duplicate symbol: .kghalp
ld: 0711-224 WARNING: Duplicate symbol: .bcopy
ld: 0711-224 WARNING: Duplicate symbol: .kghgrw
ld: 0711-224 WARNING: Duplicate symbol: .kghfrf
[code]....
OPatch completed with warnings.
As they are harmless ? Whether I need to rollback ?
How To See That Opatch Is Applied Or Not On Database (OFFLINE)
Feb 23, 2013DB : 11.2.0.2 64bit
OS : RHEL 5.7 64bit
I have applied offline patch *10417948* on my database. How can see that patch applied on database/OH or not? I have applied one online patch few months ago....in that i have applied that patch on each database after installing it...using command :
opatch util enableonlinepatch -connectString SID:sys:manager: -id <opacth-id>
when i execute a command opatch lsinventory -details i got the following output :
+Interim patches (2) :
Patch 10417948 : applied on Sat Feb 23 13:42:49 IST 2013
Unique Patch ID: 14586154
Created on 18 Oct 2012, 06:52:32 hrs PST8PDT
Bugs fixed:
10417948
[code].......
For offline patch is it required to enable patch on every database?
SQL Code For Update Using Join Conditions?
Jan 8, 2011novice to SQL (Oracle 10g)
Am trying to write code for sollowing scenario:
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)
SQL & PL/SQL :: Rank Based On Multiple Conditions
Oct 4, 2013I've below table.
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]....
SQL & PL/SQL :: Adding Conditions In CASE Statement?
Oct 28, 2010I am trying to use a CASE statement
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
Forms :: Calculation Mode With Conditions
Sep 24, 2013I have 2 fields f1,f2
I define f1 a summary in calculation mode
I want to sum f1 where f2 = 5
SQL & PL/SQL :: Where Conditions That Have Dependency Don't Get Executed In Order
May 26, 2010While 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)
Can Logical Database Be Applied From Physical Standby
Jul 24, 2013I have a setup where i have one physical and logical standby from a primary database. In case of switch over between primary and physical database my logical apply gets stopped. Can a logical database be applied from a physical standby ?
View 2 Replies View RelatedSQL & PL/SQL :: How To Identify Specific Sequence Name Applied On A Table
Mar 1, 2011How to identify the specific sequence name applied on a table.
View 4 Replies View RelatedData Guard :: Archive Copied Onto DR But Not Applied?
Jul 2, 2011We have Oracle 10G dataguard configured,
DGMGRL -
Show configuration;
Shows status "SUCCESS"
Also
Archive log list
shows the proper archives in PROD as well as DR
But, when we check the Archive Applied ="YES" its shows result as "BLANK"
Forms :: Record Count - Applied And Saved?
Jan 27, 2010Why showing at the status bar "3 records applied and saved" instated of "2 records applied and saved". 2 means 1 for Header and 1 for Detail. How I change status bar.
View 7 Replies View RelatedData Guard :: Archive Not Applied On Standby
Mar 25, 2013We recently configured data guard in test machine.Archives not applied in physical standby.Where i need to start investigation?
Primary
SQL> select THREAD#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 301
[code]...
How To Find Out The List Of Patches Applied Without Using Inventory
Jul 23, 2012I have been asked this question once.
How will you find out the list of patches applied to Oracle Database Home without using commands like opatch lsinventory -detal etc...
I think registry$history is a view from where we can find out the list of patches applied.
But I think it will not include all the bug fixes,stand alone or one-off patches.It will mainly list out the CPU patches applied(correct me if I am wrong).
SQL & PL/SQL :: Deleting Multiple Records Based On Conditions
Jun 28, 2010I 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]...
SQL & PL/SQL :: Multiple Record Split On Date Conditions?
May 12, 2012Scenario 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
Server Utilities :: Using NULL-IF Statement For Two Conditions?
Mar 6, 2006give two conditions under NULLIF statement when we are using it in a sql script to load data into a table.
View 6 Replies View RelatedCreate Table Which Contains Details Of What Specific Seeker Applied
Oct 24, 2012I need to create a table which contains the details of what companies a specific seeker applied to when he logs in into his account.... Also, when an employer logs in, he needs to get the list of seekers who applied to him...give database schema required for such a situation asap.Say, the user has a primary key UID, and each employer has a primary key EID..
View 1 Replies View RelatedData Guard :: Archived Logs Not Applied On Standby But Looks Ok
Jan 11, 2012I got a database on dataguard and my primary (db1) is shipping files to my standby (db2) with no problems.However, when I query:
select sequence#, status, applied from v$archived_log;
I see this:
SEQUENCE# S APP
---------- - ---
4 A YES
5 A YES
6 A YES
7 A YES
8 A YES
9 A YES
10 A YES
11 A YES
12 A YES
13 A YES
14 A YES
[code]....
So I did an alter system switch logfile on db1 then looked again and I can see new archived logs being applied.I thought all archived logs had to be applied on the standby since this is the very foundation of the standby database.Am I going to run in trouble later if I have a failover (unsynchronized database)