PL/SQL :: Output Only Combination Of 2 (col1 Value) And 1 (col2 Value)?

Mar 19, 2013

I have data like:

Col1 Col2 Col3
1 1 N
1 1 N
1 1 Y
2 1 N
2 1 N
2 1 N

I need in output only combination of 2 (col1 value) and 1 (col2 value). i.e. consider only those records where all records of key combination (col1, col2) have 'N'

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Combination Of Values

Jan 7, 2011

I have a requirement in this format.

These are the scripts for the 2 input tables

CREATE TABLE TMP_split
AS
SELECT 1000 AS grp_id, 'abc' AS ATTRIB, 60 AS PCT FROM DUAL
UNION ALL
SELECT 1000 AS grp_id, 'pqr' AS ATTRIB, 40 AS PCT FROM DUAL;

[Code].....

The output needs to be in this format

key | val | grp_id | pct
------------------------------------------
6~7~8 |DIM_11~DIM21~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM21~DIM31 | 1000 | 40
6~7~8 |DIM_11~DIM22~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM22~DIM31 | 1000 | 40
6~7~8 |DIM_11~DIM23~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM23~DIM31 | 1000 | 40

Is there a good way to dynamically obtain this output either by PL/SQL or by query?

This is a case where combination needs to be used. However, I am not able to arrive at the right algorithm for this

View 6 Replies View Related

SQL & PL/SQL :: Generate Unique Combination

Dec 17, 2011

I need a "solution", guidance to a problem I have to solve. I have different letters with a value associate to it like

A:10, B:20, ..., G:250 and I have a Target to reach TARGET= 90

and I need to find among all letters I have which combination is equal or closest to my target.

A:10
B:20
C:20
D:20
E:30
F:40
G:250

IF there is more than one solution possible, the first found is perfect. With my example I can reach 90 with different combinations:

A+B+C+F
A+B+D+F
A+C+D+F
B+C+D+E
B+E+F
C+E+F
D+E+F

To complicate the things I have up to 10 different letters that need to combine to match my target. If there is no combination that exactly match my target, the closest higher combination is picked.

View 31 Replies View Related

Forms :: Key Combination To Get Another Form

Mar 19, 2010

I have a requirement here. User wants when he pressed 'CTRL+E' on the form item, he must get another form. Actually 'CTRL+E' is defined for EDITOR. Then how could I achieve this?

View 2 Replies View Related

SQL & PL/SQL :: Finding UNIQUE Combination

Oct 4, 2012

I have contents like below:

BREAD,BISCUIT
BREAD,MILK,BISCUIT
COKE,MILK
MILK,SUGAR

Now combination from each row will be like below (Just for understanding):

BREAD,BISCUIT --> [COMBINATION] {BISCUIT} {BREAD} {BISCUIT,BREAD}
BREAD,MILK,BISCUIT --> [COMBINATION] {BISCUIT} {BREAD} {MILK} {BISCUIT,BREAD} {BISCUIT,MILK} {BREAD,MILK} {BISCUIT,BREAD,MILK}
COKE,MILK --> [COMBINATION] {COKE} {MILK} {COKE,MILK}
MILK,SUGAR --> [COMBINATION] {MILK} {SUGAR} {MILK,SUGAR}

Now the ultimate aim is to find unique combinations (even if the same combination is present in different rows, we have to consider it as one combination), along with its frequency, result will be like below:

BISCUIT [occurence in 2 transactions and total 4 transactions] = 2/4 = .5
BREAD [occurence in 2 transactions and total 4 transactions] = 2/4 = .5
COKE [occurence in 1 transactions and total 4 transactions] = 2/4 = .25
MILK = 3/4 = .75

[Code]....

writing query to find unique combination like above? What I have tried is as below:

create table test (row_no number, col_no number, item varchar2(50))
/

insert into test values (1,1,'BREAD');
insert into test values (1,2,'BISCUIT');
insert into test values (2,1,'BREAD');

[Code]....

But I am not able to form the exact query. let me know if this can't be done through a single query.

View 20 Replies View Related

SQL & PL/SQL :: Previous Record For Multiple Combination

Aug 17, 2012

How to achieve "Prev_Value" column as shown below without using ORACLE analytic functions

I have records stored in table for various categories and based on ID / Name / Office / Product / Category combination I want to achieve previous value column through efficient SQL query

Test Scripts as below

