Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 pagination query problem

Author  Topic 

rajasekhar857
Constraint Violating Yak Guru

396 Posts
Posted - 2010-05-26 : 05:26:21
hi query is like this

SELECT MODULE_ID,MODULE_NAME,ltrim(rtrim(CONVERT(VARCHAR,DATE_TIME,110))) AS DATE_TIME 
FROM
  (SELECT TOP 100 MODULE_ID,MODULE_NAME,ltrim(rtrim(CONVERT(VARCHAR,DATE_TIME,110))) AS DATE_TIME 
   FROM
     (SELECT TOP 100 MODULE_ID, MODULE_NAME,ltrim(rtrim(CONVERT(VARCHAR,DATE_TIME,110))) AS DATE_TIME 
         FROM
           (SELECT TOP 999999999 MODULE_ID,MODULE_NAME,ltrim(rtrim(CONVERT(VARCHAR,DATE_TIME,110))) AS DATE_TIME 
             FROM
			(SELECT AML.MODULE_ID,MET.MODULE_NAME,AML.EVENT_ID,AEL.EVENT_NAME,
			  ltrim(rtrim(CONVERT(VARCHAR,AML.DATE_TIME,105))) + ' ' + rtrim(ltrim(REPLACE(REPLACE(substring(CONVERT(VARCHAR,AML.DATE_TIME,9),13,14),':000AM',' AM'),':000PM',' PM'))) 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
			  (SELECT DISTINCT USER_LOGIN  FROM EMRUSERROLELOCATION
			  WHERE LOCATION_ID = 1831501 )
			AND AEL.EVENT_TYPE  IN (0,2) AND MET.MODULE_TYPE IN (0,2)) X
      ORDER BY DATE_TIME  ASC
      ) AS NEWTBL1
    ORDER BY DATE_TIME DESC
    ) AS NEWTBL2
  ) Z
ORDER BY DATE_TIME ASC





here date_time column is not sorting data properly like it is sorting data as

10-05-2010 4:48:16 PM
13-05-2010 11:57:24 AM
17-05-2010 6:34:29 PM
22-04-2010 6:47:08 PM
30-04-2010 5:07:35 PM

what's wrong with query?

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-05-26 : 05:52:33
Since your DATE_TIME is converted to varchar the sort cannot work in another way.
Try ORDER BY convert(datetime,DATE_TIME)

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

rajasekhar857
Constraint Violating Yak Guru

396 Posts
Posted - 2010-05-26 : 06:06:44
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
where i changed ORDER BY DATE_TIME to ORDER BY convert(datetime,DATE_TIME)
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-05-26 : 06:07:56
quote:
Originally posted by webfred

Since your DATE_TIME is converted to varchar the sort cannot work in another way.
Try ORDER BY convert(datetime,DATE_TIME)

No, you're never too old to Yak'n'Roll if you're too young to die.

It should be applied to all derived tables

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-05-26 : 06:10:11
What happens when you try this?
SELECT MODULE_ID,MODULE_NAME,ltrim(rtrim(CONVERT(VARCHAR,DATE_TIME,110))) AS DATE_TIME1
FROM
  (SELECT TOP 100 MODULE_ID,MODULE_NAME,DATE_TIME 
   FROM
     (SELECT TOP 100 MODULE_ID, MODULE_NAME,DATE_TIME 
         FROM
           (SELECT TOP 999999999 MODULE_ID,MODULE_NAME,ltrim(rtrim(CONVERT(VARCHAR,DATE_TIME,110))) AS DATE_TIME 
             FROM
			(SELECT AML.MODULE_ID,MET.MODULE_NAME,AML.EVENT_ID,AEL.EVENT_NAME,
			  ltrim(rtrim(CONVERT(VARCHAR,AML.DATE_TIME,105))) + ' ' + rtrim(ltrim(REPLACE(REPLACE(substring(CONVERT(VARCHAR,AML.DATE_TIME,9),13,14),':000AM',' AM'),':000PM',' PM'))) 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
			  (SELECT DISTINCT USER_LOGIN  FROM EMRUSERROLELOCATION
			  WHERE LOCATION_ID = 1831501 )
			AND AEL.EVENT_TYPE  IN (0,2) AND MET.MODULE_TYPE IN (0,2)) X
      ORDER BY DATE_TIME  ASC
      ) AS NEWTBL1
    ORDER BY DATE_TIME DESC
    ) AS NEWTBL2
  ) Z
ORDER BY DATE_TIME ASC


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

rajasekhar857
Constraint Violating Yak Guru

396 Posts
Posted - 2010-05-26 : 06:16:02
here date_time column is not sorting data properly like it is sorting data as
DATA COMING LIKE THIS

