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);
I am using oracle 10g.
I have a query which is used to get the contact details based on the zip code
select * from contacts where
primary_address_postalcode like '65084%' or
primary_address_postalcode like '65011%' or
primary_address_postalcode like '65034%' or
primary_address_postalcode like '65078%' or
primary_address_postalcode like '65050%' or
primary_address_postalcode like '65037%' or
primary_address_postalcode like '65329%' or
primary_address_postalcode like '65072%' or
primary_address_postalcode like '65081%' or
primary_address_postalcode like '65038%'
Result
userid name primary_address_postalcode
5 abc 65072
6 def 65038 -12343
1 xyz 65050
24 pqr 65011
32 test 65011-23455
54 itsme 65329
i want to order the records just same as the zip codes we are using in like (65084,65011,65034, 65078,65050, 65037, 65329,65072, 65081, 65038)
How can i get the result like below
expected result
userid name primary_address_postalcode
24 pqr 65011
32 test 65011-23455
1 xyz 65050
54 itsme 65329
5 abc 65072
6 def 65038-12343
I have an question.
- Imagine I have an table with several ZIPCODE.
- Imagine I want to return from that table the existence of several zipcodes I need to get.
- Imagine that I need to return both zipcodes that exists and both zipcodes that not exist.
The solution I've found to do this, was using the Pivot method, like the sample below, if there is another way to return anything like that. Return the zipcodes that exist, and the zipcodes that does not exist also!
Table Creation:
CREATE TABLE "AAA_ADDRESSZIPCODES"
("ZIPCODE" NUMBER,
"NAME" VARCHAR2(50 BYTE),
"COD_PLACE" NUMBER
)
Data:
Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1100,'Portugal',2);
Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1150,'Portugal',2);
Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1000,'Portugal',1);
Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1200,'Spain',2);
Select using Pivot:
SELECT *
FROM (SELECT distinct zipcode FROM aaa_addresszipcodes group by zipcode )
PIVOT ( count(zipcode) FOR zipcode IN (1000,1100,1200, 1150, 2000) )
I am getting string from my tool like this.... .. ‘ ‘PH1234’,’Ph3456’,’PH5678’ ‘
I wanted to remove single codes and take the each value and I have to process. Need output like this....
PH1234,
Ph3456,
PH5678.
Is there a way we can wrap pl sql codes and other people will not be able to unwrap it? because I came across this site successfully unwrapped my wrapped pl sql codes. How to just unwrapped pl sql code then what is the use of wrapping?
View 1 Replies View RelatedI am trying to build a report.My query is working fine when i take out this report for a single area_code.But it is not showing proper result when report is take for all are_code's available in table.I have used two tables transactions and balance
create table transactions ( glcode varchar2(10), area_code varchar2(10), debit number,credit number );
insert into transactions values(2000,'ap',200,200);
insert into transactions values(3000,'ap',222,222);
insert into transactions values(4000,'ap',123,123);
insert into transactions values(2000,'dp',200,200);
insert into transactions values(3000,'dp',222,222);
insert into transactions values(4000,'dp',123,123);
insert into transactions values(2000,'pp',200,200);
insert into transactions values(3000,'pp',222,222);
insert into transactions values(4000,'pp',123,123);
[code]....
Database version: 11.2.0.3.0
I need to remove duplicate values from concatenated long string of state codes(comma separated). Ex: 'VA,VA,PA,PA,CT,NJ,CT,VA'. I tried following query and did not get required out put.
select regexp_replace('VA,VA,PA,PA,CT,NJ,CT,VA,CT,PA,VA,CT','([^,]*)(,1)+($|,)', '13') new_str from dual;
Define Meta-character's format in regular expression to get desired result. Out put required: VA,PA,CT,NJ (with out any duplicates).
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
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
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]....
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
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
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.
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.)
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]....
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.
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 RelatedI'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
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
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
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
