Home » SQL & PL/SQL » SQL & PL/SQL » SQL query (Oracle 9.2.0.3)
SQL query [message #393796] Tue, 24 March 2009 06:58 Go to next message
deb.b
Messages: 44
Registered: December 2008
Member
CREATE TABLE TRACK_FLOW_TBL
(
    FLOW_ID               CHAR(4)      NOT NULL,
    INS_DTE              TIMESTAMP(6) NOT NULL,
    FLOW_HR           NUMBER(3)    NOT NULL,
    FLOW_MIN          NUMBER(3)    NOT NULL,
    FLOW_DURATION      NUMBER(5)    NOT NULL,
    QUANTITY              NUMBER(10)       NULL
   
)
/

---INSERT STATEMENTS
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 17, 45, 15, 10 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 0, 15, 5 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 15, 15, 10 ) 
/
INSERT INT OTRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 30, 15, 20 ) 
/
INSERT INT OTRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 45, 15, 1 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 19, 0, 15, 2 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 19, 15, 15, 3 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 19, 30, 15, 7 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 19, 45, 15, 5 ) 
/



INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '144', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 17, 45, 15, 3 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '144', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 0, 15, 3 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '144', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 15, 15, 3 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '144', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 30, 15, 3 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '144', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 45, 15, 3 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '144', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 19, 0, 15, 3 ) 
/



Output:

 On selecting the date  March 25, 2004, I need a report on Hourly basis (for hrs from 18:00 -24:00):


          18  19  20   21   22  23   24
           
143       36  17   0    0    0   0   0   [ Here 36 is sum of quantity for hr 18]
 
144       12   3   0    0    0   0   0


 if I select the date 25 th March 2004 and for time range (for hrs from 12:00 -18:00) then expected output should be



        12       13      14      15      16      17   18
          
143     0        0        0       0      0      10    36 


144     0        0        0       0      0      3     12 


Also,we should also be able to select quarterly ( after every 15 min) for a specific time range
Say for date 25/03/2004 Time diffe: (17-18 hrs)
the output should be:



            17:00   17:15   17:30   17:45     18:00
143         0           0       0       10       5
144         0           0       0        3       3



How to accomplish this?



Thanks in advance

[EDITED by LF: schema name removed per the original poster's request]

[Updated on: Tue, 24 March 2009 07:28] by Moderator

Report message to a moderator

Re: SQL query [message #393798 is a reply to message #393796] Tue, 24 March 2009 07:16 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

SQL> INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY )
  2              VALUES ( '144', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 19, 0, 15
, 3 )
  3  /
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MINM, FLOW_DURATION, QUANTITY )
                                                        *
ERROR at line 1:
ORA-00904: "FLOW_MINM": invalid identifier


SQL> desc TRACK_FLOW_TBL
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FLOW_ID                                   NOT NULL CHAR(4)
 INS_DTE                                   NOT NULL TIMESTAMP(6)
 FLOW_HR                                   NOT NULL NUMBER(3)
 FLOW_MIN                                  NOT NULL NUMBER(3)
 FLOW_DURATION                             NOT NULL NUMBER(5)
 QUANTITY                                           NUMBER(10)


Babu
Re: SQL query [message #393800 is a reply to message #393798] Tue, 24 March 2009 07:27 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
Re posting the test script again as it contained error!

CREATE TABLE TRACK_FLOW_TBL
(
    FLOW_ID               CHAR(4)      NOT NULL,
    INS_DTE              TIMESTAMP(6) NOT NULL,
    FLOW_HR           NUMBER(3)    NOT NULL,
    FLOW_MIN          NUMBER(3)    NOT NULL,
    FLOW_DURATION      NUMBER(5)    NOT NULL,
    QUANTITY              NUMBER(10)       NULL
   
)
/

---INSERT STATEMENTS
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MIN, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 17, 45, 15, 10 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MIN, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 0, 15, 5 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MIN, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 15, 15, 10 ) 
/
INSERT INT OTRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MIN, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 30, 15, 20 ) 
/
INSERT INT OTRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MIN, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 45, 15, 1 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MIN, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 19, 0, 15, 2 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MIN, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 19, 15, 15, 3 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MIN, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 19, 30, 15, 7 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MIN, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '143', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 19, 45, 15, 5 ) 
/



INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MIN, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '144', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 17, 45, 15, 3 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MIN, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '144', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 0, 15, 3 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MIN, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '144', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 15, 15, 3 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MIN, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '144', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 30, 15, 3 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MIN, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '144', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 18, 45, 15, 3 ) 
/
INSERT INTO TRACK_FLOW_TBL ( FLOW_ID, INS_DTE, FLOW_HR, FLOW_MIN, FLOW_DURATION, QUANTITY ) 
		 VALUES ( '144', TO_TIMESTAMP('03/25/2004 12:00:00.000000 AM', 'MM/DD/YYYY HH12:MI:SS.ff AM'), 19, 0, 15, 3 ) 
/




Output:
 On selecting the date  March 25, 2004, I need a report on Hourly basis (for hrs from 18:00 -24:00):


          18  19  20   21   22  23   24
           
143       36  17   0    0    0   0   0   [ Here 36 is sum of quantity for hr 18]
 
144       12   3   0    0    0   0   0


 if I select the date 25 th March 2004 and for time range (for hrs from 12:00 -18:00) then expected output should be



        12       13      14      15      16      17   18
          
143     0        0        0       0      0      10    36 


144     0        0        0       0      0      3     12 


Also,we should also be able to select quarterly ( after every 15 min) for a specific time range
Say for date 25/03/2004 Time diffe: (17-18 hrs)
the output should be:



            17:00   17:15   17:30   17:45     18:00
143         0           0       0       10       5
144         0           0       0        3       3




How to accomplish this?