10-05-2010 4:48:16 PM
13-05-2010 11:57:24 AM
17-05-2010 6:34:29 PM
22-04-2010 6:47:08 PM
30-04-2010 5:07:35 PM
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-05-26 : 06:46:59
[code]
SELECT MODULE_ID,MODULE_NAME,ltrim(rtrim(CONVERT(VARCHAR,DATE_TIME,110))) AS DATE_TIME1
FROM
(SELECT TOP 100 MODULE_ID,MODULE_NAME,DATE_TIME
FROM
(SELECT TOP 100 MODULE_ID, MODULE_NAME,DATE_TIME
FROM
(SELECT TOP 999999999 MODULE_ID,MODULE_NAME,DATE_TIME
FROM
(SELECT AML.MODULE_ID,MET.MODULE_NAME,AML.EVENT_ID,AEL.EVENT_NAME,
AML.DATE_TIME 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
(SELECT DISTINCT USER_LOGIN FROM EMRUSERROLELOCATION
WHERE LOCATION_ID = 1831501 )
AND AEL.EVENT_TYPE IN (0,2) AND MET.MODULE_TYPE IN (0,2)) X
ORDER BY DATE_TIME ASC
) AS NEWTBL1
ORDER BY DATE_TIME DESC
) AS NEWTBL2
) Z
ORDER BY DATE_TIME ASC
[/code]

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-05-26 : 07:18:34
Is it just me?

SELECT TOP 999999999 MODULE_ID,MODULE_NAME,DATE_TIME
FROM (
...
) AS X
ORDER BY DATE_TIME ASC

then from that

SELECT TOP 100 MODULE_ID, MODULE_NAME,DATE_TIME
FROM (
...
) AS NEWTBL1
ORDER BY DATE_TIME DESC

then from that

SELECT TOP 100 MODULE_ID,MODULE_NAME,DATE_TIME
FROM (
...
) AS NEWTBL2

then from that

SELECT Columns
FROM (
...
) AS X
ORDER BY DATE_TIME ASC

what are all these nests trying to do?

NEWTBL2 has no ORDER BY so will give a random selection ...

But NEWTBL1 and NEWTBL2 both SELECT TOP 100 - its the same rows, nothing left out isn't it? Therefore superfluous??

and the SELECT TOP 999999999 for [X]? Is that trying to select everything? (if so use TOP 100 PERCENT) or genuinely leave anything out of there are more than 999,999,999 rows? (that's a big database, and the query will take forever ...).

The query for [X] is selecting a bunch of columns that are then not used, so that may slow things down too ...

Maybe I am missing something, but why can't the inner [X] query just SELECT TOP 100 ... ORDER BY DATE_TIME ASC ?

Here's my re-formatted version in case useful

SELECT MODULE_ID,MODULE_NAME,ltrim(rtrim(CONVERT(VARCHAR,DATE_TIME,110))) AS DATE_TIME1
FROM
(
	SELECT TOP 100 MODULE_ID,MODULE_NAME,DATE_TIME 
	FROM
	(
		SELECT TOP 100 MODULE_ID, MODULE_NAME,DATE_TIME 
		FROM
		(
			SELECT TOP 999999999 MODULE_ID,MODULE_NAME,DATE_TIME 
			FROM
			(
				SELECT	AML.MODULE_ID,MET.MODULE_NAME,AML.EVENT_ID,AEL.EVENT_NAME,
					AML.DATE_TIME 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 AS AEL 
						ON AEL.EVENT_ID = AML.EVENT_ID
					LEFT OUTER JOIN EMRAuditModuleLkup AS MET
						ON MET.MODULE_ID = AML.MODULE_ID
					LEFT OUTER JOIN USERS 
						ON AML.USER_LOGIN  = USERS.USER_LOGIN
				WHERE AML.USER_LOGIN IN
					(
						SELECT DISTINCT USER_LOGIN  FROM EMRUSERROLELOCATION
						WHERE LOCATION_ID = 1831501
					)
					AND AEL.EVENT_TYPE  IN (0,2) AND MET.MODULE_TYPE IN (0,2)
			) X
			ORDER BY DATE_TIME  ASC
		) AS NEWTBL1
		ORDER BY DATE_TIME DESC
	) AS NEWTBL2
) AS Z
ORDER BY DATE_TIME ASC
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-05-27 : 03:43:28
I would be surprised if you get reply from the OP

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-05-27 : 04:39:27
Oh, OK. Thanks Madhi I'll not spend time then ...
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-05-27 : 05:00:44
quote:
Originally posted by Kristen

Oh, OK. Thanks Madhi I'll not spend time then ...

Look at all his posts, he stops as soon as he gets workable solution

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site