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


ADVERTISEMENT

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 View Related

SQL & PL/SQL :: Using Dbms_sql.execute To Build / Execute Dynamic Sql?

Oct 23, 2013

I'm working with old code that uses dbms_sql.execute to build/execute dynamic sql. In our case, the user can select varying columns(I think up to 20) with different where conditions as needed.

After building the sql, here's an example

WITH ph AS
(SELECT ph.* FROM po_header ph WHERE 1 = 2),
pf AS
(SELECT DISTINCT pf.order_id, pf.fund
FROM po_fau pf, ph
WHERE 1 = 1
AND ph.order_id = pf.order_id

[code]....

Where table records for

po_header = ~567746
po_fau = ~2153570

and PK "order_id" is a NUMBER(10) not null and a snippet of the code looks like

nDDL_Cursor := dbms_sql.open_cursor;
dbms_sql.parse(nDDL_Cursor, sSQLStr, 2);
FOR x IN 1 .. nCols LOOP
sCols(x) := '';
dbms_sql.define_column(nDDL_Cursor, x, sCols(x), 100);
END LOOP;
nError := dbms_sql.execute(nDDL_cursor);

why when the "execute" statement is fired off the elapsed time takes ~4.5 seconds but If I change "1 = 1" above to "1 = 2" it takes ~.2 seconds. If I run the above query interactively it takes ~.2 seconds. Shouldn't the above query when joining

ph.order_id = pf.order_id

return zero rows back instantly or does the "dbms_sql_execute" do some other type of parsing internally that takes cpu time.

View 14 Replies View Related

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 :: 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

Execute Dynamic SQL Using Both Execute Immediate And Ref Cursor

Jan 8, 2009

We can execute dynamic sql using both execute immediate and ref cursor..But what is the difference between the two and performance-wise which is better?

View 5 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

Execute Immediate In Trigger

Apr 17, 2013

I am trying to use execute immediate in a trigger. See the below example

create table test_tbl(col1 number,col2 varchar2(10), col3 date);
create table test_tbl_log as select * from test_tbl where 1 = 2;

create or replace trigger test_trig
after insert or update or delete on test_tbl
for each row
declare
v_str varchar2(4000);
[code].......

insert into test_tbl values(3,'test',sysdate);

I got the Error : -1008->ORA-01008: not all variables bound

In the execute immediate statement if I change the v_using_str variable to :new.col1,:new.col2,:new.col3 trigger is running fine. But here I have to create trigger on all the tables in my schema and I dont want to list all the column names for each table manually and I am using a for loop to build the v_using_str string in my original code.

View 2 Replies View Related

Execute Sql File From Pl / Sql?

Aug 18, 2010

Need to execute @/sql file after checking an output of below sql.

SELECT COUNT(*) FROM all_directories WHERE DIRECTORY_NAME='RMANDUMP';

If directory exist then oonly we require to run sql file.

If there a way we can execute sql files depending on sql output.

View 4 Replies View Related

SQL & PL/SQL :: Execute Immediate With DBLink

Feb 23, 2011

I need to write several variations of this statement below to query a remote database, but am having trouble with the syntax.

execute immediate 'select count(*) from ' || v_tablename || '@dblink ' || into v_rowcount;
PLS-00103: Encountered the symbol "INTO"....

What's the correct way to format this kind of dynamic query?

View 4 Replies View Related

SQL & PL/SQL :: How Many Times Do The Inner Execute

Oct 2, 2010

I found this query in one of my stored procedures that updates a key for a value in a data table by reading the information from a master table.

The data table is: ITEM_INVENTORY
The master table is: MASTER_SOURCE_SYSTEM

UPDATE ITEM_INVENTORY I
SET I.SOURCE_SYSTEM_ID =
(SELECT NVL(M.SRC_SYS_ID,-100)
FROM MASTER_SOURCE_SYSTEM M
WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )
WHERE ORG_CODE = 'TNXC'
AND EXISTS (SELECT 1 FROM MASTER_SOURCE_SYSTEM M
WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )

The situation here is that:

1. There are about 15000 rows that match ORG_CODE = 'TNXC'.
2. The SOURCE_SYSTEM_CODE is same for all the 15000 rows and there is a matching entry for it in the MASTER_SOURCE_SYSTEM table.

My question is: Do both the inner select statements execute 15000 times?

The statement executes within a second and updates 15000 rows. How is this made possible?

View 10 Replies View Related

SQL & PL/SQL :: FORALL With Execute IMMEDIATe

Jan 11, 2006

I am trying the following

FORALL l_loop_cntr IN 1..l_count
EXECUTE IMMEDIATE ' INSERT INTO ' ||c_table || ' VALUES l_NE_BILL_REPORTS_table (:1) ' USING l_loop_cntr;

my table name is dynamic.

but it gives the error Compilation errors for PROCEDURE PL_OWNER.TEST_BULK

Error: PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL
Line: 27
Text: EXECUTE IMMEDIATE ' INSERT INTO ' ||c_table || ' VALUES l_NE_BILL_REPORTS_table (:1) ' USING l_loop_cntr;

View 4 Replies View Related







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