Thanks in advance
Re: SQL query [message #393801 is a reply to message #393796] Tue, 24 March 2009 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice exercise.
I did it a couple of years ago to display log switches.
Really nice exercice, it kept me busy for a couple of days.
Good luck and tell us what you found.

Regards
Michel
Re: SQL query [message #393804 is a reply to message #393801] Tue, 24 March 2009 07:35 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
I need some advice here. Can I try pivoting method?

Thanks
Re: SQL query [message #393809 is a reply to message #393804] Tue, 24 March 2009 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to sum per period: sum(decode(<row_period from row_hour>,<reference_period>,<value to sum>))
If this is you mean by pivoting method, yes.
Now the way you calculate "row_period" and "reference_period" depends on your input parameters: start_date, start_hour, end_hour, period_size.

Regards
Michel
Re: SQL query [message #393811 is a reply to message #393809] Tue, 24 March 2009 07:48 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
Thanks Michel,

I am not getting you here Michel.Can you please post a simple hint with my test cases.It would help a lot!
What I am doing is first I am using group by and sum and then
want to do Column to row convertion
Re: SQL query [message #393812 is a reply to message #393811] Tue, 24 March 2009 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What I am doing is first I am using group by and sum and then
want to do Column to row convertion

Post it.

Regards
Michel
Re: SQL query [message #393816 is a reply to message #393812] Tue, 24 March 2009 08:36 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
SELECT  FLOW_ID, 
MAX(DECODE(FLOW_HR,0, TOT_QTY)) AS "00:00",
MAX(DECODE(FLOW_HR,1, TOT_QTY)) AS "01:00",
MAX(DECODE(FLOW_HR,2, TOT_QTY)) AS "02:00",
MAX(DECODE(FLOW_HR,3, TOT_QTY)) AS "03:00",
MAX(DECODE(FLOW_HR,4, TOT_QTY)) AS "04:00",
MAX(DECODE(FLOW_HR,5, TOT_QTY)) AS "05:00",
MAX(DECODE(FLOW_HR,6, TOT_QTY)) AS "06:00",
MAX(DECODE(FLOW_HR,7, TOT_QTY)) AS "07:00",
MAX(DECODE(FLOW_HR,8, TOT_QTY)) AS "08:00",
MAX(DECODE(FLOW_HR,9, TOT_QTY)) AS "09:00",
MAX(DECODE(FLOW_HR,10, TOT_QTY)) AS "10:00",
MAX(DECODE(FLOW_HR,11, TOT_QTY)) AS "11:00",
MAX(DECODE(FLOW_HR,12, TOT_QTY)) AS "12:00",
MAX(DECODE(FLOW_HR,13, TOT_QTY)) AS "13:00",
MAX(DECODE(FLOW_HR,14, TOT_QTY)) AS "14:00",
MAX(DECODE(FLOW_HR,15, TOT_QTY)) AS "15:00",
MAX(DECODE(FLOW_HR,16, TOT_QTY)) AS "16:00",
MAX(DECODE(FLOW_HR,17, TOT_QTY)) AS "17:00",
MAX(DECODE(FLOW_HR,18, TOT_QTY)) AS "18:00",
MAX(DECODE(FLOW_HR,19, TOT_QTY)) AS "19:00",
MAX(DECODE(FLOW_HR,20, TOT_QTY)) AS "20:00",
MAX(DECODE(FLOW_HR,21, TOT_QTY)) AS "21:00",
MAX(DECODE(FLOW_HR,22, TOT_QTY)) AS "22:00",
MAX(DECODE(FLOW_HR,23, TOT_QTY)) AS "23:00",
MAX(DECODE(FLOW_HR,24, TOT_QTY)) AS "24:00"
FROM (SELECT FLOW_ID,FLOW_HR, SUM(QUANTITY) TOT_QTY FROM
TRACK_FLOW_TBL GROUP BY FLOW_ID,FLOW_HR)
GROUP BY FLOW_ID;


The above is what I tried so far.
Re: SQL query [message #393819 is a reply to message #393816] Tue, 24 March 2009 08:52 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
Quote:

sum(decode(<row_period from row_hour>,<reference_period>,<value to sum>))



How I will implement above?

There will be three parameters

1. Date (say 25 mar 2009)
2. Time range ( from start time - end time)
3. Hourly/Quarterly(per 15 min)



Thanks in adavance


Re: SQL query [message #393824 is a reply to message #393816] Tue, 24 March 2009 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
MAX works if you only have 1 row per hour otherwise you have to use SUM (I think it is better to use it anyway).

Regards
Michel
Re: SQL query [message #393827 is a reply to message #393819] Tue, 24 March 2009 09:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
deb.b wrote on Tue, 24 March 2009 14:52
How I will implement above?

This is the hard part of the work.
You have to dynamically define the periods (was 0,1,2,3,...23) and how to convert hr+min into a period number (note 15 minutes=1/4 hour)

Regards
Michel

Re: SQL query [message #393831 is a reply to message #393827] Tue, 24 March 2009 09:31 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
Michel Cadot wrote on Tue, 24 March 2009 09:19

You have to dynamically define the periods (was 0,1,2,3,...23) and how to convert hr+min into a period number (note 15 minutes=1/4 hour)

Can it be possible in sql? Should I have to write pl/sql for this?

[Updated on: Tue, 24 March 2009 09:41] by Moderator

Report message to a moderator

Re: SQL query [message #393833 is a reply to message #393831] Tue, 24 March 2009 09:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can it be possible in sql?

No and yes.

No, because a SQL query needs a static column number and names.
Yes, if you use a trick, either a SQL*Plus one or SQL one that fakes a SQL*Plus output.

Regards
Michel
Re: SQL query [message #393837 is a reply to message #393833] Tue, 24 March 2009 10:22 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:

Yes, if you use a trick, either a SQL*Plus one or SQL one that fakes a SQL*Plus output.




Didn't get you!! What did you mean when you said "fakes a SQL*Plus output"

Thanks
Oli

[Updated on: Tue, 24 March 2009 10:36]

Report message to a moderator

Re: SQL query [message #393849 is a reply to message #393837] Tue, 24 March 2009 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 COL1                                      VARCHAR2(4 CHAR)
 COL2                                      VARCHAR2(4 CHAR)
 COL3                                      VARCHAR2(4 CHAR)

SQL> set head off
SQL> set feed off
SQL> col nop noprint
SQL> select 1 nop, 'COL1 COL2 COL3' from dual
  2  union all
  3  select 2, '---- ---- ----' from dual
  4  union all
  5  select 3, lpad(col1,4)||' '||lpad(col2,4)||' '||lpad(col3,4) from t
  6  order by 1
  7  /
COL1 COL2 COL3
---- ---- ----
  C1   C2   C3

Regards
Michel
Re: SQL query [message #393861 is a reply to message #393796] Tue, 24 March 2009 13:52 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
Thanks...
But I need to show in a report.It would be of great help if anyone have solved similar kinda problem.

I am struck in how to implement the columns dynamically for the report and to get the output.

There will be three parameters

1. Date (say 25 mar 2009)
2. Time range ( from start time - end time) Three values will
be selected from drop down list--(say 0-6 hrs, 6-12,12-18,18-24 )
3. Hourly/Quarterly(per 15 min)


Thanks in advance!
Re: SQL query [message #393865 is a reply to message #393861] Tue, 24 March 2009 14:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But I need to show in a report

What does this mean? What is your client tool?
What did you try so far?

Regards
Michel
Re: SQL query [message #393938 is a reply to message #393865] Wed, 25 March 2009 01:40 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
The front end is asp.
Would you please give an example about how to dynamically define the periods (was 0,1,2,3,...23) and
use

sum(decode(<row_period from row_hour>,<reference_period>,<value to sum>))

I am unable to figure out how I should proceed.

Thanks


[Updated on: Wed, 25 March 2009 01:41]

Report message to a moderator

Re: SQL query [message #394026 is a reply to message #393865] Wed, 25 March 2009 07:34 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member


Since the number of columns is dynamic every time you run this "query" they could be
different so create a stored procedure which returns a dynamically opened ref cursor

Here I am first trying to get the hourly report.This is my first dynamic sql that I am trying to implement.
While trying to execute I am getting error.
ORA-00904: "I": invalid identifier

create or replace package test_pkg
   as
     type rc is ref cursor;
  
     procedure get_query( p_cursor in out rc, p_start number, p_end number );
  end;
/


create or replace package body test_pkg
  as
   procedure get_query( p_cursor in out rc, p_start number, p_end number )
  is
       l_query long := 'select Flow_ID ';
   begin
  
     for i in 1 .. p_end - (p_start+1)
      loop
           l_query := l_query || ', sum( decode( FLOW_HR, ' ||
                         ' p_start+i-1, QUANTITY, 0 )) "' ||
                         to_char(p_start+i-1) || '"';
		   end loop;
     l_query := l_query || ' from TRACK_FLOW_TBL group by FLOW_ID';
     open p_cursor for l_query;
  end;
  
end;
/

variable x refcursor

exec test_pkg.get_query( :x, 17,20 );
ORA-00904: "I": invalid identifier 



I think there is syntax error in the string.Where the error is?
 l_query := l_query || ', sum( decode( FLOW_HR, ' ||
                         ' p_start+i-1, QUANTITY, 0 )) "' ||
                         to_char(p_start+i-1) || '"';


[Updated on: Wed, 25 March 2009 08:08]

Report message to a moderator

Re: SQL query [message #394035 is a reply to message #394026] Wed, 25 March 2009 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use dbms_output to display your query and you'll see where the error is.

Regards
Michel
Re: SQL query [message #394041 is a reply to message #394035] Wed, 25 March 2009 08:55 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
create or replace package body test_pkg
  as
   procedure get_query( p_cursor in out rc, p_start number, p_end number )
  is
       l_query long := 'select Flow_ID ';
   begin
  
     for i in 1 .. p_end - (p_start+1)
      loop
           l_query := l_query || ', sum( decode( FLOW_HR, ' ||
                         ' p_start+i-1, QUANTITY, 0 )) "' ||
                         to_char(p_start+i-1) || '"';
		   end loop;
     l_query := l_query || ' from TRACK_FLOW_TBL group by FLOW_ID';
dbms_output.put_line(l_query);
     open p_cursor for l_query;
  end;
  
end;
/

variable x refcursor

exec test_pkg.get_query( :x, 17,20 );


But didnt get!
Re: SQL query [message #394042 is a reply to message #393796] Wed, 25 March 2009 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>But didnt get!
Obviously Sad
Re: SQL query [message #394043 is a reply to message #394035] Wed, 25 March 2009 09:11 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Michel, I cant see the error prone sql being generated.
Re: SQL query [message #394044 is a reply to message #394043] Wed, 25 March 2009 09:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
set serveroutput on
Re: SQL query [message #394047 is a reply to message #394044] Wed, 25 March 2009 09:19 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I did that. I even did this. I increased the size.

 SET SERVEROUTPUT ON SIZE 1000000
Re: SQL query [message #394049 is a reply to message #393796] Wed, 25 March 2009 09:25 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then something odd is going on - it works for me (serveroutput that is):

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> set serveroutput on
SQL> variable x refcursor
SQL> exec test_pkg.get_query( :x, 17,20 );
select Flow_ID , sum( decode( FLOW_HR,  p_start+i-1, QUANTITY, 0 )) "17", sum(
decode( FLOW_HR,  p_start+i-1, QUANTITY, 0 )) "18" from TRACK_FLOW_TBL group by
FLOW_ID
BEGIN test_pkg.get_query( :x, 17,20 ); END;

*
ERROR at line 1:
ORA-00904: "I": invalid identifier
ORA-06512: at "LIVE.TEST_PKG", line 16
ORA-06512: at line 1


SQL> 


Are you using sqlplus or some other gui tool?
Re: SQL query [message #394051 is a reply to message #394049] Wed, 25 March 2009 09:45 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I am using sqlplus
Getting below error! I incresed the size still getting the error
ORA-06502: PL/SQL: numeric or value error: host bind array too small

[Updated on: Wed, 25 March 2009 09:46]

Report message to a moderator

Re: SQL query [message #394052 is a reply to message #394051] Wed, 25 March 2009 09:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So copy and paste your SQL*Plus session.
And comment open cursor statement to test.

Regards
Michel
Re: SQL query [message #394055 is a reply to message #394052] Wed, 25 March 2009 09:57 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Got it! Thanks


SQL> create or replace package test_pkg0325
  2     as
  3       type rc is ref cursor;
  4    
  5       procedure get_query( p_cursor in out rc, p_start number, p_end number );
  6    end;
  7  /

Package created.

SQL> 
SQL> 
SQL> create or replace package body test_pkg0325
  2    as
  3     procedure get_query( p_cursor in out rc, p_start number, p_end number )
  4    is
  5         l_query long := 'select Flow_ID ';
  6     begin
  7    
  8       for i in 1..p_end - (p_start+1)
  9        loop
 10             l_query := l_query || ', sum( decode( FLOW_HR, ' ||
 11                           ' p_start+i-1, QUANTITY, 0 )) "' ||
 12                           to_char(p_start+i-1) || '"';
 13       end loop;
 14       l_query := l_query || ' from TRACK_FLOW_TBL group by FLOW_ID';
 15    DBMS_OUTPUT.PUT_LINE(l_query);
 16    DBMS_OUTPUT.PUT_LINE('-------test------------');
 17       --open p_cursor for l_query;
 18    end;
 19    
 20  end;
 21  /

Package body created.

SQL> set serveroutput on
SQL> variable x refcursor
SQL> 
SQL> exec test_pkg0325.get_query( :x, 17,19 );
select Flow_ID , sum( decode( FLOW_HR,  p_start+i-1, QUANTITY, 0 )) "17" from TRACK_FLOW_TBL group by
-------test------------

PL/SQL procedure successfully completed.

ERROR:
ORA-24338: statement handle not executed


SP2-0625: Error printing variable "x"
SQL> 

[Updated on: Wed, 25 March 2009 09:58]

Report message to a moderator

Re: SQL query [message #394061 is a reply to message #394055] Wed, 25 March 2009 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it is clear now: "p_start+i-1" is not part of the query is part of your procedure.
You have to change << ' p_start+i-1, QUANTITY >> as you did it in << to_char(p_start+i-1) || '"' >>.

Regards
Michel
Re: SQL query [message #394069 is a reply to message #393796] Wed, 25 March 2009 10:56 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member


Here I am first trying to get the hourly report.Add the date parameter in where clause to select based on that.

I also want to know if this can be achieved using sql which seems impossible to me. Any suggesion would highly be appreciated.

create or replace package test_pkg
   as
     type rc is ref cursor;
  
     procedure get_query( p_cursor in out rc, p_start number, p_end number );
  end;
/


create or replace package body test_pkg
  as
   procedure get_query( p_cursor in out rc, p_start number, p_end number )
  is
       l_query long := 'select Flow_ID ';
   begin
  
      for i in 1..p_end - p_start
      loop
	    l_query := l_query || ', sum( decode( FLOW_HR, ' ||
             ' ' || to_char(p_start+i-1) ||
             ' , QUANTITY, 0 )) "' ||
             to_char(p_start+i-1) || '"';
     end loop;
     l_query := l_query || ' from TRACK_FLOW_TBL group by FLOW_ID';
     open p_cursor for l_query;
  end;
  
end;
/

variable x refcursor

exec test_pkg.get_query( :x, 17,20 );

[Updated on: Wed, 25 March 2009 11:33] by Moderator

Report message to a moderator

Re: SQL query [message #394071 is a reply to message #394061] Wed, 25 March 2009 10:59 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Michel for the suggestion. I think to get report based on parameter quarterly/half hourly a bit tricky. I would work on it.

Regards,
Oli
Re: SQL query [message #394079 is a reply to message #394071] Wed, 25 March 2009 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (select round(dbms_random.value(0,59)) val from dual connect by level <= 10)
  2  select val, trunc(val/15) quarter from data
  3  order by 1
  4  /
       VAL    QUARTER
---------- ----------
         1          0
         2          0
         4          0
        18          1
        21          1
        29          1
        34          2
        53          3
        53          3
        57          3

10 rows selected.

Regards
Michel
Re: SQL query [message #394233 is a reply to message #394079] Thu, 26 March 2009 04:28 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Michel.
For quarterly report,
SELECT  decode( trunc(FLOW_MIN/15) , 0 , FLOW_HR||':'|| 00,
                                   1 , FLOW_HR||':'|| 15,
                                   2 , FLOW_HR||':'|| 30, 	
                                   3 , FLOW_HR||':'|| 45 ) QUARTERLY, QUANTITY							  
    FROM TRACK_FLOW_TBL
	WHERE FLOW_HR BETWEEN p_start AND p_end AND FLOW_ID=143
	AND INS_DTE =TO_DATE('25/03/2004','DD/MM/YYYY')
    ORDER BY 1

and then use the pivot method.

Hope it helps.Please give your valuable suggestion if I am wrong here

Regards,
Oli

*Code Formatted

[Updated on: Thu, 26 March 2009 05:49]

Report message to a moderator

Re: SQL query [message #394307 is a reply to message #394233] Thu, 26 March 2009 07:41 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
But here the number of rows returned is dyanmic. Its not fixed. How I will use pivot?
Re: SQL query [message #394325 is a reply to message #393796] Thu, 26 March 2009 08:17 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
Helloooo!!! Is there anyone to give some hint for my query.

Thanks
Re: SQL query [message #394328 is a reply to message #394325] Thu, 26 March 2009 08:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
36 minutes was too long to wait for someone who's not getting paid to help you do your job?

I thought you were trying to group the rows into 24 hourly buckets - why is the number of rows returned variable?
Re: SQL query [message #394330 is a reply to message #394328] Thu, 26 March 2009 08:25 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
why is the number of rows returned variable? - I didnt understand.
Would you please give some hint about how to use pivot table when
the number of returned rows is dynamic?


[Updated on: Thu, 26 March 2009 08:26]

Report message to a moderator

Re: SQL query [message #394334 is a reply to message #393796] Thu, 26 March 2009 08:31 Go to previous messageGo to previous message
deb.b
Messages: 44
Registered: December 2008
Member

Time                                                  quantity
_____________________________________________________________

17:45                                                  3
18:0                                                   4
18:15                                                  4
18:30                                                 68
18:45                                                136






Based on the p_start(=17) and p_end(=19) value the rows will vary.How I will pivot for variable rows?
Previous Topic: ORA-01426: numeric overflow on 10.0.2.0.4 in code that works with 9.0.2.0.8
Next Topic: query required
Goto Forum:
  


Current Time: Fri Dec 02 18:37:18 CST 2016

Total time taken to generate the page: 0.42601 seconds