Home » SQL & PL/SQL » SQL & PL/SQL » query problem (oracle 10g)
query problem [message #403228] |
Thu, 14 May 2009 06:13  |
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   |
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   |
cookiemonster
Messages: 13963 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   |
joy_division
Messages: 4963 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 #403489 is a reply to message #403488] |
Fri, 15 May 2009 17:06  |
pablolee
Messages: 2882 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
|
|
|
Goto Forum:
Current Time: Thu Feb 06 23:01:11 CST 2025
|