Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Expression question Post Reply Post New Topic
Author Message
crystalsonic
Groupie
Groupie


Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
Quote crystalsonic Replybullet Topic: Expression question
     Posted: 07 Nov 2012 at 6:18am
I am trying to figure out what a report is doing that somebody else wrote. This is the expression used in the Record Selection area:
{Patients.Surname} like "*? *"
and {Episodes.DateCreated} in {?From} to {?To}
 
This is the SQL query:

 SELECT "Episodes"."DateColl", "Episodes"."EpisodeNo", "Episodes"."ReqNo", "Patients"."Surname", "Episodes"."DateCreated"
 FROM   "SQLUser"."Episodes" "Episodes", "SQLUser"."Patients" "Patients"
 WHERE  ("Episodes"."UnitNumber"="Patients"."UnitNumber") AND "Patients"."Surname" LIKE '%_ %' AND "Episodes"."DateCreated"={d '2012-10-24'}
 ORDER BY "Patients"."Surname"
 
 
I understand that the report prompts for the date created, but I do not understand what the surname part is doing.
 
Any thoughts? Thank you!
IP IP Logged
comatt1
Senior Member
Senior Member
Avatar

Joined: 19 May 2011
Online Status: Offline
Posts: 337
Quote comatt1 Replybullet Posted: 07 Nov 2012 at 6:25am
Does that even work?
{Patients.Surname} like "*? *"

---- also is it a '-' not an underscore, because it looks like he may be looking for hyphenated last names.

---------

It should be - if its a passed parameter
{Patients.Surname} like '*' + {?Field} + '*'

if he just wanted patients with '_'
he could have done
{Patients.Surname} like '*' + '_' + '*'

and skipped the logic in the sql query
AND "Patients"."Surname" LIKE '%_ %'

Looks like he is pulling all surnames with an '_' in them, and then wanting to only show non-null values maybe. Dell or Lock would have a better idea.

However, I don't like how he set it up.

Edited by comatt1 - 07 Nov 2012 at 6:33am
IP IP Logged
crystalsonic
Groupie
Groupie


Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
Quote crystalsonic Replybullet Posted: 07 Nov 2012 at 7:04am
As far as I know it works. This is what the report returns:
 
Patient Last Name Report
10/24/2012 To ######
Surname DateColl EpisodeNo ReqNo
ROSE SR 10/23/2012 X0719295 1234567
ROSE SR 10/23/2012 X0719296 7654321
IP IP Logged
crystalsonic
Groupie
Groupie


Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
Quote crystalsonic Replybullet Posted: 07 Nov 2012 at 7:20am
They refer to the report as the one with double names. I think it is pulling any records that have a space...
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 07 Nov 2012 at 8:00am
I am not sure why they used the "?"
IN crystal the ? repalces only one single character with a like
{field} like "ab?" works for values of ab1,abl, abr, but not values of ab12, abc 4, etc.
 
 
so I would assume
{Patients.Surname} like "*? *"
is the same as
{Patients.Surname} like "* *"
Both return any values with 2 strings seperated by a space.
 
I believe that Sql uses the _ for a single character repalcement in a Like where crystal uses the ?
 


Edited by DBlank - 07 Nov 2012 at 8:01am
IP IP Logged
comatt1
Senior Member
Senior Member
Avatar

Joined: 19 May 2011
Online Status: Offline
Posts: 337
Quote comatt1 Replybullet Posted: 07 Nov 2012 at 8:17am
Thanks as always, another lesson from the great Dell
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.