OraFAQ Forum: SQL & PL/SQL » Problem with a query.

Home » SQL & PL/SQL » SQL & PL/SQL » Problem with a query.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with a query. [message #286013]

Thu, 06 December 2007 05:20

mklynx
Messages: 17
Registered: December 2007

Junior Member

HI all

I have been trying for a few day's now to get this query to work.
And i haven't so i need help.

I have the following query.


SELECT AVG.day
     , AVG.month
     , AVG.year
     , AVG.R_day
     , AVG.R_month
     , AVG.R_year
     , AVG.h
     , avg.r_hour
     , AVG.AVG
FROM 
    (SELECT  AVG(nvl(AVR_T.T_D_sec,0) ) OVER ( PARTITION by mod_day.h , mod_day.DAY ) AVG
           , mod_day.DAY
           , mod_day.month
           , mod_day.year
           , mod_day.h h
           , AVr_T.r_date
           , AVr_T.r_hour
           , AVr_T.r_day
           , AVr_T.r_month
           , AVr_T.r_year
     FROM 
        (select
               SUBSTR(TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'),0,10) r_date
              ,SUBSTR(REC.LOCAL_TM,10,2 ) R_hour 
              ,SUBSTR(REC.LOCAL_TM,3,2 ) R_year
              ,SUBSTR(TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'),4,3) r_month
              ,SUBSTR(REC.LOCAL_TM,7,2 ) R_day
              ,(SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')), 
                  INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+1,2)*3600)+ 
                  (SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')), 
                  INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+4,2)*60)+ 
                  SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')), 
                  INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')- 
                  TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+7,2) T_D_sec
         FROM
            TMS_MESSAGE_AUDIT_LOG WCA , 
            TMS_MESSAGE_AUDIT_LOG REC
         WHERE WCA.MESSAGE_ID = REC.MESSAGE_ID AND 
               REC.MESSAGE_STATUS = '(Receive)' AND 
               WCA.MESSAGE_STATUS = 'Wait CLSB Ack' and 
               REC.MESSAGE_VERSION = 1 and 
               WCA.MESSAGE_version = 2[/color] 
        ) AVR_T ,
        (SELECT
             SUBSTR(to_char(trunc (to_date('05-Nov-07','dd-MON-rr')) + (level/25), 'dd-MON-yy'),0,2) DAY
            ,SUBSTR(to_char(trunc (to_date('05-Nov-07','dd-MON-rr')) + (level/25), 'dd-MON-yy'),4,3) month
            ,SUBSTR(to_char(trunc (to_date('05-Nov-07','dd-MON-rr')) + (level/25), 'dd-MON-yy'),8,2) year
            ,to_char(trunc (to_date('05-Nov-07','dd-MON-rr')) + (level/25), 'hh24') h 
          FROM
             dual 
          connect by level < (to_date('10-Nov-07','dd-mon-rr') - to_date('05-Nov-07','dd-mon-rr')) * 24 
        ) Mod_day
  WHERE mod_day.h = R_hour and
        mod_day.day = AVR_T.R_day and
        mod_day.month = AVR_T.R_month 
   ) AVG 
;

And i am getting the folowin output.

DAY MONTH YEAR R_DAY R_MONTH R_YEAR H R_HOUR AVG

