SQL & PL/SQL :: Generate Hourly Based Number Of Orders Placed Report

Oct 11, 2011

I have a table called 'orders' and having date and time of order placed in the field timeplaced. I need to generate a report which is having hourly based number of orders placed . If no order is placed in an hour then it should show 0 for that hour in the result .

create table orders (order_id integer, timeplaced date,last_updated date);
Insert into ORDERS
(ORDER_ID,
TIMEPLACED, LAST_UPDATED)
Values
(1, TO_DATE('10/11/2011 12:53:39', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/11/2011 10:53:39', 'MM/DD/YYYY HH24:MI:SS'));
[code]........

But I need the result for all the 24 hours. Actually row for the hour 1,2,3,4,5,6,8,9,13-23 are not present in the DB

TO_CHAR(TIMEPLACED,'HH')COUNT(*)

00
10
20
30
40
50
60
71
80
90
102
111
122
130
140
150
160
170
180
190
200
210
220
230

View 3 Replies


ADVERTISEMENT

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

SQL & PL/SQL :: Generate Range Based On Start And End Number Dynamically?

Jul 14, 2011

I have following requirement. Let say i have to generate a range based on "start number" and "end number" dynamically.Some kind of hash buckets.

e.g Start Number : 1 and End Number : 1001.

Also i want to divide that range based on some dynamic value like for above example 10 ranges of 100 each. and both 1 and 1001 should be included only once and the next row start number cannot be same as previous row end number.

Means

Range 1 1 -101
Range 2 102-200
Range 3 201-300
...
Range 10 901-1001

Is there any way of doing it automatically. I tried with Model clause. it works fine for even cases but for odd i have issues also when i take small start and end number i get an error.

SELECT case when ranges=1 then ranges else ranges+1 end Start_Id, ranges+round((1001-1)/10) End_Id
from
(

[Code].....

I want this to be generic for any values here 1 is Start Number 1001 is end number and 10 is the bucket. I need these parameters dynamic and want's the same kind of results for any values.

View 22 Replies View Related

SQL & PL/SQL :: Generating Hourly Report From Oracle Database

Jun 30, 2011

I am working on SQL Developer 1.5.1, i need to prepare hourly record of the activity on the database, for that i have a sql query that gives me the report as per selected columns on hourly basis.I need to prepare 24 reports a day.

Each time i have to go to the query , change the date as per hours like form 22:06:2011 10:00:00 to 22:06:2011 11:00:00 and get the report and export it in excel.

I want to automate the script so that whenever i run the script , it just asks me the date and runs the script 24 times and fetch me the hourly report of whole day.

the query syntax is something like this

Select
............................
...........................
..........................
from
..........................
........................
........................
where
And.......................
And...................
And......................

And req date between to-date( 22:06:2011 10:00:00) And to-date(22:06:2011 11:00:00)
Order by 7,1,2,3,4,5

Is there any possibility that i can automate the script to automatically change the hour itself and generate a report in excel 24 times?

View 6 Replies View Related

Reports & Discoverer :: Report Fires Everyday For Cancelled Orders

Aug 8, 2011

I have a query in reports.My requirement is that report should fire everyday in a year.When an order is cancelled/copied and also that when an order is cancelled then the line amount should be set to zero and order_status should automatically set cancelled.

EX:
order_no amt product_name quote_no order_status
45434545 200 dell 10022 active
when order cancelled then
order_no amt product_name quote_no order_status
45434545 0 dell 10022 cancelled

View 2 Replies View Related

SQL & PL/SQL :: How To Generate Number (not Sequence Number) In Query

Mar 9, 2011

I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).

the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?

select customer_id from
(select customer_id from capitalPLAN
where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165')
order by price desc
)
where rownum <= 25

View 4 Replies View Related

How To Generate Email Based Trigger

May 9, 2011

I have a table in some db called retailer. I want to generate an email based trigger. What i want to achieve is as follows

I want to generate an email based trigger. What i want to do is that i should store the current retailer id which is until this point and then on each further insertions in retailer id i want that some trigger or script is running in background which is storing the retailer id. When the retailer id advances by 45 then a trigger should be generated saying that retailer id has advanced to 45 numbers from this date.

View 1 Replies View Related

SQL & PL/SQL :: Generate A CSV Report?

Feb 18, 2012

I have a requirement to generate a report in csv file, attached scripts, sql query and actual output and expected output.

In the query, I am hard-coding month&year, when I run in march 2012 it should include march 2012 in output (similarly for every month going forward), to do that I have to modify my query to include march 2012 & preceding months in every individual query, this report needs to be generated once every month.

I am looking for a generic solution which does not need to be modified every month, also in the output,under "Mail File" data should be in ascending order, in the "Total Mailed" all the months should have grand total.

oracle version : 10.2.0.1.0

View 25 Replies View Related

SQL & PL/SQL :: Random Number Generate

Jul 6, 2010

I have 2 question....

1. i have a table and i want to pick data from one field randomly..

i write this query for this purpose...

SELECT SUBJECTIVE_QUES_PK FROM
(SELECT SUBJECTIVE_QUES_PK FROM PMS.SUBJECTIVE_QUES
ORDER BY SYS.DBMS_RANDOM.VALUE)
WHERE QUES_TYPE_FK=3
AND ROWNUM<=5;

2. can i write this query in forms 6i..

i know that this question not relevant but if i write it in forms section then i will be duplicate.

View 27 Replies View Related

Generate Report From OEM Grid

Feb 6, 2012

Can we collect information(generate report) about user sessions connected, database hit ratios, memory usage and other system metrics of all target databases from OEM grid control repository database. Which tables/views in repository DB will be holding these information.

View 1 Replies View Related

SQL & PL/SQL :: Generate A Report Much Like A Program?

Jun 14, 2012

I need to generate a report much like a program guide from the script attached. My problem is that my script works, but it's wrong, all the information is wrong. Here's the two queries I came up with;

SELECT CHANNELS.channel_NO,
CHANNELS.CHANNEL_NAME,
PROGRAM_PACKAGES.PACKAGE_NAME,
SUPPLIER_LISTING.PROGRAM_NAME,

[code]...

And

SELECT CHANNELS.channel_NO,
CHANNELS.CHANNEL_NAME,
PROGRAM_PACKAGES.PACKAGE_NAME,
SUPPLIER_LISTING.PROGRAM_NAME,

[code]...

View 9 Replies View Related

Forms :: How To Generate Serial Number

Aug 23, 2011

how do you generate serial numbers? i have an invoice where i have to add items, so i want the numbering to start from 1 everytime i start a new form and increment by 1 as i i enter a new record. For this i have put my code in when-new-record-instance but problem is if i delete a record and enter a new one, the serial number starts from the nex number.

For ex. if i have 4 items with serial numbers 1, 2, 3, 4 and i delete the item at no. 3 and add a new item , the serial number comes as 5.

proper code to generate the serial numbers?

View 7 Replies View Related

PL/SQL :: Generate Unique Number Without Sequence?

Feb 13, 2013

I need generate an unique number without sequence. I am using Oracle 11.2, here is the details. The column idSeq is unique withing one specific idType. I don't want a sequence for each idType.

create table tb_test (idSeq number(5), idType number(5), addr varchar2(256));

insert into tb_test
(select case when idSeq is null then 1 else max(idSeq)+1 end, 3, 'Main street');

I am having ORA-00937 : not a single-group group function error

View 23 Replies View Related

Generate Report From OEM Grid 11 Or RMAN

May 17, 2013

*How can we generate the report of backup status,tablesapce(usedf,free space) for all the databases from OEM or RMAN*

1.)we need generate the report of tablespace used,free, archive...
2.)we also need we generate the Backup status report also