CREATE TABLE TEST_Prev
(
ID1 NUMBER(3),
Name1 VARCHAR2(10),
OFFICE VARCHAR2(20),
PRODUCT VARCHAR2(20),
Overall VARCHAR2(20),
DATE1 DATE,
VALUE1 NUMBER(1)
);
commit;
[code]......

Expected output as in attached sheet.

View 11 Replies View Related

SQL & PL/SQL :: Oracle - Generating Combination Of Numbers?

Oct 25, 2010

I have to write a stored procedure/function which has to generate the combination of numbers
For eg: IF I/p is an array of numbers a(i) = [1,2,3]

I want to get various combinations of numbers with these three digits.

writing the stored procedure generating the output for this

View 18 Replies View Related

SQL & PL/SQL :: Locate Occurrence Of Symbols Combination

Aug 5, 2011

My need is to locate an occurrence of symbols starting from "s." (non-capital letter), following by word (with any capital letter at the beginning) and ending with ", " (comma and space symbols).

Ex:

select 'jeklghje, s.Glkgje, u.slgjwek, 904869' as tt from dual union all
select 's.Tklgj, u.slgjwek, 23578, elslgjs' as tt from dual union all
select 's.klgj, u.ekgjes, 238573, dlsjkgj' as tt from dual

I'm looking for occurrence of "s.Glkgje, " and "s.Tklgj, ".

I think some combination of REGEXP_INSTR and REGEXP_SUBSTR should be useful, but I'm not familiar with these functions so good yet.

View 7 Replies View Related

SQL & PL/SQL :: Deleting Duplicate Combination Of Records From Table?

Sep 29, 2011

How can I delete the duplicate combination of records from the below table.

CREATE TABLE test
(
gidNUMBER(10),
pidNUMBER(10)
);
INSERT INTO test VALUES (10,20);
INSERT INTO test VALUES (20,10);
INSERT INTO test VALUES (25,46);

[code]....

The condition is if GID = PID and PID = GID then only one combination of these records should be retained. For example Out of 10-20 and 20-10 only one record should be retained.

Expected result after deletion

GID PID
---------- ----------
10 20
25 46
89 64
15 16
19 26

View 5 Replies View Related

SQL & PL/SQL :: Part Number And Manufacturer Combination Must Be Unique?

Aug 31, 2010

i'm trying to import data to QUANTUM "oracle database" from Oracle database import assistant using OCDia.exe with SQL statement below and i'm getting this error message "Part Number and manufacturer combination must be unique"

-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE ACE_LISTPRICE_LEADTIME (P_IMP NUMBER)
IS
C DIA_RL_PKG.CURSOR_TYPE ;

[Code]....

View 2 Replies View Related

PL/SQL :: Find Out Column / Combination Of Columns From Given Table

Sep 23, 2013

Given a table with some columns and data associated with that. Need to find out a column or a combination of some columns, so that the values or combination of values will be unique in the table.The table and number of columns and the columns will be dynamic.

View 10 Replies View Related

SQL & PL/SQL :: How To Print The Multiple Output Statements In Single Output

Jul 1, 2013

DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
[code].......

this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.

View 2 Replies View Related

SQL & PL/SQL :: Cross Join Query To Remove Repeating Combination

Nov 15, 2010

I have constructed a cross join query, with the test case below

create table ajit_sites (
site_id char(1));
insert into ajit_sites values ('A');
insert into ajit_sites values ('B');
insert into ajit_sites values ('C');
COMMIT;

sql below is constructed to display combination of all sites (cross-join), it also removes records where "origin" is the same with "dest"

select
a.site_id origin, b.site_id dest
from
(select site_id from ajit_sites) a,
(select site_id from ajit_sites) b
where
a.site_id <> b.site_id b

Is there any way i could remove records with the behavior below

Origin , Dest
A , B
B , A

For instance from the example above, i want to only retain one of the records since record (A, B) or record (B, A) means the same.

View 3 Replies View Related

Forms :: Oracle 10g Key Combination To Call Form Trigger

Nov 11, 2009

I need to fire the KEY-DUPREC trigger as I click on the key combination Shift+P therefore; I added the following line of code in the frmweb.res file.

80 : 1 : "Shift+P" : 64 : "Duplicate Record"

It worked iff no error was raised. So, if I have a raise form trigger failure in the KEY-DUPREC trigger, a capital P will appear in the text field that called the trigger.

View 2 Replies View Related