08 NOV 07 08 NOV 07 13 13 6.5
08 NOV 07 08 NOV 07 13 13 6.5
08 NOV 07 08 NOV 07 13 13 6.5
08 NOV 07 08 NOV 07 13 13 6.5
08 NOV 07 08 NOV 07 13 13 6.5
08 NOV 07 08 NOV 07 13 13 6.5
08 NOV 07 08 NOV 07 13 13 6.5
08 NOV 07 08 NOV 07 13 13 6.5
09 NOV 07 09 NOV 07 13 13 9.66666666666667
09 NOV 07 09 NOV 07 13 13 9.66666666666667
09 NOV 07 09 NOV 07 13 13 9.66666666666667
09 NOV 07 09 NOV 07 13 13 9.66666666666667
09 NOV 07 09 NOV 07 13 13 9.66666666666667
09 NOV 07 09 NOV 07 13 13 9.66666666666667
06 NOV 07 06 NOV 07 14 14 5.625
06 NOV 07 06 NOV 07 14 14 5.625
06 NOV 07 06 NOV 07 14 14 5.625
06 NOV 07 06 NOV 07 14 14 5.625
06 NOV 07 06 NOV 07 14 14 5.625
06 NOV 07 06 NOV 07 14 14 5.625
06 NOV 07 06 NOV 07 14 14 5.625
06 NOV 07 06 NOV 07 14 14 5.625
07 NOV 07 07 NOV 07 14 14 6.57142857142857
07 NOV 07 07 NOV 07 14 14 6.57142857142857
07 NOV 07 07 NOV 07 14 14 6.57142857142857
07 NOV 07 07 NOV 07 14 14 6.57142857142857
07 NOV 07 07 NOV 07 14 14 6.57142857142857
07 NOV 07 07 NOV 07 14 14 6.57142857142857
07 NOV 07 07 NOV 07 14 14 6.57142857142857
08 NOV 07 08 NOV 07 14 14 4
05 NOV 07 05 NOV 07 15 15 6.75
05 NOV 07 05 NOV 07 15 15 6.75
05 NOV 07 05 NOV 07 15 15 6.75
05 NOV 07 05 NOV 07 15 15 6.75
07 NOV 07 07 NOV 07 16 16 4
07 NOV 07 07 NOV 07 16 16 4
05 NOV 07 05 NOV 07 20 20 3

What i need is as output is

05-NOV-07 0 0
05-NOV-07 1 0
05-NOV-07 2 0
05-NOV-07 3 0
05-NOV-07 4 0
05-NOV-07 5 0
05-NOV-07 6 0
05-NOV-07 7 0
05-NOV-07 8 0
05-NOV-07 9 0
05-NOV-07 10 0
05-NOV-07 11 0
05-NOV-07 12 0
05-NOV-07 13 0
05-NOV-07 14 0
05-NOV-07 15 6.75
05-NOV-07 16 0
05-NOV-07 17 0
05-NOV-07 18 0
05-NOV-07 19 0
05-NOV-07 20 3
05-NOV-07 21 0
05-NOV-07 22 0
05-NOV-07 23 0

enz...

The main query ( AVR_T ) gives the following output.

R_DATE R_HOUR R_YEAR R_MONTH R_DAY T_D_SEC

