SQL & PL/SQL :: Eliminate Donor With Multi Topic Code
Apr 27, 2012
Below is an overs implication of what I need to extract from a donor list and am having some difficulty pulling the correct targets. I need to pull a donor who has had only one topic in the past year.
I only want id number 100 and not 120 since 120 has three topics in the past year, I tried using not exists...etc and can't seem to get the donors with the one topic 'HC' that is being requested.
example:
create table Topic(Idnumber number(8),topic varchar2(4));
INSERT INTO Topic(idnumber,topic)
VALUES (100, 'HC')
/
INSERT INTO Topic(idnumber,topic)
VALUES (120, 'HC')
/
[code].......
IDNUMBER TOPI
---------- ----
100 HC
120 IRS
120 PRS
120 HC
SQL> select idnumber from topic where topic in('HC');
IDNUMBER
----------
100
120
SQL> select idnumber from topic where topic in ('HC') and topic not in('IRS','PRS');
IDNUMBER
----------
100
120
View 12 Replies
ADVERTISEMENT
Mar 7, 2010
SQL> select * from year;
YEAR YEARGOAL
1992 5000
1995 5000
1993 5000
1994 5500
[code]...
what would the query be toList the donor number and last name of donors who did not contribute in 1994, in order by donor number.
View 2 Replies
View Related
Feb 12, 2009
I have no knowledge about Barcode. The problem is an issue of Loyalty Cards of a Hotel and Restaurant to various customers and then these cards will be presented by the customers time to time in the Hotel as well as Restaurant. The Owner of the Hotel and Restaurant wants to generate separate barcode for each card and when this card will be presented then the bar code reader will readout the code and the system will calculate the amount of discount/rebate. Because if the data entry operator enter the code of the card through key board the it will be a chance of leakage or misuse of that card.
View 8 Replies
View Related
May 30, 2012
I have to compare my SVN source code (packages, views etc) with the production code in the database like views etc (actually we are not sure that what we have in the svn is the final version of production code, we have objects created in the production database, but we don't have latest scripts for that. we have to deploy the svn code in the UNIX box).
So here the comparison is between the OS files and the database objects.
I thought I would get scripts of all the packages, views etc from the production database by using DBMS_METADATA or some utility and save the code in OS files then compare one svn file with OS file manually by using some comparison tools e.g toad provide one comparison tool.
View 5 Replies
View Related
Jun 11, 2013
I have a table like below:
tableA
aid des
1 concrete
2 wood
3 straw
4 plastic
5 fiber glass
6 other
tableB
bid material
01 1
02 2
03 3
01 2
01 2
02 3
01 5
The result I need when updating another table with this info is:TableC
edw_id bid Requirement
021 1 concrete, wood, fiber glass
032
025
123
521
I do not want :
concrete, concrete, concrete, wood, wood, fiber glass
SO far I am using the following but since I am dealing with hundreds of column that has the same material, when using listagg() from oracle 11.2g, they column width is too wide to fit into the required column.
update eris_data_work e set E.flex37 =
(select
LISTAGG(CM.des, ',') WITHIN GROUP (ORDER BY CM.des) AS casing_material
from CODE_CASING_MATERIAL CM, TBLCASING CA
where CM.code=CA.MATERIAL and CA.well_id=E.owner_oid AND CM.DES IS NOT NULL
GROUP BY CA.well_id)
where E.source='WWIS_ON'
I have even used the regexp_count() to try to eliminate duplicates however I have had no success so far
View 4 Replies
View Related
Mar 17, 2009
I am writing my first procedure and need to exclude two codes from a list of receipts. I can probaby use the following
and rc_receipt_code in (1, 2, 3, 4, 7, 8)
however can I use <> to eliminate 2 codes for instance can I say
select NVL(sum(rc_amt), 0)
into tot_cont
from trefrc
where rc_filer_seq = filer
and rc_receipt_code <> (5, 6);
View 1 Replies
View Related
Jan 27, 2009
I have to eliminate duplicate pairs from the following data set.
for ex. Adney Vaughan and Berkly Wassen appears in both AG1 and AG2. how can i get rid of these repititive rows?
AG1 ----------- AG2
Acton Wibert ---- Currier Barhydt
Adney Vaughan --- Luella Edmund
Adney Vaughan --- Berkly Wassen
Alden Anstruther --- Courtney Gavet
Ashley Alvord --- Saunders Buel
Aswin Wilbraham --- Dale Cooper
Barnum Sears --- Grayson Lightfoot
Berkly Wassen --- Luella Edmund
Berkly Wassen --- Adney Vaughan
Bersh Musgrave --- Derward Knight
Berthilda Darrell --- Broderick Reynold
Broderick Reynold --- Berthilda Darrell
View 1 Replies
View Related
Nov 2, 2011
i can't eliminate the spaces between values, i tried to use rtrim but still failed.
Set pagesize 0
set linesize 1000
set heading off
set feedback off
set colsep '|'
SELECT '200', '20002977', T0.TP, T0.Description, T2.FirstName, T2.LastName, 'Geography Code', SUBSTR(T3.aoManager, -6,5)
[code]....
View 6 Replies
View Related
Apr 29, 2011
In my table i have a field called swistmsg, which contains value as
:201:0001OTT11000004|:23b1:CRED|:32a1:01-01-2011|:32a2:USD|:
32a3:1000.|:33b1:USD(similarly around 100 rows)
and my requirement is
201 0001OTT11000004 23b1 CRED 32a1 01-01-2011 32a2 USD 32a3 1000 33b1 USD
and i have eliminate '.',':'&'|' (only quoted chars)from the string separate string
View 17 Replies
View Related
May 10, 2012
I have a table with Date Field . While selecting the records its display like below format.
TO_CHAR(CHAR_DATE,'DD-MM-RRRRHH:MI:SSAM')
10-05-2012 12:00:00 AM
10-05-2012 03:26:16 PM
1 row doesnt have time, but in default it shows 12:00:00 AM, how to eliminate it. Display should be
10-05-2012
10-05-2012 03:26:16 PM
create table time_test (char_date date);
INSERT INTO TIME_TEST ( CHAR_DATE ) VALUES (
TO_Date( '05/10/2012', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TIME_TEST ( CHAR_DATE ) VALUES (
TO_Date( '05/10/2012 03:26:16 PM', 'MM/DD/YYYY HH:MI:SS AM'));
COMMIT;
select TO_char(CHAR_DATE,'dd-mm-rrrr HH:MI:SS AM') from time_test;
i need in to_char only, im using it in reports
View 4 Replies
View Related
Feb 13, 2012
I downloaded oracle sql developer, i type my code into a worksheet but if i use the run statement option, it asks me to make a connection. I dont want to make a connection, just test the data locally.However, even if I do try and make a connection, i get ora-12560 error (local connection).
I just want to type up some data to make some table and test to retrieve or manipulate the data. I'll use any program, command line or gui.
View 7 Replies
View Related
Nov 24, 2011
I need to delete the duplicate values from plsql table OR move the distinct values in plsql table to other plsql table.
how can i do this ?
DECLARE
TYPE alist IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
p_tbl alist;
BEGIN
p_tbl(1) := 'A1';
p_tbl(2) := 'B2J';
p_tbl(3) := 'A1';
[code]......
The p_tb1 table contains all the above values including duplicates. Now I need only distinct values to be copied in another plsql table of same type.
View 14 Replies
View Related
Mar 5, 2010
I need to eliminate the blank spaces based on below conditions Consider name column with a value as
Input : "sa c h in Te nd ulka r" where "Sachin" is first name and "Tendulkar" is last name. there is more than 1 space between sachin and tendulkar (here its not displaying properly)
Condition :Second name is seperated from first name with more than 1 spaces and others are with 1 black space. I need to get result as Output:"sachin Tendulkar" ( there should be 1 blank space between first and last name in result.)
View 3 Replies
View Related
Jul 10, 2012
My requirement is as follows .
Oracle version details
BANNER
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
[code]....
View 6 Replies
View Related
Aug 21, 2013
How to eliminate duplicates from record types?Below code errors out with "Wrong number of arguments in call to MULTISET...."
error. DeclareTYPE ln_x_tab IS RECORD(x1 number ,x2 VARCHAR2(4000) ,x3 VARCHAR2(4000) ,x4 VARCHAR2(4000) ,x5 VARCHAR2(4000)); TYPE ln_x_type IS TABLE OF ln_x_tab INDEX BY BINARY_INTEGER; ln_x1 ln_x_type; ln_dist_x1 ln_x_type; gc_stmt varchar2(4000); Begin gc_stmt := ' SELECT x1, x2, x3, x4, x5 FROM table WHERE dynamic_conditions; EXECUTE IMMEDIATE gc_stmt BULK COLLECT INTO ln_x1; ln_dist_x1:= ln_x1 MULTISET UNION DISTINCT ln_x1; End;
I need ln_dist_x1 to have distinct records from table.
View 12 Replies
View Related
Feb 18, 2013
I have oracle database server set with Windows NT authentication. How can I get rid of this kind of authentication as this is holding up additional Windows Domain with its own PDS and so on. Or is it possible to move Oracle Database server to a different Domain and authentication to be coming from new domain?
View 4 Replies
View Related
Sep 11, 2013
I'm trying to eliminate duplicate string for more than 1 occurrences along with its delimiters, but couldn't get it working. Here is what I tried.
SQL> column str format a30
SQL> column replaced format a30
SQL> with x as
2 (select 'a#~#b#~#a#~#d' as str from dual union all
3 select 'a#~#b#~#c#~#a' as str from dual union all
4 select 'b#~#a#~#c#~#a' as str from dual)
select str,
regexp_replace(str, '[^a|#~#a]{2,}','',1,2) replaced
from x; 5 6 7
STR REPLACED
------------------------------ ------------------------------
a#~#b#~#a#~#d a#~#b#~#a#~#d
a#~#b#~#c#~#a a#~#b#~#c#~#a
b#~#a#~#c#~#a b#~#a#~#c#~#a
The output I need is
a#~#b#~#d
a#~#b#~#c
b#~#a#~#c
View 12 Replies
View Related
May 9, 2012
i need to write a function to eliminate SUNDAY AND SATURDAY;
My criteria is
if My date as (5/19/2012 ) and i want to add 10 days to it themn my function should return 06/01/2012
if My date as (5/13/2012 ) and i want to add 12 days to it themn my function should return 05/29/2012
View 11 Replies
View Related
Apr 28, 2010
I am spooling to a text file some output for a client. The file has 4 queries in it, one creates a header row, another a comment row, another the data rows and finally a trailer.
Code looks something like this:
/*
Custom Extract
Project: Plan Data Extract
Product: EOWin 4.02 - Oracle db
Use: Script to create above extract and spool results to text file
Input Parameters: &1 Path and name of output file
*/
[code]....
and the output looks like this:
HDR,04272010,Plan Data
CMT,Plan Num,Plan ID,Plan Name,Shares Allocated
DAT,1,01,Plan 01,99999999
DAT,2,02,Plan 02,99999999
DAT,3,03,Plan 03,99999999
DAT,4,04,Plan 04,99999999
FTR,4
but the client and I want the output to look like this with no blank lines in between the queries:
HDR,04272010,Plan Data
CMT,Plan Num,Plan ID,Plan Name,Shares Allocated
DAT,1,01,Plan 01,99999999
DAT,2,02,Plan 02,99999999
DAT,3,03,Plan 03,99999999
DAT,4,04,Plan 04,99999999
FTR,4
View 3 Replies
View Related
Dec 2, 2006
I would like to find a way to do a multi row insert in Oracle similar to the examples below ... if possible.
Note that I need it to work with constant values and not values selected from (another) table (ref examples below).
In MySQL and DB2 I can do this:
insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
values (8000,0,'Multi 8000',1),(8001,0,'Multi 8001',1)
In MSSQL, PostgreSQL, and SQLite I can do this:
insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
select 8000,0,'Multi 8000',1 union all select 8001,0,'Multi 8001',1
View 14 Replies
View Related
Sep 16, 2010
I need to select multiple columns but only have 2 of them which are distinct. For instance if i have
userid lastname firstname city country time
1 jones tom lon gb 2:25
2 wall paul la usa 2:30
1 jones tom lon gb 2:50
3 smith jane ny usa 2:55
what i would want to do is select all the columns but avoid duplicate lastname-firstname combination rows. The problem is if i use a group by i have to include all the columns and because time is different i will get tom jones twice. a way of getting round this so i can select all the columns but only 1 row of tom jones.
View 7 Replies
View Related
Jun 2, 2012
My application spools the data from database into file and file is fixed length. My application working fine when database has single byte characters. But I am getting fields postion misposition and length mismatch in spooled line when data base has Multi byte characters(ê).
I want to convert the multi byte characters into single byte while spooling the data( ê ==> e)
The below query has converted the multi byte to single byte character. But actual value is modified into lower case.
####################################################
select utl_raw.cast_to_varchar2((nlssort(c.Name,'nls_sort=binary_ai'))) sbyte, '|'
c.Name
from EMP c
viva! experiencias | Viva! Experiências
####################################################
View 2 Replies
View Related
Oct 13, 2011
I have a table with, for example, three columns: A, B,C.
I execute on this table only one select: CODESELECT * FROM TABLE WHERE A = :1 AND B=:2
Column A has a lot of distinct values (numbers), but B can have only two values: 'Y' or 'N' (cardinality about 50%/50%). It is worth to create index on two columns: A, B? Does query using index on A column will be much slower than using index on A, B?
View 4 Replies
View Related
Jan 24, 2012
I have in my database (OLTP-System) a table with about 6000000 records and a zise of about 2GB.
the way to create multi_column indexes on the table?
What are the rule to define the best-position of a column in an index?
index_1(col_1,Col_2,col_3) and not [ (col_1,Col_3,col_2) or (col_2,Col_3,col_1) or (col_2,Col_1,col_3) or (col_3,Col_2,col_1) or (col_3,Col_1,col_2) ] ?
View 4 Replies
View Related
Mar 12, 2012
I want to enter data in Arabic and English through Oracle Forms6i.
View 8 Replies
View Related
Feb 21, 2011
I've below table structure with given table data. I need below given output i.e. a linear output. It should be dynamic i.e. there are only three columns for Manoj and four columns for Arun. If I insert one more row for Arun the new added row should also be reflected in the same row and every column should give same contact type.
CREATE TABLE TEST123
(
PERSON_NAME VARCHAR2(100),
CONTACT_TYPE VARCHAR2(20),
CONTACT_NO VARCHAR2(15)
) ;
[code]...
how go generate this output on single query.
View 9 Replies
View Related
Mar 8, 2012
CREATE OR REPLACE PACKAGE pkg_mkt_hub_load_collection
AS
PROCEDURE sp_final_load_mkt_hub;
END pkg_mkt_hub_load_collection;
/
CREATE OR REPLACE PACKAGE BODY pkg_mkt_hub_load_collection
AS
[code]....
error : 30/44 PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
when i removed bulk collect from fetch commands, it works fine.
View 2 Replies
View Related
Oct 3, 2011
I have a multi tab form. When I open any form; the form no. and form lists are automatically uploaded. I can enter the rest of the data and save the form. The problem is; if I try to open another tab(form), system did not allow it and ask to enter data in the already open form fields. I can only save the form, which I open first.
View 2 Replies
View Related
Jul 22, 2012
I been doing a little testing with indexing the contents of URLs (database v11.2.x.x).
ctx_ddl.drop_preference('TEST_URL');
ctx_ddl.create_preference('TEST_URL','URL_DATASTORE');
ctx_ddl.set_attribute('TEST_URL','Timeout','3600');
create index datastores_text on test_url_search ( doc ) indextype is ctxsys.context parameters ( 'Datastore TEST_URL' );
SELECT SCORE(1),DOC from TEST_URL_SEARCH WHERE CONTAINS(DOC, 'London', 1) > 0
This works. i like to more
e.g. multi words search e.g. ' London Games' where if London or games are present it return a URL with a score,
or
Search in the Chinese.
The only way I can see to do this is create different indexes as the attributes for 'URL_DATASTORE' are limited. If I can what does the 'Contains' statement look like?
View 0 Replies
View Related
Aug 23, 2012
whether it is possible to implement multi-threading in plsql?
we have a plsql procedure in our application which processes around 50 MM records per day. I am looking for options to implement multi-threading from the same procedure, so that work can be distributed across parallel threads/sessions and will finish processing faster.
View 4 Replies
View Related