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
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.
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.
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 RelatedIs there any alternative to Union or Union all of two different table. Example would be great.
View 5 Replies View Relatedan 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 RelatedIs 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.
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)
)
)
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 RelatedI 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.
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].....
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
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.
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.
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.
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;
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 RelatedTABLE 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?
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]...
Which warehouses have pending orders for products, which are not in stock at the warehouse at the moment? Provide warehouse number, id of the product that is not in stock, number of orders issued for this product and total quantity ordered.
The tables I am using are
Warehouses:
Name Type
----------------------------------------
W_ID NUMBER(38)
CITY VARCHAR2(20)
W_SIZE NUMBER(38)
Inventories:
Name Type
-----------------------------------------
P_ID NUMBER(38)
W_ID NUMBER(38)
QUANTITY NUMBER(38)
Orders:
Name Type
-----------------------------------------
ORD_ID NUMBER(38)
SUPPLIER_ID NUMBER(38)
ISSUING_EMP_ID NUMBER(38)
ORDER_DATE DATE
ORDER_STATUS CHAR(1)
This is my code so far:
select w.w_id, i.p_id,
sum(decode(o.ord_id, ' ', i.p_id, 0)) Orders_issued,
select sum(i.quantity)
from
inventories i
orders o,
[code]...
but I get this error:
select sum(i.quantity)
*
ERROR at line 3:
ORA-00936: missing expression
Just working with Unix for the first time and trying to understand this decode statement?
cursor l_cursor is select decode(type||'-'||to_char(line,'fm99999'),
'PACKAGE BODY-1','/'||chr(10), null) ||
decode(line,1,'create or replace ', '') ||
decode(type||'-'||to_char(line,'fm99999'),
'JAVA SOURCE-1','and compile ' || type || ' named "' ||
name || '"' || chr(10) || 'AS' || chr(10), '') ||
text text
from user_source
where upper(name) = upper(p_name)
order by type, line;
In my Hard code i saw decode like this
DECODE(tablename.columnname,'k',text,NULL,text, NULL)
can I use decode like this.
I need to get create_user_id for different sale_location_id.Also create_user_id field will be having different values.This is part of my big query.I need to add this stmt in that.So taken that part and figuring it out.
create table it(sale_location_id number,create_user_id varchar2(10));
table IT created.
insert into it values(1,'ISRA')
1 rows inserted.
insert into it values(2,'USFA')
1 rows inserted.
select a.sale_location_id,decode(a.sale_location_id,1,a.create_user_id like 'IS%',a.create_user_id like 'U%') create_user_id from it a
given error as:
ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"
How to write this.
I have a scenario where in report table I need to get
IF FLAG='Y' AND LOC=118443 THEN STATE='FIN'
IF FLAG='Y' AND LOC!=118443 THEN STATE SHOULD BE ('COMP','PART')
IF FLAG='N' IT SHOULD INCLUDE ALL STATES.
I tried using decode in WHERE CLAUSE , but not successful.
Create Table Report(Id Number,Loc Number,Flag Varchar2(3),State Varchar2(20));
table REPORT created.
Insert Into Report Values(1,1,'Y','COMP');
1 rows inserted.
Insert Into Report Values(2,118443,'Y','FIN');
1 rows inserted.
Insert Into Report Values(7,118443,'Y','COMP');
1 rows inserted.
[code]...
How to use decode and case in "where" and "from" clause of a select statement.
I know the decode can't be used in where clause. In that case how we can use decode and case in from clasue that is: table definition
I tried writing a decode statement but it doesn't work as expected. The data I'm working has 4 different brand names with an associated code. I tried using decode to get the 4 brands to be on one line like this: Description, Inventory_Item, Product_Line, Product_Type, Brand_1, Brand_2, Brand_3, Brand_4..I still get 4 lines with the Brands displayed like a stair steps.
What do I have to do to get the data on one line? My code is as follows:
SELECT sgk_invfg_organization_items.description,
sgk_invfg_organization_items.inventory_item,
sgk_invfg_organization_items.product_line,
sgk_invfg_organization_items.product_type,
Decode(sgk_item_master_launch_v.sgk_model_item, 20,
sgk_item_master_launch_v.sgk_model_number) brand_1,
Decode (sgk_item_master_launch_v.sgk_model_item, 30,
[code]...
I have a small doubt...Without any creation of tables as I feel it's not needed...(let me know if u need them) Error says "Missing Keyword"
select distinct record_number
from meta m, procedu b, control v, fact f, calendar dc, person p
where f.key = m.key and f.group_key = b.group_key
and b.proc_key = v.proc_key
and f.calendar_key = dc.calendar_key
and f.person_key = p.person_key
and VALUE BETWEEN DECODE((Select distinct operator_type
[code]....
But this gives an error...If the decode gives a "single"...the statement is working fine...but If the decode gives a "range"...the above statement gives an error saying "missing keyword"..Does the above code when mapped to "Range"...is it not producing "and" like " value between 100 and 1000"
How can i use the decode function?
for example
I have the value of 1000 then the numbers 50-100 will be 'A' and 1-49 = 'B'?
DECODE(:P_PERIOD_TYPE,'PJTD','PROJECT-TO-DATE','PTD','PERIOD-TO-DATE','YTD','YEAR-TO-DATE')
what does it mean..
How to get the required output with single query to acheive IF SAL>2500 and job='manager' then display job as 'seniormanager'.
CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7, 2),COMM NUMBER(7, 2),DEPTNO NUMBER(2));
INSERT INTO EMP VALUES(7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
[code]....