19-OCT-07 12 07 OCT 19 6
22-OCT-07 13 07 OCT 22 10
22-OCT-07 13 07 OCT 22 12
22-OCT-07 13 07 OCT 22 12
22-OCT-07 13 07 OCT 22 10
22-OCT-07 13 07 OCT 22 8
22-OCT-07 14 07 OCT 22 9
22-OCT-07 14 07 OCT 22 10
22-OCT-07 15 07 OCT 22 10
23-OCT-07 13 07 OCT 23 10
23-OCT-07 13 07 OCT 23 10
23-OCT-07 13 07 OCT 23 8
23-OCT-07 14 07 OCT 23 10
23-OCT-07 13 07 OCT 23 9
23-OCT-07 13 07 OCT 23 8
23-OCT-07 13 07 OCT 23 10
24-OCT-07 14 07 OCT 24 3
24-OCT-07 15 07 OCT 24 5
24-OCT-07 15 07 OCT 24 5
24-OCT-07 15 07 OCT 24 9
24-OCT-07 15 07 OCT 24 5
24-OCT-07 15 07 OCT 24 5
24-OCT-07 15 07 OCT 24 3
24-OCT-07 16 07 OCT 24 5
24-OCT-07 17 07 OCT 24 5
24-OCT-07 17 07 OCT 24 5
24-OCT-07 18 07 OCT 24 5
24-OCT-07 18 07 OCT 24 5
24-OCT-07 18 07 OCT 24 8
25-OCT-07 13 07 OCT 25 7
25-OCT-07 13 07 OCT 25 8
25-OCT-07 13 07 OCT 25 10
25-OCT-07 13 07 OCT 25 13
25-OCT-07 13 07 OCT 25 8
25-OCT-07 13 07 OCT 25 11
26-OCT-07 14 07 OCT 26 5
26-OCT-07 14 07 OCT 26 8
26-OCT-07 15 07 OCT 26 10
05-NOV-07 15 07 NOV 05 9
05-NOV-07 15 07 NOV 05 6
05-NOV-07 15 07 NOV 05 6
05-NOV-07 15 07 NOV 05 6
05-NOV-07 20 07 NOV 05 3
06-NOV-07 14 07 NOV 06 3
06-NOV-07 14 07 NOV 06 3
06-NOV-07 14 07 NOV 06 9
06-NOV-07 14 07 NOV 06 5
06-NOV-07 14 07 NOV 06 9
06-NOV-07 14 07 NOV 06 4
06-NOV-07 14 07 NOV 06 6
06-NOV-07 14 07 NOV 06 6
07-NOV-07 14 07 NOV 07 7
07-NOV-07 14 07 NOV 07 9
07-NOV-07 14 07 NOV 07 5
07-NOV-07 14 07 NOV 07 5
07-NOV-07 14 07 NOV 07 4
07-NOV-07 14 07 NOV 07 6
07-NOV-07 14 07 NOV 07 10
08-NOV-07 13 07 NOV 08 4
08-NOV-07 13 07 NOV 08 3
07-NOV-07 16 07 NOV 07 5
07-NOV-07 16 07 NOV 07 3
08-NOV-07 13 07 NOV 08 3
08-NOV-07 13 07 NOV 08 9
08-NOV-07 13 07 NOV 08 2
08-NOV-07 13 07 NOV 08 6
08-NOV-07 13 07 NOV 08 11
08-NOV-07 13 07 NOV 08 14
08-NOV-07 14 07 NOV 08 4
09-NOV-07 13 07 NOV 09 5
09-NOV-07 13 07 NOV 09 10
09-NOV-07 13 07 NOV 09 15
09-NOV-07 13 07 NOV 09 3
09-NOV-07 13 07 NOV 09 20
09-NOV-07 13 07 NOV 09 5
19-NOV-07 15 07 NOV 19 5
19-NOV-07 16 07 NOV 19 6
20-NOV-07 14 07 NOV 20 9
20-NOV-07 14 07 NOV 20 9
20-NOV-07 15 07 NOV 20 9
20-NOV-07 16 07 NOV 20 5
21-NOV-07 12 07 NOV 21 5
21-NOV-07 13 07 NOV 21 5
21-NOV-07 13 07 NOV 21 10
21-NOV-07 13 07 NOV 21 15
21-NOV-07 13 07 NOV 21 5
28-NOV-07 14 07 NOV 28 7
23-NOV-07 16 07 NOV 23 6
23-NOV-07 16 07 NOV 23 6
23-NOV-07 19 07 NOV 23 4

And this is the correct data with out the none existing lines.
These none existing lines i need to add. But that is something i can not get to work.

Can anybody Help

Marcel Koert ( Reformatted )

[Updated on: Thu, 06 December 2007 05:36]

Report message to a moderator

Re: Problem with a query. [message #286016 is a reply to message #286013]

Thu, 06 December 2007 05:22

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Try again, edit your post and this time use code tags. The way it is now, I can't make much out of it.

Read the forum guide (it's the topic in the yellow bar).

Thanks.

MHE

Report message to a moderator

Re: Problem with a query. [message #286035 is a reply to message #286013]

Thu, 06 December 2007 06:10

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Outer join like ,

 WHERE mod_day.h(+) = AVR_T.R_hour and
        mod_day.day(+) = AVR_T.R_day and
        mod_day.month (+)= AVR_T.R_month

Rajuvan

Report message to a moderator

Re: Problem with a query. [message #286036 is a reply to message #286013]

Thu, 06 December 2007 06:14

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

And better you go for Aggregate function instead of Analytic functions if you want the output like ,

Quote:

05-NOV-07 13 0
05-NOV-07 14 0
05-NOV-07 15 6.75
05-NOV-07 16 0
05-NOV-07 17 0
05-NOV-07 18 0

Rajuvan.

Report message to a moderator

Re: Problem with a query. [message #286040 is a reply to message #286035]

Thu, 06 December 2007 06:16

mklynx
Messages: 17
Registered: December 2007

Junior Member

The output that i now get is.

DAY MONTH YEAR R_DAY R_MONTH R_YEAR H R_HOUR AVG

08 NOV 07 08 NOV 07 13 13 6.5
08 NOV 07 08 NOV 07 13 13 6.5
08 NOV 07 08 NOV 07 13 13 6.5
08 NOV 07 08 NOV 07 13 13 6.5
08 NOV 07 08 NOV 07 13 13 6.5
08 NOV 07 08 NOV 07 13 13 6.5
08 NOV 07 08 NOV 07 13 13 6.5
08 NOV 07 08 NOV 07 13 13 6.5
09 NOV 07 09 NOV 07 13 13 9.66666666666667
09 NOV 07 09 NOV 07 13 13 9.66666666666667
09 NOV 07 09 NOV 07 13 13 9.66666666666667
09 NOV 07 09 NOV 07 13 13 9.66666666666667
09 NOV 07 09 NOV 07 13 13 9.66666666666667
09 NOV 07 09 NOV 07 13 13 9.66666666666667
06 NOV 07 06 NOV 07 14 14 5.625
06 NOV 07 06 NOV 07 14 14 5.625
06 NOV 07 06 NOV 07 14 14 5.625
06 NOV 07 06 NOV 07 14 14 5.625
06 NOV 07 06 NOV 07 14 14 5.625
06 NOV 07 06 NOV 07 14 14 5.625
06 NOV 07 06 NOV 07 14 14 5.625
06 NOV 07 06 NOV 07 14 14 5.625
07 NOV 07 07 NOV 07 14 14 6.57142857142857
07 NOV 07 07 NOV 07 14 14 6.57142857142857
07 NOV 07 07 NOV 07 14 14 6.57142857142857
07 NOV 07 07 NOV 07 14 14 6.57142857142857
07 NOV 07 07 NOV 07 14 14 6.57142857142857
07 NOV 07 07 NOV 07 14 14 6.57142857142857
07 NOV 07 07 NOV 07 14 14 6.57142857142857
08 NOV 07 08 NOV 07 14 14 4
05 NOV 07 05 NOV 07 15 15 6.75
05 NOV 07 05 NOV 07 15 15 6.75
05 NOV 07 05 NOV 07 15 15 6.75
05 NOV 07 05 NOV 07 15 15 6.75
07 NOV 07 07 NOV 07 16 16 4
07 NOV 07 07 NOV 07 16 16 4
05 NOV 07 05 NOV 07 20 20 3
22 OCT 07 14 7.66037735849057
22 OCT 07 14 7.66037735849057
24 OCT 07 18 7.66037735849057
24 OCT 07 18 7.66037735849057
24 OCT 07 18 7.66037735849057
24 OCT 07 15 7.66037735849057
24 OCT 07 15 7.66037735849057
24 OCT 07 15 7.66037735849057
24 OCT 07 15 7.66037735849057
24 OCT 07 15 7.66037735849057
24 OCT 07 15 7.66037735849057
23 NOV 07 19 7.66037735849057
22 OCT 07 15 7.66037735849057
26 OCT 07 15 7.66037735849057
22 OCT 07 13 7.66037735849057
22 OCT 07 13 7.66037735849057
22 OCT 07 13 7.66037735849057
22 OCT 07 13 7.66037735849057
22 OCT 07 13 7.66037735849057
21 NOV 07 13 7.66037735849057
21 NOV 07 13 7.66037735849057
21 NOV 07 13 7.66037735849057
21 NOV 07 13 7.66037735849057
26 OCT 07 14 7.66037735849057
26 OCT 07 14 7.66037735849057
19 NOV 07 15 7.66037735849057
23 NOV 07 16 7.66037735849057
23 NOV 07 16 7.66037735849057
24 OCT 07 17 7.66037735849057
24 OCT 07 17 7.66037735849057
20 NOV 07 14 7.66037735849057
20 NOV 07 14 7.66037735849057
19 OCT 07 12 7.66037735849057
24 OCT 07 16 7.66037735849057
19 NOV 07 16 7.66037735849057
28 NOV 07 14 7.66037735849057
20 NOV 07 16 7.66037735849057
24 OCT 07 14 7.66037735849057
21 NOV 07 12 7.66037735849057
23 OCT 07 13 7.66037735849057
23 OCT 07 13 7.66037735849057
23 OCT 07 13 7.66037735849057
23 OCT 07 13 7.66037735849057
23 OCT 07 13 7.66037735849057
23 OCT 07 13 7.66037735849057
20 NOV 07 15 7.66037735849057
23 OCT 07 14 7.66037735849057
25 OCT 07 13 7.66037735849057
25 OCT 07 13 7.66037735849057
25 OCT 07 13 7.66037735849057
25 OCT 07 13 7.66037735849057
25 OCT 07 13 7.66037735849057
25 OCT 07 13 7.66037735849057

Report message to a moderator

Re: Problem with a query. [message #286044 is a reply to message #286013]

Thu, 06 December 2007 06:22

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Sorry ,

 WHERE mod_day.h = AVR_T.R_hour(+) and
        mod_day.day = AVR_T.R_day(+) and
        mod_day.month = AVR_T.R_month(+)

Not able to test as we dont have Sample data

Thumbs Up

Rajuvan

[Updated on: Thu, 06 December 2007 06:24]

Report message to a moderator

Re: Problem with a query. [message #286049 is a reply to message #286044]

Thu, 06 December 2007 06:31

mklynx
Messages: 17
Registered: December 2007

Junior Member

Table

CREATE TABLE TMS_MESSAGE_AUDIT_LOG
(
MESSAGE_ID VARCHAR2(16 BYTE) NOT NULL,
MESSAGE_VERSION NUMBER(2) NOT NULL,
CREATE_TM VARCHAR2(18 BYTE) NOT NULL,
MESSAGE_STATUS VARCHAR2(30 BYTE),
TRANSACTION_TYPE_NM VARCHAR2(30 BYTE),
MESSAGE_TP VARCHAR2(3 BYTE),
WORKFLOW_OBJECT VARCHAR2(30 BYTE) NOT NULL,
WORKFLOW_REQUEST VARCHAR2(30 BYTE) NOT NULL,
WORKFLOW_RETURN_CD VARCHAR2(30 BYTE) NOT NULL,
AUDIT_ACTION VARCHAR2(255 BYTE),
LAST_UPDATE_USER_LOGON_ID VARCHAR2(12 BYTE),
LOCAL_TM VARCHAR2(18 BYTE) NOT NULL,
LOCAL_TIME_ZN_NM VARCHAR2(70 BYTE) NOT NULL,
LOCAL_DAYLIGHT_IN CHAR(1 BYTE) NOT NULL,
FPRINT VARCHAR2(30 BYTE)
)

Attached is a file with the data.

Marcel Koert

Attachment: TMS_MESSAGE_AUDIT_LOG.zip
(Size: 110.76KB, Downloaded 574 times)

Report message to a moderator

Re: Problem with a query. [message #286052 is a reply to message #286013]

Thu, 06 December 2007 06:38

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Did you try with Other way around of outerjoin with aggregate function ? Why couldn't you paste the sample script instead ?

Are we supposed to work for your solution ?

Thumbs Up

Rajuvan

Report message to a moderator

Re: Problem with a query. [message #286058 is a reply to message #286052]

Thu, 06 December 2007 06:45

mklynx
Messages: 17
Registered: December 2007

Junior Member

Hi

Thanks.

While i was replying i was also trying to get it to work and it did. Your "OUTER JOIN" was on the spot.

Marcel Koert

Report message to a moderator

Re: Problem with a query. [message #286074 is a reply to message #286058]

Thu, 06 December 2007 07:13

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Post a test case (create table and insert statements) and not this useless unformated listing.

Regards
Michel

Report message to a moderator

Previous Topic:	Dropping index
Next Topic:	Need to Split select statement column output into multiple lines

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 08 05:47:55 CST 2024