PL/SQL :: SQL To Sum A Column While Avoiding Repeating Values

Jan 31, 2013

I have these records in a table:

CODE AMOUNT DESCRIPTION
AAA 5 five dollars for pizza
AAA 2 two dollars for tips
AAA 1 one dollar for dogsitting
BBB 6 six dollars for babysitting
BBB 1 one dollar for tips

My goal is to list all records, "grouping" by code, with sum(amount), but the final display has to show all descriptions, one for row, avoiding to repeat the "CODE" column and "sum(AMOUNT)" column.The result should be like this:

CODE SUM(AMOUNT) DESCRIPTION
AAA 8 five dollars for pizza
two dollars for tips
one dollar for dogsitting
BBB 7 six dollars for babysitting
one dollar for tips

That is, the "CODE" is displayed only the first row, with its sum of "amount".I think I have to use the analytics functions, but I was a little stuck.

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Select Only Rows Where Certain Column Repeating Values

Mar 6, 2012

I am trying to come up with a sql select statement that provides all rows for employees with 2 or more cities.

with sample_table as (
select 'John' name,'city' ValueType,'Toronto' Value from dual union all
select 'John' name,'city' ValueType,'Vancouver' Value from dual union all
select 'Susan' name,'city' ValueType,'Toronto' Value from dual union all
select 'Susan' name,'city' ValueType,'Seattle' Value from dual union all
select 'Susan' name,'age' ValueType,30 Value from dual union all
select 'Susan' name,'city' ValueType,'Atlanta' Value from dual union all

[Code]...

NAME VALUETYPE VALUE
----------- ------------- ------------
John City Toronto
John City Vancouver
Susan City Toronto
Susan City Seattle
Susan Age 30
Susan City Atlanta
David City Chicago
David age 35
David Status married
David City Dallas

The above code is just to describe the sample table and the desired result set. Please note that Mary is not on the result set since she has no city assigned to her. Also Julia is not on the result set since she only has one city assigned to her. The others are there because they had at least 2 cities assigned to them.

I need the sql syntax that would return this result set.

View 6 Replies View Related

PL/SQL :: Append Values If Repeating Same Name?

Jan 4, 2013

if the same name repeating it should to append with _1 and _2 until same name reached.

select 'fname' name from dual
union all
select 'lname' name from dual
union all
select 'email' name from dual
union all
select 'fname' name from dual
union all
select 'fname' name from dualmy output should be like below...

fname
lname
email
fname_1
fname_2

View 11 Replies View Related

SQL & PL/SQL :: Mark Repeating Values As Blank

Jun 1, 2010

I have a result set with the following structure

Column 1 Column 2 Column 3 Column 4
--------- --------- --------- ---------
A1 B1 10 50
A1 B1 20 50
A1 B2 30 40

But i want to restructure this result like below,

Column 1 Column 2 Column 3 Column 4
--------- --------- --------- ---------
A1 B1 10 50
20
B2 30 40

I am just trying to change the repeating values on a certain combination as blank.

View 5 Replies View Related

Spatial :: Select Repeating Values

Sep 7, 2012

some sample data in my point geometry table.

every POLYID has two rows with NAME value, i need to select the two rows if NAME is same for a given POLYID.

example: POLYID 4351 has same name N, then i need to select two rows with PILYID 4351.

POLYID POINTID           NAME
-----------------------------------------------------------------
4348 5763           N
4348 5764           F
4351 5741           N
4351 5756           N
4367 5721           M7

[Code]....

View 6 Replies View Related

Reports & Discoverer :: Column Headers Repeating In Oracle Reports 10g

Mar 19, 2013

I'm using Report Builder 9.0.4.0.33.

I am creating a report which generates the data to a PDF/CSV based on users Input.When I create the report using Report Builder and execute the report,Delimited data appears in a PDF layout.

But if I add the below code in After Parameter Form trigger,the column headers keeps repeating with the data.

function AfterPForm return boolean is
begin
IF :P_DESTYPE = 'CSV' THEN
:MODE :='DEFAULT';
:DESFORMAT:='DELIMITED';
Elsif :P_DESTYPE = 'PDF' THEN
:DESTYPE := 'CACHE';
:DESFORMAT:='PDF';
END IF;
return (TRUE);
end;
[code]....

But the PDF output seems fine where the header is shown only once.I could see many posts regarding this in Oracle reports 6i wherein delimited_hdr=no is used in Command Line but I would like to know the work around in Oracle reports 10g.

View 3 Replies View Related

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

Dec 23, 2010

My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.

The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.

fixing the errors or if there is any new logic that can be implemented.

DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)

[Code]...

View 9 Replies View Related

SQL & PL/SQL :: Avoiding Space

Jan 5, 2012

I have table like below.

Name Gender
----- ------
f1 Female
f2 Female
m1 Male
m2 Male
f3 Female
m3 Male
m4 Male

but I need the output like below

Male Female
----- ------
m1 f1
m2 f2
m3 f3
m4

I have tried to get the above O/p but getting along with null values.

SQL> ed
Wrote file afiedt.buf

1 select case when gender='male' then name end male,
2 case when gender='female' then name end female
3* from details s1
4 /

MALE FEMALE
--------------- ---------------
f1
f2
m1
m2
f3
m3
m4

7 rows selected.

how the get the above o/p with out null values.

View 9 Replies View Related

SQL & PL/SQL :: Avoiding Insert Into Table

Nov 21, 2010

I've got situation where one of the procedure causing insert into table rows but some of them are not needed Temporary I need to disable this and exclude some type of data being inserted by changing procedure.

I wouldn't want to change the procedure is there a way to write a trigger that would not allow to insert a row into a table if condition is met? Something of a logic:

CREATE OR REPLACE TRIGGER avoid_insert
BEFORE (AFTER?) INSERT
if entity_id = 123 DO NOT DO THE INSERT (DO NOT DO ANYTHING)

View 6 Replies View Related

SQL & PL/SQL :: Joining But Avoiding Duplicate Rows

Oct 16, 2010

I have two tables A and B

CREATE TABLE A(EMP_ID NUMBER, EMP_NAME VARCHAR2(100))
CREATE TABLE B(EMP_ID NUMBER, EMP_ATT1 VARCHAR2(10), EMP_ATT2 VARCHAR2(10))
INSERT INTO A VALUES(1, 'ONE');
INSERT INTO A VALUES(2, 'TWO');
INSERT INTO A VALUES(3, 'THREE');

[Code]....

This query returns all the matching row of A and B

SELECT A.EMP_ID, A.EMP_NAME, B.EMP_ATT1, B.EMP_ATT2
FROM A
INNER JOIN B ON A.EMP_ID=B.EMP_ID

The output for this shows:

EMP_ID EMP_NAME EMP_ATT1 EMP_ATT2
1 ONE 1ATT1 1ATT2
2 TWO 2ATT1 2ATT2
2 TWO 2ATT1.1 2ATT2.1
3 THREE 3ATT1 3ATT2

The requirement is to avoid duplicate rows even if matched:

EMP_ID EMP_NAME EMP_ATT1 EMP_ATT2
1 ONE 1ATT1 1ATT2
2 TWO 2ATT1 2ATT2
3 THREE 3ATT1 3ATT2

View 8 Replies View Related

PL/SQL :: How To Fetch Values From Two Columns Using Values From One Column

Jul 25, 2013

From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?

View 2 Replies View Related

SQL & PL/SQL :: Avoiding Repetition Of Condition In Not Exist Clause?

Dec 7, 2010

Given two tables in a 1:N relationship, I want to retrieve, for each parent row, any child row which either satisfies some condition cond1 or, if no child rows matching cond1 exist, which satisfies another condition cond2.

I can easily write such a query using NOT EXISTS, but I am forced to repeat the condition cond1. This is inconvenient, as the repeated condition is quite long (involving other tables as well), and I would like to write it only once.

For example (using the HR schema included in Oracle 10g XE): I want to retrieve, for each department, all the "new" employees (hired after a certain date) or, if no "new" employees exist, the department manager.

SELECT d.department_id,
d.department_name,
d.manager_id,
e.employee_id,
e.first_name,
e.last_name,

[code].....

View 3 Replies View Related

Application Express :: Hyperlink Column With Link Parameters As Region Column Values

Apr 10, 2013

On my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.

I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.

So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:

a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?

The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.

---

"Application Express 4.2.1.00.08"

View 2 Replies View Related

Application Express :: Can Pass More Than 3 Column Values With A Column Link

Jun 19, 2012

I want to pass multiple column values of a row in an interactive report page to hidden items in another page through column link. And I did it successfully. However, I found I need to pass more than 3 columns of a row in this report, while a column link only permits me to pass 3 column value at most. Is there anyway that I can pass more column values to hidden items in another page?

View 3 Replies View Related

SQL & PL/SQL :: How To Convert Multiple Column Values Into Single Column

Jul 19, 2013

CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......

actual output of the below query, but i want to display in different way

select * from type;

C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E

The expected output should be like this below, how to write a query or which built in function used to get the below result,

Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M

View 4 Replies View Related

Performance Tuning :: Avoiding Bind Variable From Sql In Java Code?

Jun 15, 2010

We are on oracle 10.2.0.4 on Solaris 10 and have a perf. issue with a bind variable using query. The query is in java application. I want to test its performance when the query doesn't use bind variable and instead uses the passed value as literal. How can it be done?

As example lets say the query is:

SQL> variable vn varchar2(20);
SQL> EXEC :vn :='ADAMS';

PL/SQL procedure successfully completed.

SQL> select * from emp where ename=:vn;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83 1100 20

1 row selected.

SQL> EXEC :vn :='KING';

PL/SQL procedure successfully completed.

SQL> select * from emp where ename=:vn;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10

1 row selected.

But the statements will be taken as similar statements by oracle (due to :vn). Now I want oracle to take it as literal and the change for this has to be done in java code in my actual scenario which has a different query (but conceptually it uses bind variable and I want it to use passed value as literal). How can it be done?

View 9 Replies View Related

SQL & PL/SQL :: Column Values To Column Names

May 20, 2011

I had a table( T1). Five columns exists in the table. The table data is like below

C1C2C3 C4C5
--- ----- ---- ----- ----
JAN FEB MAR APR MAY
One Two Three Four Five

I need the output like below one

JANFEBMARAPRMAY
---- ----- ----- ---- ----
OneTwoThreeFourFive

View 13 Replies View Related

SQL & PL/SQL :: Replace Repeating The Same Description With -DO-

Dec 19, 2012

I am looking for oracle query to replace repeating description with -DO-. Sample Data is:

CREATE TABLE SCOTT.ITAX
(
VDATE DATE,

[Code].....

VDATE DESCRIPTION ITAX AMOUNT
--------- -------------------------------------------------- ---------- ----------
14-NOV-12 CANOLA OIL 3 3500
25-NOV-12 CANOLA OIL 3 2500
10-DEC-12 CANOLA OIL 3 3300
01-NOV-12 CANOLA SEES 3 5600
10-NOV-12 CANOLA SEES 3 5500
01-DEC-12 CANOLA SEES 3 5400

6 rows selected.

Required Output is:

VDATE DESCRIPTION ITAX AMOUNT
--------- -------------------------------------------------- ---------- ----------
14-NOV-12 CANOLA OIL 3 3500
25-NOV-12 -DO- 3 2500
10-DEC-12 -DO- 3 3300
01-NOV-12 CANOLA SEES 3 5600
10-NOV-12 -DO- 3 5500
01-DEC-12 -DO- 3 5400

View 2 Replies View Related

SQL & PL/SQL :: Remove Repeating Duplicates

Aug 3, 2011

i have two tables

Quote:
Table has following data.

id indicator
---------------
1 A
2 A
3 A

Quote:
Table2 has

id indicator
---------------
1 X
1 X
2 X
3 X

I would like to have following output ( Am running query on Toad)

t1_id t1_indicator t2_id t2_indicator
---------------------------------------------
1 A 1 X
1 X
2 A 2 X
3 A 3 X

View 4 Replies View Related

SQL & PL/SQL :: Inserting Data In Repeating Tables

Apr 19, 2011

How to Insert the data in Repeating Tables. I mean Suppose One Student has many Addresses like home,office,permanent etc.

There is one column in this table sequence_no. while Inserting a record how to insert this sequence no I don't know It maintains unique sequence no for each student.

If student has 3 addresses then Its seq no is 3.

I am inserting through a procedure where multiple students data is to be inserted.

how to take care of this sequence no.

View 15 Replies View Related

SQL & PL/SQL :: Same Sequence Number For Repeating Data

Feb 21, 2013

I want to insert same sequence number for repeated data else new sequence.

my data is as below

EMP_NAMEEMP_INV

PrasCTI
PrasCTI
ShariqWitness
KateCTI
ShariqAssisted
ShariqWitness

the first 2 record are same Pras CTI and Pras CTI so i want my sequence generate value once as below

1 Pras CTI
1 Pras CTI
2 Shariq Witness
3 Kate CTI
4 Shariq Assisted
2 Shariq Witness

Shariq and witness are repeating so same sequence number but Kate CTI occured once so nextval of sequence

How can i acivee it

CREATE TABLE EMP_INV
(
EMP_NAME VARCHAR2(100 BYTE),
EMP_INV VARCHAR2(100 BYTE)
)
Insert into EMP_INV
(EMP_NAME, EMP_INV)
Values
('Shariq', 'Witness');

[Code]...

View 12 Replies View Related

SQL & PL/SQL :: How To Find Any Character Is Repeating In A String

Sep 8, 2011

How to find whether any character in a string is repeating or not

Eg: '123LH563' should return 'YES' , as 3 is repating there
'1234567' sould return 'NO' as there is no character which is repeating

View 8 Replies View Related

SQL & PL/SQL :: Displaying Multiple Row Values As Multiple Column And Row Values

May 4, 2010

find the Test Case below.

--Creation of Table

create table tb1
(ID number(4),
event varchar2(20),
vdate date);

--Inserting Values into the Table.

INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')

[Code]...

--Selecting data from Table.

SELECT * FROM TB1;

ID EVENT VDATE
---------- -------------------- ---------
1 V1 01-JAN-09
1 V2 02-FEB-09
1 V3 04-MAR-09
1 V4 03-APR-09
1 V5 05-MAY-09
2 V1 01-JAN-10
2 V2 02-FEB-10
2 V3 04-MAR-10
2 V4 03-APR-10
2 V5 05-MAY-10

10 rows selected.

how can i display the data as below format using Oracle 9i SQL.

IDV1 V2 V3 V4 V5
--- ---------------- ------------ --------------- -------------- ------------
11-Jan-092-Feb-094-Mar-093-Apr-095-May-09
21-Jan-102-Feb-104-Mar-103-Apr-105-May-10

View 4 Replies View Related

SQL & PL/SQL :: Max Row Values Into Column

Oct 3, 2011

I have table output as shown below.

AID UCD U_TXT UDATE PID
116 1 Req Documents 01-OCT-2011 100
116 2 AGG APPR 01-OCT-2011 101
116 3 Docs received 02-oct-2011 102
116 1 Tmp received 02-oct-2011 103
117 2 Notice sent 03-oct-2011 104

UCD - We have total 19 codes (1 to 19), each can have multiple rows for one AID.. like 1 repeated twice for AID 116.
PID - Primary id (Primary key column)

Output I am looking
--------------------
AID COL1 COL1_TXT COL1_DATE COL2 COL2_TXT COL2_DATE..ETC
116 1 'Tmp received' 02-oct-2011 2 AGG APPR 01-OCT-2011
117 2 Notice sent 03-oct-2011

If the same UCD repeated multiple times then we should get the max(PID) record for that UCD and for that AID

I tried with group by AID,PID. but couldn't bring the rows to columns. I have attached the script with the post

View 2 Replies View Related

SQL & PL/SQL :: Column Values In Row

May 10, 2011

i've follow problem:

select x1
from table

and i get
a
b
c

What i want is
a,b,c

The main problem here is, that it can be
a
b

as such as
a
b
c
d
e
f

in detail, i don't know prior how many i will get. With declare a cursor is it easy, but i try to find one solution only with sql.

View 6 Replies View Related

SQL & PL/SQL :: Regex Delete Repeating Words In A String

Apr 5, 2011

I am trying to delete duplicate or repeating words in a string using regular expressions (regex) and an Oracle database.I have Googled quite a bit on this topic and was unable to track down something that fit the bill or remotely came close to paying the tip.

A pattern would be: word word start with newThe final string should look like this: word start with new Basically all the strings begin with the two words repeating.

View 7 Replies View Related

SQL & PL/SQL :: Control Sequence To Return Same Value For Repeating Data?

Feb 13, 2013

how can i control the sequence to return same value for repeating data.

inter_no

10
10
11
12
12
13
14

what is required is as below

inter_no seq_val

10 1
10 1
11 2
12 3
12 3
13 4
14 5

is is on select statement will will insert the data once i get the sequence val is same for repeating one

View 5 Replies View Related

Reports & Discoverer :: Skip The Repeating Frame?

Feb 23, 2011

I have a Requirement in the Report.

During the Report Runtime the Each Page Contain 55 Lines. Their as Lot of Item Groups are available in the Report Each Items Group have Maximum 10 Lines in the Report with Summary Contain in the Repeating Frame

My Requirement is During the Report Preview if the Report Not Fitted in the Page then Whole Item Group is Require to Skip to Next Page.

View 1 Replies View Related

Reports & Discoverer :: Many Graphs In Repeating Frame?

Jun 8, 2012

I have a graph inside a repeating frame, but every time a run the report, it doesn't bring me all de graph, generally skip the last one, for example i show one graph per location but never show me the last ones. What can i do? If I ask 4 location, it bring me 3, if I ask 5 it bring me 4.

View 4 Replies View Related

Get Values From Table Column Using PL/SQL Or Just Using SQL

Dec 3, 2008

I'm having a table which has a column which has values given inside square brackets.

[[[123412]]] ,[[[[werer34]]]],'[[qw2_w3rt]]

Now, the requirement is to get the values which are inside the innermost square brackets.Such as in

First case : 123412
Second one: werer34
Third : qw2_w3rt

Only the values inside are needed. Is there any way to achieve it using Pl-Sql or just using Sql ?

View 3 Replies View Related







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