Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Problem with In Clause() CR 11.5 Post Reply Post New Topic
Author Message
mjoshi2978
Newbie
Newbie
Avatar

Joined: 14 Sep 2009
Location: United States
Online Status: Offline
Posts: 9
Quote mjoshi2978 Replybullet Topic: Problem with In Clause() CR 11.5
     Posted: 14 Sep 2009 at 10:42am
Guys !
 
I am struggling with this issue since long. I have a report parameter of type "string" which is of "static" type.  I am trying to pass out multivalue comma separated id's which can give me the results filtering all of these ids and display the matching ones on report. When I pass parameters for  {?KPNUID} say like "KP12345,NU67890" then it filters the results perfectly and displays the result for both the above ids onto my report. But the issue comes when I try to pass parameters for {?FRM_ID}. I am passing values for e.g. as "12345,67890". Here it filters for all the permutation and combination of firm ids like "1", "12","123", "1234", "23", "234", "34", "345", "2345" and likewise. I have both these fields viz KPNUID and FIRMID as varchar type in my table. Below is the code for my record selection formula:
 
----------------------------------------------------------------------------
StringVar kpam = "";
StringVar frmid="";
 
//condition for KP NUID
(
kpam := iif ({?KPNUID} = "", "", {?KPNUID});
if (kpam = "") then
    (IsNull({T_RPT_BOB_CONTRACT.KPAM_NUID}) = true OR {T_RPT_BOB_CONTRACT.KPAM_NUID} <> '')
else 
    {T_RPT_BOB_CONTRACT.KPAM_NUID} in {?KPNUID}
)
 
AND

//condition for FIRM ID
(
frmid := iif ({?FRM_ID} = "", "", {?FRM_ID});
if (frmid = "") then
     (IsNull({T_RPT_BOB_CONTRACT.FIRM_ID}) = true OR {T_RPT_BOB_CONTRACT.FIRM_ID} <> '')
else 
     {T_RPT_BOB_CONTRACT.FIRM_ID} in {?FRM_ID}
)

-----------------------------------------------------------------------
 
Please help me..and respond me soon on how to resolve this issue.
 


Edited by mjoshi2978 - 14 Sep 2009 at 4:45pm
MVJ
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 15 Sep 2009 at 6:36am
not the way that I would have done it...haven't used 'in' much, but it is obviously giving false positives.
 
how about something like:
//condition for FIRM ID
(
frmid := iif ({?FRM_ID} = "", "", "," + {?FRM_ID} + ",");
if (frmid = "") then
     (IsNull({T_RPT_BOB_CONTRACT.FIRM_ID}) = true OR {T_RPT_BOB_CONTRACT.FIRM_ID} <> '')
else 
     instr({?FRM_ID}, ","+{T_RPT_BOB_CONTRACT.FIRM_ID}+",") > 0
)
 
 
this will make a unique a substring to search for, instead of any partial part of the string.
 
HTH
IP IP Logged
mjoshi2978
Newbie
Newbie
Avatar

Joined: 14 Sep 2009
Location: United States
Online Status: Offline
Posts: 9
Quote mjoshi2978 Replybullet Posted: 15 Sep 2009 at 7:14am
Hi HTH,
 
Thanks for your response..I will try this out...and let you know today itself..
 
 
 
 
MVJ
IP IP Logged
mjoshi2978
Newbie
Newbie
Avatar

Joined: 14 Sep 2009
Location: United States
Online Status: Offline
Posts: 9
Quote mjoshi2978 Replybullet Posted: 15 Sep 2009 at 8:10am

Dude,

 
I tried your below response for using Instr() function and it run perfectly well. But as with instr() function, it will return a numeric value stating the index of the string where the comparison matches. My objective in the above problem is completely different. What I am trying to achieve is compare the string comma separated parameters with the actual table values and should filter only those records which matches. Say like I have 2 different records with Firm Id as "000001" and "000002" in my tables. So when I pass these values as input parameters say "000001,000002" then it should return me those 2 records displayed in the report. This works good with In() Clause if I am passing some chars with it like "K0001,S0005", but does not work with pur numeric values as above "000001,000002".
 
I hope you understand my concern...
 
Thanks anyways for trying it out..and if you do have any other alternative then please let me know...
 
 
MVJ
IP IP Logged
mjoshi2978
Newbie
Newbie
Avatar

Joined: 14 Sep 2009
Location: United States
Online Status: Offline
Posts: 9
Quote mjoshi2978 Replybullet Posted: 15 Sep 2009 at 9:11am

Guys,

 
Finally I was able to solve the mystery on my own. I used arrays and split() function to make it work as I was expecting. I used a workaround as below.
 
StringVar Array frmid_arr;
 
//condition for FIRM ID
(
frmid := iif ({?FRM_ID} = "", "", "," + {?FRM_ID} + ",");
//split the parameters and store it in an array
frmid_arr := split({?FRM_ID},",");
if (frmid = "") then
     (IsNull({T_RPT_BOB_CONTRACT.FIRM_ID}) = true OR {T_RPT_BOB_CONTRACT.FIRM_ID} <> '')
else
//compare your table values with the array  
    cstr({T_RPT_BOB_CONTRACT.FIRM_ID}) in (frmid_arr)
)
 
Thanks HTH and others who tried to find me a solution or atleast had glympse into the problem...
 
MVJ
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 15 Sep 2009 at 9:14am
it all depends on where you are usint this.  I figured it was in a filter statement, so it was returning true or false, which usually how you filter.
IP IP Logged
mjoshi2978
Newbie
Newbie
Avatar

Joined: 14 Sep 2009
Location: United States
Online Status: Offline
Posts: 9
Quote mjoshi2978 Replybullet Posted: 15 Sep 2009 at 12:10pm

Thanks Lockwelle,

 
I really appreciate your help on it. I will do post other topics that are of my concern.
 
 


Edited by mjoshi2978 - 15 Sep 2009 at 12:10pm
MVJ
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.