View 4 Replies View Related

Generate AWR Report Between Two Timings (Not Snapshot ID)

Aug 12, 2013

I have a requirement to create a generic script to generate awr report on the basis of two timings . We are planning to do load test on multiple server. The user will key in the start time and the end time of the load test and the script should round off the user entered time to the nearest snapshot id and generate a awr report. I have tried using the dba_hist_snapshot table fields begin_interval_time and end_interval_time column. However it failed.

The servers are running on 11g as well as 10g and few are in RAC and few single instances.

/************************************
* INPUT PARAMETERS
************************************/
UNDEFINE FROM_TIME
UNDEFINE TO_TIME

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: Generate ADDM And ASH Report In HTML

May 10, 2010

I am trying to generate (Oracle 10g) ADDM and ASH report using sql query in HTML format. Just like below given AWR report.

e.g. :

SELECT output
FROM
TABLE
(dbms_workload_repository.awr_report_html
(37933856,1,2900,2911 )
);

View 1 Replies View Related

SQL & PL/SQL :: Generate PAN Number By Concatenation Of Data From Different Tables?

Jul 11, 2011

How to generate a PAN number by concatenation of data from different tables

View 3 Replies View Related

SQL & PL/SQL :: Reference Cursor To Generate Sequence Number

Apr 1, 2013

I want the query to generate the sequenctial number from 1 for the new financial year in some query.

View 6 Replies View Related

SQL & PL/SQL :: Query To Generate Report For Shopping Store

Mar 25, 2013

I've below test case