SQL & PL/SQL :: List All Tables In Database With Particular Record Values Combination

Jul 1, 2013

I have 3 tables in the Oracle database( emp, employee, emp1) which has following record values in it.

empidenamejob
7369, 'SMITH', 'CLERK'

I would like to list these 3 tables thru SQL/PLSQL, having the above record values combination. Also, the name of the columns could be different in all the tables i.e. name could be 'ename' in Emp table , and 'name' in Employee table. Is there way to do this in SQL or PLSQL ?

View 3 Replies View Related

Forms :: List Item Values Showing With ALT + DOWN Key Combination

Jan 9, 2013

In my forms(version 9i), list item values showing with ALT + DOWN key combination but I want to change it to only DOWN key.I search this combination in FMRWEB.RES file but there isn't any entry of ALT + DOWN.

View 1 Replies View Related

SQL & PL/SQL :: To_char Function In Combination With Distinct Clause - ORA-00936 / Missing Expression

Mar 2, 2012

I have a very simple table with 2 columsn. As_of_date is one of the column. This column is "Date" data type.

When I use distinct clause inside a to_char function it gives the following error:

ORA-00936: missing expression
00936. 00000 - "missing expression"

The Sql is

select to_char(distinct(as_of_date),'mm-dd-yyyy') from sales

I can't see any syntax error in the sql..but forsome reason, it doesn't work.

View 2 Replies View Related

SQL & PL/SQL :: Splitting 1 Output Row Into 3 Output Rows

Sep 25, 2013

Currently I have a requirement where I need to create 2 more output rows using each result row.

In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.

--Create tables charges
create table charges
(
charge_type varchar2(10) ,
charge number
);

[Code]....

My expected output should be like below:

Item_type amount
-------------------- ----------
charge1 100
Charge1_tax1 10
Charge1_tax2 20
charge2 200
Charge2_tax1 20
Charge2_tax2 40

how I can achieve the expected output using a single sql query

View 6 Replies View Related

SQL & PL/SQL :: Use Reg-Exp To Get Required Output?

Mar 11, 2013

Following query is giving me the required output but i want to use reg_exp from same output.

SELECT TO_NUMBER ((SUBSTR (narr,
INSTR (narr, '#') + 1,
(INSTR (narr, 'DT')) - (INSTR (narr, '#') + 1)
) "Bill No"
FROM (SELECT '25 NOS. BILL BOOK FOR FAISALABAD @80/- TH A.R. PRINTER AGST BILL#21 DT:31-01-2013 TH

[code]...

Required Output using Reg_Exp

SQL>/
Bill No
----------
21
9

View 6 Replies View Related

SQL & PL/SQL :: Not Getting Output For Other Schemas?

Feb 2, 2010

One function returns table metadata in xml form. This works for current schema but not for other schemas. Is it a privilege problem?

my code is..

CREATE OR REPLACE FUNCTION TEST.F_DBEG2
RETURN XMLTYPE
AS
Handle NUMBER;
V_OUTPUT_TOTAL XMLTYPE;
V_CNT NUMBER := 0;
BEGIN

[code]....

but when i select the object of another schema, got output.

SELECT * FROM TEST_NEW.DEPT;

Which privilege i need?

View 9 Replies View Related

Forms :: Different Output In 6i And PL/SQL

Jan 12, 2012

Here i have one PL/SQL block which will returns the age of an employee using his id.

SET SERVEROUTPUT ON;
DECLARE
v_num NUMBER;
v_days NUMBER;
[code]...

It will returns an output of 27..I tried the same in Forms 6i using a text field and a button with a trigger "when_button_pressed". when i am entering the same id of employee i am getting a totally different answer

DECLARE
v_num NUMBER;
v_days NUMBER;
BEGIN
SELECT to_date(sysdate)-to_date(dob)
INTO v_num
FROM customer_details
WHERE application_id=:block3.day;
v_days :=floor(v_num/366);
MESSAGE(v_days);
MESSAGE(v_days);
END;

It is giving me a result of -73...i cleared my problem. but i cound not understand the internal work happening inside the forms and pl sql machine.

View 10 Replies View Related

Output Should Starts As Between?

Jun 13, 2013

percentage_pointsmarksage95336.590346.575376.550496.595347903777549750337 if i fired the statementselect percentage_points from table1 where marks=33 and age=6.5 then output should beuser has score  percentage between 90 and 95i can write case condition between 1 to 100 percentages but any other condition which easy 

View 1 Replies View Related

PL/SQL :: No Output From Procedure

Dec 4, 2012

When I run the below procedure it does not return any data. But it returns stating Procedure created. When I just run the query it returns the result.

So what am I going wrong here.

set serveroutput on
CREATE OR REPLACE PROCEDURE PROD.STATUS_COUNTS
(
p_count IN OUT number,
p_status IN OUT varchar2,
p_mpp IN OUT number
)
AS
BEGIN

[Code]....

View 7 Replies View Related

PL/SQL :: Frequency In Output

Jun 18, 2012

I have created scripts as follow

CREATE TABLE STUDENTS(
  STD_ID Number,
  STD_NAME Varchar2(25 ) CONSTRAINT SYS_C002716 NOT NULL,
  PHONE_NUMBER Char(20 ),
  FATHER_ADDRESS Varchar2(100 ),

[Code]...

-- Add keys for table STUDENTS

ALTER TABLE STUDENTS ADD CONSTRAINT SYS_C002717 PRIMARY KEY (STD_ID)
/

-- Table CLASSES

CREATE TABLE CLASSES(
  CLASS_NAME Char(40 ),
  CLSS_NUM Number(38,0) CONSTRAINT SYS_C009724 NOT NULL,
  LEV_ID Number(38,0)
)

[Code]....

------------------------- ----------
abk                                amr
saad                              amr
abk                               OSM
saad                              OSM

I expected the query gives me student_name in what class name and didn't expect to get frequency in output

View 2 Replies View Related

PL/SQL :: Output Print More Than 255

Jan 2, 2013

I am getting an error while printing a sql query which is in a string inside a stored proc. I used

dbms_output.enable(1000000);

dbms_output.put_line(v_query);

and error "I get ORU-10028: line length overflow, limit of 255 bytes per line"

How do I print everything?

View 3 Replies View Related

Concatenation Of Fields For Output

Mar 15, 2007

I have the following fields:

Addressln1
Addressln2
Suburb
Town

I know how to concatenate them

trim(Addressln1) || ',' || trim(Addressln2) || ',' || trim(Suburb) || ',' || trim(Town) as Address

1. I would like to know if any of the fields are empty I would like to eliminate the comma character from the string.
2. Can I replace the comma with a new line character and what character to be used in the syntax.

View 2 Replies View Related

SQL & PL/SQL :: Pivot Output In Equijoin

Feb 11, 2013

I am joining two oracle provided basic tables emp and dept and want to show result as a department detail and followed by employee detail belong to that.

Standard result of joining two tables looks like below:

deptno dname empname
10 ACCT SCOTT
10 ACCT MILLER
20 SALES JOHN
20 SALES XYZ
30 FINANCE AAA

Now I need the output as below as per a report requirement.
entity_type name/no
DEPT 10
EMP SCOTT
EMP MILLER
DEPT 20
EMP JOHN
EMP XYZ

I am using oracle 10g release 10.2.0.1.0. Can I use oracle analytic function here?

View 2 Replies View Related

Groupby Query - Not Getting Output?

Jun 25, 2013

I have a table with data as follows

Source name address city
File Y N N
File N N Y
DB Y N Y
DB N Y N
XML Y Y N

I am trying to get output as follows

Source Y/N CountName CountAddress
File Y 1 0
File N 1 2
DB Y 1 1
DB N 1 1
XML Y 1 1
XML N 0 0

View 2 Replies View Related

How To Output Parameter To App C# Procedure PL/SQL

Sep 17, 2013

this PROCEDURE for Paging.

PROCEDURE cursor_example
IS
p_id NUMBER;
p_status number;
p_rownum number;

[code]...

View 1 Replies View Related

SQL & PL/SQL :: To Get Output From Table - Possible To Combine A Row?

Mar 6, 2012

To get output from a below mentioned table? can we combine a row like that?

Table: Test_Fruits

ID_NO_1 LABELS1 ID_NO_2 LABELS2
------- -------- ------- --------
1Fruit
2Vegitable
4 Apple
3 Potato
-----------------------------------------
Expecting output:
Pls guid me how to get like this?
ID_NO_1 LABELS1 ID_NO_2 LABELS2
------- -------- ------- --------
1Fruit 4 Apple
2Vegitable 3 Potato
-----------------------------------------

View 6 Replies View Related







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