SQL & PL/SQL :: Query To Retrieve The Data Without Iteration?

Dec 27, 2010

I have one table as shown below,

CREATE TABLE MAT
(matrl varchar2(100),
date_man date,
weight number(10)
);

INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat1','12-DEC-10',100);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat2','13-DEC-10',200);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat3','21-DEC-10',300);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat4','26-DEC-10',400);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat5','22-DEC-10',500);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat6','02-DEC-10',600);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat7','23-DEC-10',700);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat8','07-DEC-10',800);

The data will look like this .

matrldate_manWeight
mat112-Dec-10100
mat213-Dec-10200
mat321-Dec-10300
mat426-Dec-10400
mat522-Dec-10500
mat602-Dec-10600
mat723-Dec-10700
mat807-Dec-10800

I need to order by this data in terms of date and need to get the data as shown below. Acc_wt is accumulated weight till the material.

Date Weightacc_wt
mat602-Dec-106000
mat807-Dec-10800600
mat112-Dec-101001400
mat213-Dec-102001500
mat321-Dec-103001700
mat522-Dec-105002000
mat723-Dec-107002500
mat426-Dec-104003200

Can we make use of oracle lag and lead functions here?Or some other methods?

View 6 Replies


ADVERTISEMENT

Data Guard :: ORA-24333 - Zero Iteration Count

Dec 4, 2008

Error@ ORA-24333 - Zero Iteration Count.

Here is the program,..for my report, Strangely I am able to run the query Successfully for my report in SQL developer and report builder and I can even give a bind parameters using both , but the requirement for this report is I should ether run this report in discoverer or xml For that I have successfully created a report in report builder and when I create a Concurrent Program and Run It,… I get an error ORA-24333 - Zero Iteration Count

