Select query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Select query

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-01-19 : 07:20:07

Hi,

I have the following select code which works fine..

SELECT     TOP (100) PERCENT dbo.qryPatientInfoDUMP.PatID, dbo.qryPatientInfoDUMP.PatEID, dbo.qryPatientInfoDUMP.PracticeGPID, 
                      GPRDTech.gprdsql.TblPracDetails.GoldPracID, dbo.tblpatdetails_May.vision_patid, dbo.tblpatdetails_May.goldpracid AS GOLDPRACIDnw
FROM         GPRDTech.gprdsql.TblPracDetails INNER JOIN
                      dbo.qryPatientInfoDUMP ON GPRDTech.gprdsql.TblPracDetails.prac_eid = dbo.qryPatientInfoDUMP.PracticeEID AND 
                      GPRDTech.gprdsql.TblPracDetails.prac_no = dbo.qryPatientInfoDUMP.PracticeGPID INNER JOIN
                      dbo.tblpatdetails_May ON dbo.qryPatientInfoDUMP.PatID = dbo.tblpatdetails_May.vision_patid AND 
                      GPRDTech.gprdsql.TblPracDetails.GoldPracID = dbo.tblpatdetails_May.goldpracid

Now, I want to order by GPRDTech.gprdsql.TblPracDetails.GoldPracID.. the problem am facing where ever I add order by GPRDTech.gprdsql.TblPracDetails.GoldPracID .. I receive the SQL Execution Error - Timeout Expired...

Secondly, I want dbo.tblpatdetails_May.vision_patid to be case sensitive..

Therefore, for example for dbo.qryPatientInfoDUMP.PatID = 00XL
ONLY record with 00XL from dbo.tblpatdetails_May.vision_patid displayed.

Presently, it output 00XL , 00xL, 00Xl, 00xl.. Therefore, 4 records instead..

Any help please will be greatly appreciated..

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-19 : 08:12:00

Have you tried to remove the TOP clause? Seems to be useless...

For case sensitive matches you can use COLLATE with a case sensitive collation.

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

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-01-19 : 08:13:41

what do youu mean with a case sensitive collation

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-19 : 08:25:43

In object explorer when you right click the database then you will see the collation.
For example 'SQL_Latin1_General_CP1_CI_AS'.
In this example the 'CI' part means CaseInsensitive.
'CS' would be case sensitive.

An example to use collate:
dbo.qryPatientInfoDUMP.PatID = dbo.tblpatdetails_May.vision_patid collate SQL_Latin1_General_CP1_CS_AS

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

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-01-19 : 09:19:32

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources