SQL & PL/SQL :: Alternative For Not In

Nov 8, 2011

I've got to create 2 tables...

Table 1 include members of a certain club with specific criteria
Table 2 needs to include everyone else on the main database, not in the club, but with the same criteria.

So, ultimate goal is to have two tables, similar criteria, T1 = club members and T2 = non-club members

Table 1 ran just fine.

Table 2 is running and running and running. I'm looking for a more efficient way then the syntax below:

create table NON_club_Members nologging as
select distinct hcp.ID, count (distinct hcp.profile_key)
from mrtcustomer.table_1 hcp, Club_Members cm
where hcp.child_birth_dt between '31-OCT-2000' and '30-OCT-2011'
and hcp.ID not in
(select cm.id from Club_Members)
group by hcp.id

I've also tried <> and that also ran and ran and ran.

View 1 Replies


ADVERTISEMENT

Alternative To DECODE In Regards To Just SQL (not PL)

Jul 1, 2007

im used to using PL SQL via oracle. but lately ive been doing a lot of mysql and PHP and i really need to use something similar to DECODE in my queries. or am i forced to do the checks via PHP.

View 6 Replies View Related

SQL & PL/SQL :: Alternative To UTL FILE?

Jan 12, 2011

I have been told that I cannot use Oracle directory objects, external tables and the UTL_FILE package in my production environment for security reasons.

In this scenario what are the alternate options available in Oracle to write a file and save it in a specific location in the server. One of my requirement is to process a file and write the results of processing in a output file in the application server which in turn will be accessed by the online application.

View 7 Replies View Related

Alternative To DESC

Dec 12, 2006

For whatever reason, in PHP, DESC statements do not work. They're simply unrecognized and you get an OCI "invalid SQL statement" error. It may be some sort of security feature. I've also had the same result with the SQL scratchpad in the Java EM, but not in SQL Plus. Is there an alternative method for getting details on a table that I can use that also doesn't require being logged in as a SYSDBA?

View 5 Replies View Related

Alternative To Union Of Two Different Table?

Dec 21, 2010

Is there any alternative to Union or Union all of two different table. Example would be great.

View 5 Replies View Related

SQL & PL/SQL :: Alternative To Oracle Cursors

Aug 13, 2013

an alternative to Oracle Cursors.My table has huge amount of data and is taking time in processing. I had thought of using BULK COLLECT but it can only be used to insert, delete or update data and will not allow me to select data.

View 33 Replies View Related

SQL & PL/SQL :: Alternative Of Soundex Function

Feb 16, 2010

Is there any alternative of Soundex function. I have two table of city. I want to compare city of both table. Now problem is that one city name is 'HANGO'. In table A City is feed with spelling 'HANGO' and other it is spelled 'HANGU' OR 'HUNGU'. Similarly there are many cases. I used Soundex function but it is not working perfectly.

Is there any other way to compare data by sound.

View 1 Replies View Related

Tnsnames.ora With 2 Alternative IP Server

Jan 31, 2012

Is there a way to put on tnsnames.ora the alternative IP of a server that if the first dont respond it tries the ip of the second? Example:

ORA92 =
(DESCRIPTION =
(ADDRESS_LIST =
try
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.1)(PORT = 1521))
except
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.2)(PORT = 1521))
end )
(CONNECT_DATA =
(SERVICE_NAME = tsh1.world)
)
)

View 6 Replies View Related

Client Tools :: SQL Monitor Alternative?

Jan 5, 2011

Many moons ago, at a previous employer, I had access to the SQL Monitor tool that came with TOAD. I could really use an alternative to this now, as we don't have TOAD here, and it would need to be free. I'm getting a problem with a forms update failing, and I really need to see what's actually being submitted to the database by the form. We're still stuck on Forms 6 by the way

View 2 Replies View Related

SQL & PL/SQL :: Alternative For Writing Subquery In IF Condition

Sep 4, 2011

I am working in EBS 11i and database 9i. I know that we can not write subquery in IF condition like below.

IF deptno IN (select deptno from dept)
--
END IF;

Is there any alternative to achieve above scenario.

View 1 Replies View Related

PL/SQL :: Getting Alternative To LEFT OUTER JOIN?

Sep 21, 2012

I have the following query but it is taking too much time because of the LEFT OUTER JOIN on HST table which is a huge table , is there an alternative to LEFT OUTER JOIN that can be used to optimize the code:

SELECT HST.COMP_CODE,
HST.BRANCH_CODE,
HST.CURRENCY_CODE,
HST.GL_CODE,
HST.CIF_SUB_NO,
HST.SL_NO,
SUM(CV_AMOUNT) CV_AMOUNT,

[code].....

View 10 Replies View Related

How To Load Alternative Records Into Table By Using Sql*loader

Sep 26, 2013

1)my csv file contain 100 records,i want to load 60 records into one table and reaming into another table with same control file by using sql*loader ?

2)how to load the alternative records into table by using sql*loader

View 1 Replies View Related