=> with Tender As ( Select F1.Store_code as Store_Code, ……

With Tenders As ( Select F1.Store_code AS Store_code,
F2.Tender_type_code AS Tender_type_code,
Sum(F2.Payment_amount) As Trans_amount,
Count(F2.trx_id) as Trans_count
From ors_transactions F1

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Find Maximum Iteration In Table And Change Data

Jul 27, 2011

i have inner query result like

Emp status
----- ----------
ram a
ram a
ram b
ram a

i want write a query to find a maximum occurrence of status and update in status column.

result should be

Emp status
----- ----------
ram a
ram a
ram a
ram a

i tried lot of thing but not to go.

View 2 Replies View Related

TimesTen In-Memory :: Query To Retrieve Previous Day Data

Jun 13, 2012

I would like to have a query which should fetch previous day records from column which is having timestamp data type.

select mdn from user_table where updatetimestamp > trunc(sysdate) - INTErVAL '24' HOUR;

But this gives output not for previous day, but all records which are 24 hrs less than current day. How to get records for previous day based on column having timestamp data type.

View 4 Replies View Related

SQL & PL/SQL :: Query To Retrieve Value In Specified Schema

Jan 6, 2011

I need a query to retrieve a particular string from all the columns and all tables in a specified schema.

For example:i had a value "Current" in a particular column in a particular table. i need to find this value if i don't know the table name and column name.

View 2 Replies View Related

SQL & PL/SQL :: Join Query - Retrieve Last Or First Rank Row?

Dec 2, 2010

the following statement gives each customer owns how many promotions.

Q:) how do i retrieve a customer who has max. promotions?

SELECT C.CUSTOMER_ID,COUNT(P.PROMOTION_ID)
FROM PROMOTIONS P,CUSTOMERS C
WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID
=====================================================
CUSTOMER_ID COUNT(P.PROMOTION_ID)
-------------------------------------
001 | 5
002 | 8
003 | 4
004 | 6
005 | 5
006 | 3

View 7 Replies View Related

An Iteration Count Of Zero Specified For Statement Action - Specify Number Of Times?

Aug 2, 2013

Get the following error from Oracle  ORA-24333: zero iteration count Cause: An iteration count of zero was specified for the statementAction: Specify the number of times this statement must be executed on whether is this a Oracle bug or if not what should be set to avoid this failure.

View 2 Replies View Related

SQL & PL/SQL :: Query To Retrieve Records In Frequency Of Days?

Dec 18, 2012

I have a following requirement in SQL -

Requirement -

I need to list the purchase Ordrers which are not closed.

my sql query should pull if the PO is not closed in 30,90 and 150 days.

It should be shown only on 3oth,90th and 150th day only even the Purchase order is not closed on 33rd day.

View 1 Replies View Related

Reports & Discoverer :: How To Execute Query 2 If 1 Doesn't Retrieve Anything

Dec 3, 2010

In oracle report how do we execute query2 if query1 doesn't retrieved any records.

View 4 Replies View Related

SQL & PL/SQL :: Query To Retrieve Number Of Not Null Rows For Each Column?

Jul 7, 2011

I need a generic query to generate total # of records for each table in a schema, total # of records that are not null for each column in the table, and total # of records that are null for each of those columns in those tables.

ex:

the output should look like this.

owner schema table_name total# recs in the table, column_name,
------ ------ ---------- ------------------------- -----------

# of records not null # of records null
---------------------- --------------------

View 12 Replies View Related

SQL & PL/SQL :: Build Query To Retrieve Records Between Dates With Same Field?

Mar 17, 2010

i have a doubt in building a query.

I have a table with fields

job_no activity Date
101 anchorage 20/01/2010
102 berthing 25/01/2010
103 sailing 29/01/2010

If i want to know the status of the ship on the date '22/01/2010' It has to show as 'anchorage', becoz on '25/01/2010' only it came to berthing from anchorage. How to write a query to achieve this.

View 12 Replies View Related

SQL & PL/SQL :: Query To Retrieve List Of Order_id Which Has No Records In Database

Jul 20, 2010

Its a simple query to retrieve data with the Order_ID.If the record is there its fine and can retrieve the data. But my query is to retrieve the list of order_id which has no records in database.

View 12 Replies View Related

PL/SQL :: How To Retrieve The Data In Where Clause 

Dec 28, 2012

SELECT mkct_group_code FROM ID_MKT_CONTTYPE_GROUP_MASTER where mkct_group_code=20'

Note mkct_group_code column is varchar2(10) type.

It has values as
20
10
30
20'
30'

View 3 Replies View Related

Retrieve Data From A Table For Certain IDs

Apr 7, 2013

I am writing a SQL inside an application to retrieve data from a table for certain IDs or for All employees based on the user's input. I create a variable :SELECTION for the where clause. When I run the SQL, I receive the error of missing right parenthesis. There is no error if I hard code the IDs.

If the input is 'All' -> the variable :SELECTION is blank so if the SQL :

select name, address, ID from employee :SELECTION

will be

select name, address, ID from employee

If the input of users ID as '1234','9999' - > the :SELECTION = ' where ID in (:VARIABLEID)'
:VARIABLEID = '1234','9999'

so the SQL:
select name, address, ID from employee :SELECTION

should be

select name, address, ID from employee where ID in ('1234','9999')

However, I got the missing right parenthesis error. I suspect it is something with the single quote, so I tried different ways, but it is still not working.

View 1 Replies View Related

Fast Way To Retrieve All Data Blocks

Apr 19, 2012

I am interested about the fast way to access all data in physical block. what is the quick way to bring data blocks using the rowid, I found this script but soon as I can have faster access:

select * from table_name t
WHERE ROWID between 'AAAUaOAAEAAHkJiAAA' and 'AAAUaOAAEAAHkJiAA8';
where 'AAAUaOAAEAAHkJiAAA' is the last element in the block and 'AAAUaOAAEAAHkJiAA8' is the first one

my question is can retrieve all the data in one block more quick than this query.

View 5 Replies View Related

SQL & PL/SQL :: Retrieve Hierarchical Data From Same Table

Nov 10, 2011

Look into the below table:

TABLE :- EMPLOYEE
________________________
| ID | SUPERVISOR |
|_______________________|
| A101 | B102 |
|________|______________|
| | |
| B102 | C104 |
|________|______________|
| | |
| C104 | D108 |
|________|______________|
| | |
| D108 | E104 |
|________|______________|

Here B102 is supervisor of A101 and C104 is supervisor of B102 and so on. I want to get this data into new table in below format

TABLE :- Hierarchy
________________________________________________________________
| ID |SUPERVISOR_1 |SUPERVISOR_2 |SUPERVISOR_3 |SUPERVISOR_4|
|______________________|_____________|_____________|____________|
| A101 | B102 | C104 | D108 | E104 |
|________|_____________|_____________|_____________|____________|
| | | | | |
| B102 | C104 | D108 | E104 | NULL |
|________|_____________|_____________|_____________|____________|
| | | | | |
| C104 | D108 | E104 | NULL | NULL |
|________|_____________|_____________|_____________|____________|
| | | | | |
| D108 | E104 | NULL | NULL | NULL |
|________|_____________|_____________|_____________|____________|
| | |
| E104 | NULL | NULL NULL NULL
|________|_____________|_____________|_____________|____________|

I want to insert 1st two rows into Hierarchy table, then I would like to update Supervisor_2 to Supervisor_4. Here I don't want to use 'CONNECT BY PRIOR', as it take more time to execute (there are millions of records).SQL code for same.

View 7 Replies View Related

SQL & PL/SQL :: How To Retrieve Data That Does Not Exists In A Table

Dec 13, 2011

I have the following table with the some sample data:

|| Filename || receiving_site || originating_site ||
| fileA | siteA | HQ |
| fileA | siteA | siteA |
| fileB | siteA | HQ |
| fileA | siteB | HQ |
| fileA | siteB | siteB |
| fileB | siteB | HQ |
| fileB | siteB | siteB |

The table works in such way:

1. For each of the Filename, if originating_site is not the same as receiving_site, it means that the file has been sent to receiving_site but has not been acknowledged received yet.

2. For each of the Filename, if originating_site is the same as receiving_site, it means the file has been sent and received by the receiving_site.

My task is to list out all the Filename per receiving_site that has been sent, but not received yet by the receiving_site. For example from the sample data above, I am expecting to see that siteA, fileB has not been received yet.

How can I do that? I had tried MINUS and NOT EXISTS command, but I am just not able to get the result that I want.

View 2 Replies View Related

SQL & PL/SQL :: How To Retrieve 6 Decimal Place Of Data

Oct 14, 2011

IF I have Salary Table,In that Salary Table,How to retrieve 6 decimal place of data .

View 16 Replies View Related

SQL & PL/SQL :: Retrieve Data From Blob Column?

Sep 30, 2011

example for retrieve data from blob column using pl sql procedure in oracle?

View 2 Replies View Related

SQL & PL/SQL :: Retrieve Subset Of Data From Table

Jan 20, 2012

How to retrieve first hundred records from a table ?

FYI
---

The table size is 5 GB
The table count is 127922653
Table has 14 columns

Table is partitioned as well.

The table has 10 partitions.

View 17 Replies View Related

Forms :: Using LOV On Some Field To Retrieve Data

Jun 8, 2011

I am using LOV on some field to retrieve data, but sometimes it does not retrieve anything cause of unavailability of date its fine but i want to put N/A in that field when such condition is occurring .

View 2 Replies View Related

PL/SQL :: Retrieve XML Data In Clob Type?

Dec 5, 2012

I have a table created as below

create table tab123( x clob);

Inserted a row as below

insert into t values ('"<ProcessBatchRequest xmlns:inp1="http://services.abc.com/" ServiceVersionNumber="" xmlns="http://services.abc.com/GlobalBatchServiceV1.0">
<inp1:BatchDetail>
<inp1:ApplicationID>test.123</inp1:ApplicationID>
<inp1:ApplicationBatchID>test.123</inp1:ApplicationBatchID>
<inp1:MessageTimeStamp>2012-11-28T11:05:32.000-05:00</inp1:MessageTimeStamp>

[code]....

Now i want to retrieve the value present under a given xpath.

View 7 Replies View Related

SQL & PL/SQL :: Retrieve Data By Checking Any Part Of The String?

Jul 8, 2012

I have a table

select * from testing
Quote:
ID NAME REDATA
-------------------- -------------------- --------------------
1 ABC,DEF,GHI,LKJ 000001
2 MNC,GHI,CTF 000002

2 rows selected

select * from testing where name like ('GHI,TCF');

now my output should be as above because GHI is common in both the names.if any of the where condition string matches the Name field then I need that record to be retrieved.

View 9 Replies View Related

Forms :: How To Retrieve Only That Data Which Is Based On Given Condition

Jun 22, 2011

,i have a table called travel detail from which i have retrieved few rows based on 1 condition by using LOV and on next few rows modification is allowed. The rows are getting modified but it is also allowing me to enter in next blank row and insert any data in that row.how can i retrieve only that data which is based on the given condition. Further blank rows should not b displayed.

View 2 Replies View Related

SQL & PL/SQL :: Retrieve Data With Multiple Select Statements

Jun 3, 2012

I'm trying to retrieve data with multiple select statements. The query works fine for 1 account (segment6) but fails for more than account. Below is the query.

select PERIOD_NAME "Month/Year",segment1 "Company" ,Segment6 "GL Account", currency_code "Currency",Mmt "Movement",
BEGIN_BALANCE+PERIOD_DR-PERIOD_CR "Balance At Date"
FROM
(select gb.period_name "PERIOD_NAME",decode (gcc.segment1, '20', 'Internode', '10', 'Agile') SEGMENT1,
gcc.segment6 "SEGMENT6", gb.currency_code "CURRENCY_CODE", gb.begin_balance_dr "BBDR", gb.begin_balance_cr "BBCR",

[code].....

View 3 Replies View Related

Forms :: Fetching Data - Retrieve Username

Sep 7, 2010

I have a detailed block with a user ID column. The user name is not available in the block. Therefore, I have created a non-database column to retrieve the user name into it.

Here is the code I've used.

PROCEDURE get_details IS

iLoop number := 1;

CURSOR c is

SELECT FULL_NAME
FROM GRP_EMPLOYEE
WHERE EMPLOYEE_NUMBER = :USER_ID;

[Code]...

However, it retrieves the first record only.

The results are displayed like this.

Loop: Result:

1 Full_Name
2 Empty
3 Empty
4 Empty

View 2 Replies View Related

PL/SQL :: Retrieve Data By Using Both Group By And Order By Clauses?

Nov 16, 2012

I have a table name as angdata77 having attributes like asigno..i want to retrieve data from angdata77 by using both group by & order by clauses.. for total count..am using the query as

select asigno,count(*) from angdata77 group by asigno order by asigno;

Is there any other query for retrieving the data from angdata77

View 5 Replies View Related

Table Locking When Many Users Retrieve Data

Sep 2, 2009

I have a "select ... into" in my PL/SQL and it doesn't retreive any data(I suppose just certain columns because if it doesn't retreive a row then the PL/SQL should throw an error) when there are many users accessing this table. It shouldn't be some table locking right? Because if it is, it should wait until the table is available then it will retreive data. Currently it just proceeds with the PL/SQL and selecting nothing in that query. what details should I look at to trace the cause of this problem.

View 4 Replies View Related

Security :: How To Retrieve Unmatched Data From A Column

May 30, 2013

i have a column that contains 1 to 100 values but it has some missed data such as it does not has 50,45,34,23 etc..

so how could i find the missed data alone in a single query..

View 4 Replies View Related

How To Connect To Oracle To Retrieve Data Using SQL Language

Jan 9, 2012

how to connect to Oracle to retrieve data using SQL language. I've started with a new company which is pretty much a sink or swim type of environment meaning you're an idiot if you don't figure it out on your own. I was told that we use Toad for Oracle 10g. I was given a server name; location and an .ora file named tnsnames that was given to me but I have no clue what to do with. I've come from companies where this was already setup. You just go into access to retrieve the data.

II have totally hit a brick wall with all the other roadblocks I encounter and all researched out. Where I can get step by step instructions on how to connect to start sql in Toad/Oracle?

View 5 Replies View Related







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