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

LAST_UPDATE_DATE, COMMENTS, REASON, CORRECTD_ACTUAL_DELIVERY_DATE, LAST_UPDATE_DATE, CORRECTD_PROMISE_DATE, LAST_UPDATE_DATE

View 2 Replies


ADVERTISEMENT

One Query In 3 Conditions?

Jun 25, 2013

0 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

View 1 Replies View Related

SQL & PL/SQL :: From Clause With Conditions

Apr 2, 2013

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 .

View 1 Replies View Related

PL/SQL :: Filtering Particular Conditions

Jun 8, 2012

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
)

[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

View 10 Replies View Related

SQL & PL/SQL :: Execute Immediate Instead Of All 16 Conditions?

Aug 21, 2013

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?

View 2 Replies View Related

SQL & PL/SQL :: Put 0 If Row Exist But Has No Amount In Where Conditions

Jul 28, 2012

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.

View 8 Replies View Related

SQL & PL/SQL :: Multiple Conditions In WHERE Section

Nov 5, 2012

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).

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)

View 2 Replies View Related

SQL & PL/SQL :: Adding Conditions In Where Clause

Oct 20, 2010

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

View 5 Replies View Related

How Many Conditions Can Will Be Applied In Where Clause

Oct 3, 2013

How many conditions can will be applied in where clause and what is the maximum length for where condition?

View 6 Replies View Related

SQL Code For Update Using Join Conditions?

Jan 8, 2011

novice 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)

View 1 Replies View Related

SQL & PL/SQL :: Rank Based On Multiple Conditions

Oct 4, 2013

I'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]....

View 3 Replies View Related

SQL & PL/SQL :: Adding Conditions In CASE Statement?

Oct 28, 2010

I 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

View 10 Replies View Related

Forms :: Calculation Mode With Conditions

Sep 24, 2013

I have 2 fields f1,f2

I define f1 a summary in calculation mode

I want to sum f1 where f2 = 5

View 3 Replies View Related

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)

View 10 Replies View Related

PL/SQL :: Difference - In Conditions (Join And Where Clause)

Sep 19, 2013

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.

View 23 Replies View Related

SQL & PL/SQL :: Deleting Multiple Records Based On Conditions

Jun 28, 2010

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]...

View 8 Replies View Related

SQL & PL/SQL :: Multiple Record Split On Date Conditions?

May 12, 2012

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

View 5 Replies View Related

Server Utilities :: Using NULL-IF Statement For Two Conditions?

Mar 6, 2006

give two conditions under NULLIF statement when we are using it in a sql script to load data into a table.

View 6 Replies View Related

Performance Tuning :: How To Keep RANGE SCAN Even After Adding New Conditions

Aug 29, 2011

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

[code]...

View 4 Replies View Related

SQL & PL/SQL :: Conditions Of Clause IF / PLS-00382 Expression Is Of Wrong Type

Jun 4, 2010

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)
.....

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]....

View 12 Replies View Related

Implicit Data Conversion / Join Conditions Between Number And Vrahchar2

Jun 17, 2013

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.

View 1 Replies View Related

Forms :: Change Records Visual Attribute Depending On Certain Conditions

Jul 12, 2011

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.

View 2 Replies View Related

Reports & Discoverer :: Single Data Group Should Work For Multiple Type Of Conditions?

Jan 8, 2011

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.

View 6 Replies View Related

SQL & PL/SQL :: Merge Columns Of 2 Tables?

Apr 14, 2011

i wanted the query for the followin example

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

View 5 Replies View Related

SQL & PL/SQL :: Filter In Xplan Not In Query

Feb 9, 2011

This is probably a priv/similar issue but I've not seen it before.

select * from ccs.paymentmethod;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 2424632210
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19791 | 2589K| 39 |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL| PAYMENTMETHOD | 19791 | 2589K| 39 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(1=2)

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.

View 2 Replies View Related

SQL & PL/SQL :: Merge Two Tables Into One And Knock Out Duplicate Records?

Jul 12, 2006

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.

View 4 Replies View Related

PL/SQL :: Merge / Update With Multiple Tables And Columns

Sep 6, 2013

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?

View 5 Replies View Related

SQL & PL/SQL :: Query Based On Date Filter Is Not Working

Aug 14, 2011

is the definition of my table :

CREATE TABLE DATEFETC
(
ID VARCHAR2(10 BYTE),
DT DATE
)

And these are the data that are available,(select * from DATEFETC)

IDDT

00108-09-2011
00208-10-2011
00308-11-2011

That's fine.
Now i am executing this query ,but this is returning no rows.Why ?

select * from datefetc where dt between to_date('08-08-2011','mm-dd-yyyy') and to_date('08-12-2011','mm-dd-yyyy')

View 1 Replies View Related

SQL & PL/SQL :: MERGE INTO - Unable To Get Stable Set Of Rows In Source Tables

Aug 30, 2010

When i am running the below merge statement it gives the error as below

MERGE INTO non_tab tt
USING (SELECT non_tab.acct,
non_tab.mis,
non_tab.n_gaap_skey,
non_tab.run,
non_tab.mit_ul AS t0,
CASE
WHEN (( CASE
[code]...

ORA-30926: unable to get a stable set of rows in the source tables.

View 5 Replies View Related

Query To Exclude All Rows Based On Single Row Filter?

Jun 10, 2011

I'm using Oracle 10g.

Question: How can I write query to return just ID only if all the codes for that ID end in 6.

If I use
SQLselect id from table_a where code like '%6'
then I also get ID=1, which I don't want.

TABLE_A
ID code
=======
1 100
1 106
2 206
3 306
3 336
4 400

Desired Result
ID
==
2
3

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved