Grabbing 2 Unique Values Based On A Field?

Nov 29, 2012

I have a list sample:

Order#Location VendorName
--------- --------- ------- -------
145646842 MLIQUID02T 368308 JOHNNEISHA
134962284 MLIQUID02T 368308 JERRY
141138899 MLIQUID02T 368308 CARLENA
5078916 MLIQUID02T 368308 DONNA

[code]....

What I'd like to do is run SQL that can create output where I get 2 records from each Location. Example:

Order#Location VendorName
--------- --------- ------- -------
145646842 MLIQUID02T 368308 JOHNNEISHA
134962284 MLIQUID02T 368308 JERRY
13999694 MLIQUID03T 368308 TINA
175439805 MLIQUID03T 368308 RANDI
4801973 MLIQUID05T 368308 DIANA
55907648 MLIQUID05T 368308 DESIREE

Personally, I don't need the top value(s), but it would be nice.I was trying a few routes with rownum, and I can get it to pull 1 set of pairs with a where location= condition, but I can't seem to successfully combine the two.

View 1 Replies


ADVERTISEMENT

Find Situations Where Single Unique Value Of Field A Has Both Values Z AND X In B?

Feb 19, 2011

In literal terms, I'm interested in two fields, I'll call them Field A and Field B. I want to find all situations where a single unique value of Field A has both values Z AND X in Field B (not either or, but both together).

To go into some detail -

I need to make a query that finds ONLY occurrences where one employee id has a certain set of values together (without going into specifics, I'll say PermissionA, PermissionB, PermissionC). I can easily make a query that returns all user id's and all permissions belonging to them, and I can use criteria to filter the results to Permission A B and C only so as to exclude other permissions from getting returned (since there are hundreds),

however my objective is to get ONLY results where the same employee ID has all of those permissions (not just any one or two of the three). However, I don't want to have any criteria that limits the employee ID (I want to search all employee id's, and get a list of those with permissions a and b and c, not just any combination thereof, but all of them). I'm currently able to organize the output using a pivot table by employee id > role,

so that I can easily look at each employee and the roles they have, but I want to undertake a project that will involve searching a much larger number of employees (a # that makes it impractical for me to look through the list, I need to have a query that limits the results to the combinations that I'm looking for, as in this example - permission a b and c together).

View 1 Replies View Related

Forms :: Performing Validation Based On Unique Values In A Form

Mar 10, 2013

In my form i have a multi record block which is based on a table in which i am performing insertion, deletion, updation.

My requirement is as below,

While saving a records i want to check distinct value of specific item can not be greater than some value say 2. Then only my records should be saved. I am planning to populate those values in a collection table type and take the distinct values from it. How to populate record values in a collection table type.

View 2 Replies View Related

Non Unique Index Using A Non Unique Field

Aug 14, 2013

Using Oracle 11g, below is the table, partitions, unique and non-unique local index: 

CREATE TABLE DOCA(  DOCA_ID  NUMBER  NOT NULL ,  DOCA_BKG_PAX_ID  NUMBER  NULL ,  ROW_PURGE_DATE  DATE  NULL ,)PARTITION BY RANGE(ROW_PURGE_DATE)INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))(  PARTITION P2007 VALUES LESS THAN (TO_DATE('01/01/2008', 'dd/mm/yyyy')),  PARTITION P200801 VALUES LESS THAN (TO_DATE('01/02/2008', 'dd/mm/yyyy')),)  TABLESPACE T0; ALTER TABLE DOCA ENABLE ROW MOVEMENT;    

CREATE UNIQUE INDEX XPKDOCA ON DOCA(  DOCA_ID  ASC,  ROW_PURGE_DATE ASC)LOCALREVERSE TABLESPACE I0;  ALTER TABLE DOCA  ADD CONSTRAINT  XPKDOCA PRIMARY KEY (DOCA_ID);    CREATE INDEX XFKDOCA_DOCA_BKG_PAX_ID ON DOCA(  DOCA_BKG_PAX_ID  ASC)LOCALREVERSETABLESPACE I0;  

I would like to know the difference between the performance of the unique and non-unique local indexes?.

View 10 Replies View Related

Forms :: Update Field Based On Another Field Checkbox

May 3, 2013

I have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.

I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.

--tables with insert statement
create table item_trans (trans_item varchar2(12),trans_qty number,trans_act_qty number)

insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);

--i want to set the value of trans_Act_qty as trans_qty

--i create one dummy or test block to keep the select all check box. for that table test script is

CREATE TABLE TEST
(
C VARCHAR2(2000 BYTE),
B NUMBER,
A NUMBER
);

insert into test (C,B,A) values ('A',1,1);

--code written in select all check box which is created on test.block.

BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
:M_END_YN := :M_END_ALL;
[code].......

--code written in M_END_YN ( actual check boxes where i will uncheck).

IF :M_END_YN = 'N' THEN
:M_END_ALL := 'N';
END IF;

--code written on button to update those values which are checked.

BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
IF :M_END_YN = 'Y' THEN
[code]......

View 5 Replies View Related

Unique Function Based Index

Jun 27, 2011

I have a table which sees a lot of use for queries

CREATE TABLE CASE_STAGE
(
ID NUMBER(9) NOT NULL,
STAGE_ID NUMBER(9) NOT NULL,
CASE_PHASE_ID NUMBER(9) NOT NULL,
"CURRENT" NUMBER(1) NOT NULL,
--and other columns
)

ID is a primary key
CASE_PHASE_ID is a foreign key

"CURRENT" should only ever have values of 0 or 1. When it has a value of 1 it is unique for that CASE_PHASE_ID

What I have tried that doesn't work is

create unique index case_stage_F_IDX1 on case_stage("CURRENT", case_Phase_id) which gives me
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

What is the correct syntax, something like ("CURRENT"=1,case_phase_id) seems right but fails with an error about a missing bracket. Do I need to use a CASE statement here?

View 1 Replies View Related

Replication Challenge Based On Unique Needs

Feb 7, 2013

Let's say we have Table - A and we would like to replicate specific row transaction to Table B.

Here are the rows in *Table A*
Time: Lets say 15:00

A1 Just Updated @15:00
A2 Just inserted @15:01
A3

B1 - Daily Delete Row -i.e just deleted a while back - Non scheduled process --executed by application @15:02
B2 -
B3 - Daily Delete Row - i.e just deleted a while back -- Non Schduled process --executed by application @15:05
B4 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
B5 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
B6 -Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10

Current Data in Table B (Before Replication)
@15:00

A1 (without updates)
A3
B1
B2
B3
B4
B5
B6

Expected rows in Table B (via replication/snapshot/materialized view / or any other method)

*Replication at 15:30*
Table B - Read Only

Expected rows after replication-

A1 -- Newly updated details
A2 -- Newly inserted row
A3
B1 - Daily delete row is expected to be replicated
B2
B3 - Daily delete row is expected to be replicated

***Note row B4 is not expected to be replicated to table B.

Questions:

1) How can we get updates, inserts and daily deletes replicated while ignore large purges?
2) How can large purge changes be reflected in replicated tables as well without deleting daily deletes?

View 1 Replies View Related

Application Express :: Assign Values In Many Rows Based On Search Values?

Jul 25, 2013

I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by. 

View 4 Replies View Related

PL/SQL :: Unique Values Of Each Column

Aug 21, 2013

I have a problem .I have a oracle table its column count more then 500 . I want unique values of each column and insert one table.

View 44 Replies View Related

SQL & PL/SQL :: Get Unique Row Number For List Of Values?

Apr 9, 2012

I need a query to get the below.

Source :

select * from test;
LVL
1
2
3
1
2
3
4

Output:

LVLSEQ
11
21
31
12
22
32
42

I need above to uniquely identify the set of data.

View 25 Replies View Related

SQL & PL/SQL :: How To Retrieve Unique Values From A String

Feb 5, 2013

Objective: I need to compile a final string by concatinating the unique values from different strings.

Here is the script to create tables and data.

Create table temp_acronyms(id number, acronym varchar2(30);
insert into temp_acronyms values(1, 'ABC');
insert into temp_acronyms values(2, 'DEC//NOFO');
insert into temp_acronyms values(3, 'CBK//FO TO USA');
insert into temp_acronyms values(4, 'DEC//NO ENTRY');
insert into temp_acronyms values(5, 'ABC//NOFO');

COMMIT;

select * from temp_acronyms;

ID ACRONYM
--- --------
1 ABC
2 DEC//NOFO
3 CBK//FO TO USA
4 DEC//NO ENTRY
5 ABC//NOFO

I need to store all the unique strings from the acronyms for id's 1,2, 3, 4 and 5 into a variable. doesn't matter even if it is through database procedure.

my final string should have the values as below

ABC//DEC//NOFO//CBK//FO TO USA//NO ENTRY

View 6 Replies View Related

SQL & PL/SQL :: Finding Tricky Unique Values

May 30, 2013

I have a table which stores customer ID and price lists assigned to those customers. Each price list can have multiple customers assigned to them. So for example

Customer APrice_List1
Customer BPrice_List1
Customer C Price_List2
Customer D Price_List3

Now I am trying to write a SQL statement to return just pricelists which contain just ONE customer (so in the above example would just return Price_list2 and Price_List3).

View 3 Replies View Related

SQL & PL/SQL :: Get_hash_value Generated No Unique Values

May 8, 2012

We are using the function dbms_utility.get_hash_value to create a unique identifier, the hash is created based on statement of creating an index. We see that for a different statement is being generated the same value, which in this cause errors because this value must be unique.

The Hash is to be calculated as follows HASH_VALUE = dbms_utility.get_hash_value (sql_text, a 10000)

We are using the correct the hash function or should we use another way to generate unique values?

View 9 Replies View Related

SQL & PL/SQL :: How To Not Generate Row_number For Unique Values

Jun 6, 2013

I don't want to generate row_number for unique values 'C' and 'E' in below query.

SELECT NAME, ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME) FROM
(SELECT 'A' NAME FROM DUAL
UNION ALL
SELECT 'A' FROM DUAL
UNION ALL
SELECT 'A' FROM DUAL

[code].....

Means row_number should be NULL for unique values.

View 12 Replies View Related

SQL & PL/SQL :: Multiple Unique Values In One Column?

Aug 26, 2011

I have a problem with some tables in database.

Table has three columns: userid, pname, pvalue.

Userid has unique values, for example: 234, 123, 587, etc.

In the field pname, there is three possible values: MAC, IP, S/N.

So if I go like this:

select pvalue
from table
where pname = 'MAC';

i get the values of MAC.

If I go like this:

select pvalue
from table
where pname = 'IP';

i get the values of IP.How can I join MAC with IP that is matching this MAC? I need an SQL statement for this.

View 11 Replies View Related

SQL & PL/SQL :: Updating Column With Unique Values Without Using Sequences

Apr 17, 2013

i am having a table with out pk along with data.

Now , i need to add one column to that table , and update this column with the sequence no

like 1,2,3...... upto the max no of records. but i have to do this with out using a sequence.

how can i do.

View 1 Replies View Related

SQL & PL/SQL :: Unique Values - Print Output Of Table

Dec 9, 2011

I am stuck with this query.

I have a table "xyz" as -->>

SQL> select * from xyz;

A B
---------- ----------------------------------------
1 Hello
2 Hello
3 No Hello
4 No Hello
5 Hello
6 Hello
7 Hello

I want to print the output of this table as -->>

A B
---------- ----------------------------------------
1
2 Hello
3
4 No Hello
5
6
7 Hello

To make it more clear, I just want that whenever the value f column "B" changes then only its value should be printed, else it should be NULL. And if "B" has same value for all the records then the value of "B" should be printed at the last.

Either of SQL or PLSQL would d for me.

View 11 Replies View Related

Reports & Discoverer :: Get Unique Values With Formula Column

Jul 5, 2013

I am facing a problem in leave_form report! want to show employee's previous leaves detail (leavedate,leavetype), i don't want to show leavetype 'PPP' repeating frame type is Across/Down, there are 22 records of leavetype 'PPP' and one record of leavetype 'CL'

problem is report is showing 22 records of leavetype 'CL' of same leavedate!

i want that report should show the actual leavedate and leavetype records.using 9i database 6i developer server2003

For leavetype

FUNCTION Cf_1formula
RETURN CHAR
IS
v_leavetype CHAR(40);
BEGIN
SELECT LT.description
INTO v_leavetype
FROM hrm_attendance L,
hrm_leavetype LT,
hrm_employees E
[code]....

View 2 Replies View Related

SQL & PL/SQL :: Field Name Based On Calculation

Apr 19, 2010

I want to display Week No as the heading.So, I have these:

define week_no=number
column week_col new_value week_no
select to_number(to_char(sysdate, 'ww')) week_col from dual;

I have tested it and it is working as the way I expected: select 'to display week no as column' "Week &week_no" from dual;

The output:
Week 16
----------------------------
to display week no as column ...So how do I display Week 15, Week 17 etc based on the calculation of &week_no e.g. &week_no+1?

View 19 Replies View Related

Partitioned Table Based On Field

Nov 27, 2012

I have partitioned the table based on field.But when I am selecting by Partition or by the field I am getting Explain plan as Table Access full.I am pasting the sql and Explain Plan here. The table has two partition by BOOKING_DT_WID. One less than 20100801 and other less than 99991231.

CODESELECT * FROM WC_BOOKING_SALESREP_F WHERE BOOKING_DT_WID >= 20100801;
SELECT * FROM WC_BOOKING_SALESREP_F  PARTITION(SALESREP_LESS1_99991231);
Here is the Explain Plan for the same.
CODESELECT STATEMENT  ALL_ROWSCost: 1,501  Bytes: 293,923,641  Cardinality: 809,707                  
    4 PX COORDINATOR              
[code]....

How do I know if the sql is doing partition prune.

View 1 Replies View Related

Updating Field Based On Two Tables

Mar 19, 2007

I am attempting to update a single field in one table based on a select from two tables. However, I am receiving the following error.

ORA-00933: SQL command not properly ended

The sql I am using is as follows:

update PS_TRNS_CRSE_DTL
set RQMNT_DESIGNTN = 'TRN'
FROM PS_TRNS_CRSE_DTL A, PS_STDNT_CAR_TERM B
where (SELECT A.EMPLID, A.ACAD_CAREER, A.INSTITUTION, A.MODEL_NBR, A.ARTICULATION_TERM, A.TRNSFR_EQVLNCY_GRP, A.TRNSFR_EQVLNCY_SEQ, A.TRNSFR_STAT, A.GRADING_BASIS, A.RQMNT_DESIGNTN, B.STUDY_AGREEMENT
FROM PS_TRNS_CRSE_DTL A, PS_STDNT_CAR_TERM B
[code]......

View 6 Replies View Related

SQL & PL/SQL :: Dynamic Variable Based On Another Field?

Apr 26, 2010

In the query below, I'm attempting to replace task codes with task descriptions by left joining to a multi-purpose control table.

In our corp, the description for the task code varies based on the value of change_program in each record, so rather than referencing the value of code_index.tabl statically as I do in the query below (201), I need a dyanamic variable to be defined for each record based on the value of work.change_program, which would represent the value for code_index.tabl.

For each record
if change_program =1 then v_tabl = 201
elseif change_program =2 then v_tabl = 202
elseif change_program =3 then v_tabl = 203
else v_tabl = 201

how to declare and use variables.

SELECT account,
change_program,
task_code_01,
task_code_02,
task1.longdesc,
task2.longdesc
FROM work

[code]....

View 4 Replies View Related

PL/SQL :: Selecting Records Based On Value Of Field

Dec 5, 2012

I have a table test with 10,000 records in it and 50 columns.I have to select those rows which contain values as "Sales Dum" in their field..For table with small number of colums i did this

SELECT * FROM tbl_website_dtl WHERE created_by like '%Sales%' or website_name like '%Sales%' or website_code like '%sales%';But should i do for table containing 50 columns.

View 5 Replies View Related

Application Express :: Change LOV Based On Another Field Value?

Sep 26, 2012

I have a need to change the LOV of a page item's select list based on the selection in another page item's select list. So in other words, for a page item that is a select list, I need to use one LOV normally, and a different LOV if the value of a different page item is set to X.

A dynamic action would be useful here (on change where Select List 1 = x), but I don't know how to take the action of changing LOVsfor Select List 2 based on that action.

View 5 Replies View Related

SQL & PL/SQL :: Partition A Existing Table Based On Varchar2 Field?

Oct 13, 2011

I need to partition a existing table based on varchar2 field (which is actaully date value but storing as character in the table). Using below statement for creating table, but getting error.

create table TST_CUST_ARC
(
interact_id NUMBER(10),
extrn_id VARCHAR2(38),
src_cd VARCHAR2(25),
full_nm VARCHAR2(45),
run_control_date VARCHAR2(13)

[code]....

Getting error : ORA-00907: missing right parenthesis

View 5 Replies View Related

SQL & PL/SQL :: Generate The Number Of Rows Based On Table Field?

Mar 29, 2013

generate the number of rows based on table field.

Sample code is given below.

CREATE TABLE T
(
docno VARCHAR2(10),
CODE VARCHAR2(8),
QTY NUMBER(3)
)
LOGGING

View 4 Replies View Related

Application Express :: Populate Text Field Based On LOV Selection With AJAX

Apr 24, 2013

I would like to populate a text box based on selection from a LOV. If someone selects a LOV value and then tabs off off of that element, I would like the text box populated from a sql statement based on the LOV value in the predicate.Application Express 4.1.1.00.23

View 1 Replies View Related

Forms :: Auto Populating Records In Other Fields Based On Inventory Number Field

Jul 13, 2011

I am having a problem with auto populating different fields based on inventory no. field.. This is a bug giving to me to work on and i not able to figure out how to populate the other fields.

How to set any triggers for the items to auto populate and i am suppose to finish this work today.

View 2 Replies View Related

SQL & PL/SQL :: Separate CSV Values In A Field?

Nov 6, 2012

create table nov06(
f_name varchar2(20),
l_name varchar2(50),
m_name varchar2(10)
)

insert into nov06 (l_name) values (aaa,bbb);

insert into nov06 (l_name) values (ddd,nnn,jr);

insert into nov06 (l_name) values (fff,mmm);

here the values in the l_name are seperated by ','.
comma seperated values represents "f_name,l_name,m_name".

I've to seperate the strings and put in their respective fields.

Quote:
e.g in second insert statement value (ddd,nnn,jr), so the values will be
f_name -> ddd
l_name -> nnn
m_name -> jr

how can I do it???

View 2 Replies View Related

SQL & PL/SQL :: Inserts Based On Column Values

Aug 15, 2012

I have a data in one table with 6 columns where user may be updating values in all of these 6 columns or he may enter 3 or 4 columns based on that inserts should take place, this is similar to my previous thread , i am using if condition to check column for null if its not null then i will make a insert , but is there any other easier way to do this.

CREATE TABLE ot_inspect_head
(inh_sys NUMBER,i_txn_code VARCHAR2(12),i_no NUMBER,i_ref_txn VARCHAR2(20),i_ref_no NUMBER);
CREATE SEQUENCE inh_sys START WITH 1;
CREATE TABLE ot_inspect_item
(ii_inh_sys NUMBER , ii_pi_sys NUMBER,ii_sys NUMBER,ii_item_cd VARCHAR2(12),
ii_grade VARCHAR2(12),ii_qty number, ii_flex_01 VARCHAR2(12),ii_flex01_qty number,
ii_flex_02 VARCHAR2(12),ii_flex02_qty number,ii_flex_03 VARCHAR2(12),ii_flex03_qty number);
CREATE SEQUENCE ii_sys START WITH 1;

insert into ot_po values ('ss-po',1,ph_sys.nextval);
insert into ot_inspect_head values (inh_sys.nextval,'ss-ins',1,'ss-po',1);
commit;
select * from ot_inspect_item

II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- -------------
2 1 2 HEA100 A 100

--Now if the inspection user issues the update statement , it will delete this row
--from ot_inspect_item and reinserts the values with values based on
--ii_flex_01,ii_flex_02,ii_flex_03
[code]...

View 5 Replies View Related







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