CREATE TABLE MYMERCHANT
(
SRNONUMBER(5),
PARENTSRNONUMBER(5),
LEVELNONUMBER(5),
LEVELNAMEVARCHAR2(25)
) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 1, NULL, 101, 'HQ1' ) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 2, 1, 102, 'DIV1' ) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 3, 1, 103, 'DIV2' ) ;
[code]........

Structure is like above. There is a Head Quarter (HQ1) under which we have different division like (DIV1 and DIV2). Under division we have different location where our shopping stores are operating and transactions are done (LOC1, LOC2 for DIV1) and (LOC3 for DIV2) respectively.

DESIRED OUTPUT
User can generate reports at two levels i.e. Head Quarter level and Division Level.

When user wants report at Head Quarter Level :
Page 1 (Should Display as below)
HQ1 DIV1 LOC1 450
HQ1 DIV1 LOC2 400
HQ1 DIV2 LOC3 600

Page 2 (Should Display as below)
HQ1 DIV1 LOC1 450
HQ1 DIV1 LOC2 400

Page 3 (Should Display as below)
HQ1 DIV2 LOC3 600

When user wants report at Division Level : eg DIV1
Page 1 (Should Display as below)
HQ1 DIV1 LOC1 450
HQ1 DIV1 LOC2 400

View 2 Replies View Related

Forms :: How To Generate PDF Report From Form Developer 6i

Sep 21, 2010

I want to generate a PDF report from Form Developer 6i.

View 5 Replies View Related

Performance Tuning :: Generate HTML AWR Report?

Jan 10, 2012

I would like to generate HTML awr report and save it to my local machine.

After running $ORACLE_HOME/rdbms/admin/awrrpt.sql in sqlplus and specifying HTML in (Enter value for report_type: HTML) i hvae get numerous html code in my sqlplus prompt.. I want to save the html report in local machine and open it by double clicking on it.. it will be opened in a browser..

View 9 Replies View Related

Generate Oracle Report And To MS Word Format?

May 25, 2011

I would like to generate oracle report and to MS word format? new xml format of MS office?

View 2 Replies View Related

Generate Report Of Backup / Tablesapce From OEM / RMAN?

May 17, 2013

How can we generate the report of backup status,tablesapce(usedf,free space) for all the databases from OEM / RMAN

1.)we need generate the report of tablespace used,free, archive...

2.)How can we generate the Backup status report also

View 3 Replies View Related

Requirement To Generate A Report Of Users And Their Different Licenses

Apr 30, 2013

I have come across a requirement to generate a report of all the users in Hyperion shared services console along with their previlages and also the different licences consumed by the users like Essbase, HFM, Hyperion Planning, Workforce planning etc.... The provisioning report in Shared services is not giving these details.

View 0 Replies View Related

Generate Random And Unique 6 Digit Number In Oracle

Sep 29, 2008

I need to generate random and unique 6 digit number in Oracle. I need to insert these numbers into a table. I tried using DBMS_RANDOM package, which generates random 6 digit numbers, but fails to generate UNIQUE numbers.

View 3 Replies View Related

SQL & PL/SQL :: Performance To Generate Auto-increment Number In Oracle

Jun 16, 2010

which method is more efficient in terms of performance to generate an auto increment number in oracle.

Step1

CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

Step2
CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;
[code]....

View 22 Replies View Related

PL/SQL :: How To Generate Large Random Number Without Decimal Places

Nov 5, 2012

I need to generate random numbers from the range 71 million to 90 million. While generatind the same iam getting the value with decimal places.

How to generate with out decimal places ?

SQL> select ABS(dbms_random.value(71000000,90000000)) from dual;
ABS(DBMS_RANDOM.VALUE(71000000,90000000))
-----------------------------------------
87283730.7

View 6 Replies View Related

Reports & Discoverer :: Generate Report To Database Table?

Aug 25, 2011

Is it possible to save a report directly in a table instead of the typically destination? (file, cache,...)

View 4 Replies View Related

Reports & Discoverer :: How To Generate Report And Send Straight To Email

Apr 11, 2013

How to generate a report and straight send it to an Email? I want to match student reg number and the parents email provided .

View 1 Replies View Related

Reports & Discoverer :: Created A Report To Generate Details Of Officials

Dec 4, 2010

we have a admin wizard where in there are 850 officials whose details are like

emp id
date of birth
date of joining
basic pay
photo
etc........

i have the photos of the officials out of the data base.i have created a report to generate the details of the officials. what i would like to have is to get the photo of each official from the file which is not in the data base.

i have the command in pre_form read_image_file('path where the photos are','msg.mcn');

this gives the same photo to all the officials. how do i be able to link the photos to their emp id and get it.

View 5 Replies View Related







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