Performance Tuning :: Getting Alternative To REPLACE Function?

Jun 12, 2010

I have a table with:

1 million rows
average row length 200 bytes
50 columns
and this update statement
UPDATE mytable SET varchar2_4000_column = replace(replace(replace...300 times)

It looks at every row in the table (no WHERE clause) and does these 300 replace operations on this column for each row. Each replace replaces with a null so effectively it is removing strings. Much of the time these strings are not in the column.

This update statement takes 25 minutes and it is 98% CPU and 2% USER_IO time.

I figure that is what is taking all the time since it is a CPU bound statement. if rows in this table are persistent over time then tag rows with a flag to show which ones have already been processed and skip these next time around.

View 2 Replies View Related

Reports & Discoverer :: Alternative For Live Previewer?

Aug 25, 2012

My clients want to print the output of reports of size 25*12 and 15*12 in 11g and I am able to do the printing but the page break required for each page is not coming properly and because of this they cannoy identify which is the 1st and 2nd paper..

But in 6i D2K, there is the Live Previewer through which I can print without any page break issues.

My query is what is the alternative for Live previewer in Oracle Reports 11g so that i can make it right.

View 1 Replies View Related

SQL & PL/SQL :: Alternative To Nested Group By For Performance Enhancement?

Dec 1, 2011

I need consultation on an alternative to a query that I wrote and seems very expensive.

I have the following table:

CREATE TABLE TRY_B
(TID NUMBER PRIMARY KEY,
INFO1 VARCHAR2(10),
INFO2 VARCHAR2(10),
GROUP_NO NUMBER,
TYPEID NUMBER);

[code]....

My objective is to get the data of group_no that is repeated more than once and which includes exactly one occurrence of typeid = 0.

My current query is:

SELECT * FROM TRY_B
WHERE GROUP_NO IN
(
SELECT GROUP_NO
FROM TRY_B
GROUP BY GROUP_NO

[code]....

which works but takes very long time with the real data as my real table is huge.

View 6 Replies View Related

SQL & PL/SQL :: Alternative To Filer Initial Using Single REGEXP_REPLACE?

Feb 9, 2011

Any alternative to filer the Initial using a single REGEXP_REPLACE ?
Oracle version 10.2.xxxx

Currently using two REGEXP_REPLACEs.
SELECT REGEXP_REPLACE(
REGEXP_REPLACE('K I Rajuvan K I', '(([[:upper:]]{1,2})) ','')
, '([[:upper:]]{1,2})$','') CITY
FROM dual;

View 8 Replies View Related

Application Express :: How To Know Which Default Alternative Report Selected

Oct 15, 2012

I'm using apex 4.1 and create a page with Interactive report with one default primary report and several default alternative report. when i selected one alternative report, how do i know which one is i picked up? it looks obvious to the user but my point is when the user switches to another page, or there are something should change based on the selected report, i want the user see the new page with-in the context of what have selected for that IR report. for example, i have a "summary" report based on year, so the alternative report will be year-2011, year-2012, etc, when user selected this year, 2012, on the same IR page, there is another report based on year 2012, when user selected 2011, that report should show 2011 report.

View 0 Replies View Related

Replication :: Alternative For Complete Refresh / Delete Records From MVIEWS?

Aug 19, 2009

TABLE NAME :ABC
(PARTITION BASE TABLE - 84 Partition and each Partition has 500-800 Millions of Records)

/* Step1 Create MATERIALIZED VIEW LOG */
CREATE MATERIALIZED VIEW LOG ON ABC;

/* Step2 Create MATERIALIZED VIEW Refresh after Every 20 Minutes */
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/ (24*60) AS
SELECT * FROM ABC
WHERE TMSTP > SYSDATE-1;

Scenario:Intially data extracted will be "Greater than SYSDATE-1 = 18/08/2009 11:27:39 " - 76 Millions of Records After 20 Min of FAST Refresh, it will add 2500 Records

i)Is query in the MVIEWS (MV_ABC) SYSDATE-1 will be changed on each FAST Refresh, as observation (as per definition) it is incrementing the records to intial extracted data?

ii)As COMPLETE Refresh will be expensive to execute on the partition base table on each refresh.
Is there any alternative way to achieve the Nature of COMPLETE REFRESH in Materialized View?

iii)Is Delete or Conditional Delete option is possible in Materialized View (i.e not a delete from the base table but the delete from MVIEWS)?

iv)What is the machnism for populating the logs for MVIEWS?Is it overhead for the system and better than a Trigger based approach?

View 11 Replies View Related

PL/SQL :: Function Retrieve Output Very Slowly - Alternative Code To Replace With Join?

Aug 6, 2012

In my project, the below function retrieve output very slowly. Is there any alternative code to replace this function with join

CREATE OR REPLACE FUNCTION f_johnson (i_case_id number,i_seq_num argus_app.case_reference.seq_num%type) RETURN VARCHAR2 AS
c_ref VARCHAR2(32500) := null ;
CURSOR c_refer IS

[code]...

View 2 Replies View Related







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