Home » SQL & PL/SQL » SQL & PL/SQL » query problem (oracle 10g)
query problem [message #403228] Thu, 14 May 2009 06:13 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
SELECT MODULE_ID,
  MODULE_NAME,
  EVENT_ID,
  EVENT_NAME,
  DATE_TIME,
  USER_LOGIN,
  PATIENT_ID,
  REFERENCE_ID,
  USER_IP_ADDRESS,
  TRAN_STATUS,
  USER_NAME
FROM
  (SELECT MODULE_ID,
    MODULE_NAME,
    EVENT_ID,
    EVENT_NAME,
    DATE_TIME,
    USER_LOGIN,
    PATIENT_ID,
    REFERENCE_ID,
    USER_IP_ADDRESS,
    TRAN_STATUS,
    USER_NAME
  FROM
    (SELECT MODULE_ID,
      MODULE_NAME,
      EVENT_ID,
      EVENT_NAME,
      DATE_TIME,
      USER_LOGIN,
      PATIENT_ID,
      REFERENCE_ID,
      USER_IP_ADDRESS,
      TRAN_STATUS,
      USER_NAME
    FROM
      (SELECT MODULE_ID,
        MODULE_NAME,
        EVENT_ID,
        EVENT_NAME,
        DATE_TIME,
        USER_LOGIN,
        PATIENT_ID,
        REFERENCE_ID,
        USER_IP_ADDRESS,
        TRAN_STATUS,
        USER_NAME
      FROM
        (SELECT AML.MODULE_ID,
          MET.MODULE_NAME,
          AML.EVENT_ID,
          AEL.EVENT_NAME,
          TO_CHAR(AML.DATE_TIME,'MM-DD-YYYY HH:MI:SS AM') AS DATE_TIME,
          AML.USER_LOGIN,
          AML.PATIENT_ID,
          AML.REFERENCE_ID,
          AML.USER_IP_ADDRESS,
          AML.TRAN_STATUS,
          USERS.USER_NAME
        FROM EMRAuditTrial AML
        LEFT OUTER JOIN EMRAuditEventLkup AEL
        ON AEL.EVENT_ID = AML.EVENT_ID
        LEFT OUTER JOIN EMRAuditModuleLkup MET
        ON MET.MODULE_ID = AML.MODULE_ID
        LEFT OUTER JOIN USERS
        ON AML.USER_LOGIN       = USERS.USER_LOGIN
        WHERE ( AML.USER_LOGIN IN ('superadmin', 'ezemrx','ezemrx')
        OR AML.USER_LOGIN      IN
          (SELECT DISTINCT USER_LOGIN FROM EMRUSERROLELOCATION WHERE LOCATION_ID = 1528
          ) )
        AND TRUNC(AML.DATE_TIME) BETWEEN TO_DATE('14-MAY-2009 10:00:00','dd-MON-yyyy')
         AND TO_DATE('14-MAY-2009 10:50:00', 'dd-MON-yyyy')
      AND AEL.EVENT_TYPE  IN (1,2)
      AND MET.MODULE_TYPE IN (1,2)
      ORDER BY MODULE_NAME ASC
      )
    WHERE rownum <= 100
    )
  ORDER BY MODULE_NAME DESC
  )
WHERE rownum <= 100 ) ORDER BY DATE_TIME DESC




hi i am facing problem with my query van anyone help me out the error is

SQL Error: ORA-01830: date format picture ends before converting entire input string
01830. 00000 - "date format picture ends before converting entire input string"
*Cause:
*Action:

[Updated on: Thu, 14 May 2009 06:15]

Report message to a moderator

Re: query problem [message #403229 is a reply to message #403228] Thu, 14 May 2009 06:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
First, you do realise that you can simplify your query down to:
SELECT MODULE_ID,
        MODULE_NAME,
        EVENT_ID,
        EVENT_NAME,
        DATE_TIME,
        USER_LOGIN,
        PATIENT_ID,
        REFERENCE_ID,
        USER_IP_ADDRESS,
        TRAN_STATUS,
        USER_NAME
      FROM
        (SELECT AML.MODULE_ID,
          MET.MODULE_NAME,
          AML.EVENT_ID,
          AEL.EVENT_NAME,
          TO_CHAR(AML.DATE_TIME,'MM-DD-YYYY HH:MI:SS AM') AS DATE_TIME,
          AML.USER_LOGIN,
          AML.PATIENT_ID,
          AML.REFERENCE_ID,
          AML.USER_IP_ADDRESS,
          AML.TRAN_STATUS,
          USERS.USER_NAME
        FROM EMRAuditTrial AML
        LEFT OUTER JOIN EMRAuditEventLkup AEL
        ON AEL.EVENT_ID = AML.EVENT_ID
        LEFT OUTER JOIN EMRAuditModuleLkup MET
        ON MET.MODULE_ID = AML.MODULE_ID
        LEFT OUTER JOIN USERS
        ON AML.USER_LOGIN       = USERS.USER_LOGIN
        WHERE ( AML.USER_LOGIN IN ('superadmin', 'ezemrx','ezemrx')
        OR AML.USER_LOGIN      IN
          (SELECT DISTINCT USER_LOGIN FROM EMRUSERROLELOCATION WHERE LOCATION_ID = 1528
          ) )
        AND TRUNC(AML.DATE_TIME) BETWEEN TO_DATE('14-MAY-2009 10:00:00','dd-MON-yyyy')
         AND TO_DATE('14-MAY-2009 10:50:00', 'dd-MON-yyyy')
      AND AEL.EVENT_TYPE  IN (1,2)
      AND MET.MODULE_TYPE IN (1,2)
      ORDER BY MODULE_NAME ASC
      )
    WHERE rownum <= 100
    ORDER BY date_time_desc

All the outer layers did was to re-order the rows, and then pick the same 100 rows that you already had, and then to re-order them again.

As to your problem, you have this line :
TO_DATE('14-MAY-2009 10:00:00','dd-MON-yyyy')

You have a string containing a date and time, but only specify a date in your format mask

Use a format mask that includes a time, such as 'DD-MON-yyyy hh24:mi:ss'
Re: query problem [message #403242 is a reply to message #403228] Thu, 14 May 2009 07:10 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
That'll fix the oracle error but it won't get any data back.

The time portion of trunc(<any date you like>) is midnight by definition.
Therefore it can't really fall between 10am and 10.50am.
Re: query problem [message #403249 is a reply to message #403242] Thu, 14 May 2009 07:47 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
cookiemonster wrote on Thu, 14 May 2009 08:10

The time portion of trunc(<any date you like>) is midnight by definition.
Therefore it can't really fall between 10am and 10.50am.


Another great catch by cookiemonster.
Re: query problem [message #403488 is a reply to message #403228] Fri, 15 May 2009 16:59 Go to previous messageGo to next message
fall_in_luv
Messages: 5
Registered: January 2009
Location: Egypt
Junior Member
AND TO_DATE('14-MAY-2009 10:50:00', 'dd-MON-yyyy')


you have to put the format of time too!!
Re: query problem [message #403489 is a reply to message #403488] Fri, 15 May 2009 17:06 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
fall_in_luv wrote on Fri, 15 May 2009 22:59
AND TO_DATE('14-MAY-2009 10:50:00', 'dd-MON-yyyy')


you have to put the format of time too!!

ECHO ECHO ECho Echo echo
Previous Topic: Statement execution speed depends on single statement or stored procedure?
Next Topic: Output of the query
Goto Forum:
  


Current Time: Sat Dec 10 08:47:16 CST 2016

Total time taken to generate the page: 0.08504 seconds