Pushing Select criteria to Database - redux 3

[Turkbear]

Hi all,
( especially Synapse) -

I am trying to insure that the selection criteria I am using is passed to the Database
( CR 8.5, Oracle 9.2, Native Connection):

In its simple form it is:

{RCA_EMPLOYEE_TIMESHEETS.DEPTID} startswith {?OrgNbr}
  and
{RCA_EMPLOYEE_TIMESHEETS.PAY_END_DT} In {?StartDate} to {?EndDate}

This works as expected and the Sql contains the
correct criteria..

However, if I try to test for a special case of {?OrgNbr}
It never gets to the database, just the Date range check does..

I have tried:

(
IF {?OrgNbr} <> 'CO' then
{RCA_EMPLOYEE_TIMESHEETS.DEPTID} startswith {?OrgNbr}
Else
IF {?OrgNbr} = 'CO' then
{RCA_EMPLOYEE_TIMESHEETS.DEPTID} startswith 'T790'
)
  and
{RCA_EMPLOYEE_TIMESHEETS.PAY_END_DT} In {?StartDate} to {?EndDate}

Which I thought would insure pass-through..but I must be missing something, since it does not.

Any thoughts on recoding?

Thanks..

 

[kskid]

Create a separate formula

//@DEPTID
IF {?OrgNbr} <> 'CO' then
{?OrgNbr}
Else
'T790'

Then in your record selection, use the following

{RCA_EMPLOYEE_TIMESHEETS.DEPTID} startswith @DEPTID
and
{RCA_EMPLOYEE_TIMESHEETS.PAY_END_DT} In {?StartDate} to {?EndDate}

 

[Turkbear]

Thanks..That did the trick..

( Here comes the but...)

Whan the value is CO I really need to use a series of values for the startswith clause..I'll try a few things and see if I can get it to do that..


Thanks again for the fast and accurate response..( star for you)

 

[Turkbear]

Hi again..After re-checking with the designers of this report ( I was trying to tweak it for them), I found out that the special case of 'CO' only involved 6 additional location codes..I could code these just like the other {?OrgNbr} values, so it works in the simple method I posted..

Thanks again for the help, and if any of you do have any ideas to solve this if I DID need to have multiple values for the special case, please post..But it is now just for general interest.

 

[synapsevampire]

As Witchita demonstrated, I often have to build seperate formulas to ensure SQL passthrough, especially when using user friendly prompts for date ranges, such as This Month, or This Quarter, etc.

I'd need additional information to better understand your intent with regards to the series of values for the startswith clause, is this to be hard coded or something from a table, or?

-k

 

[Turkbear]

Hi,
I was able to hardcode it into the Default parameter list ( with appropriate descriptive Text)since it was only a few values...But, in general here is what I am tying to do:

In our Organization, our sections are identified by a dept_nbr and its format is T79xxxx - all have T79 to start and the last 4 digits futher refine the location..
If the 4th number is a 9 then the office is in one of our outstate locations which is identified by the 5th number
( for instance T799123 would, in our system, be an outstate location, District 1).
If it is a 7 then it is in what we call the Metro District..
So, for most locations I can use

If dept_nbr startswith {?Parameter}

(?Parameter being,for instance, T7991, if the user wanted District 1)

The special case ( our Central Office locations ) requires testing the 4th number to see if it is NOT 7 or 9
( or IS one of 0,1,2,3,4,6,8 )

Hence the need for an If type test that still passes the criteria to the Oracle database..

 

[rosemaryl]

Dear Turnkbear,

First, I am getting confused between the various posts ... so maybe a restatement of the issue will help.

I have read through all the foregoing posts and your latest post more than 4 times and I am failing to see the issue.

Could you restate this?

For Example ... I need to always pull data where the first three characters of the department ID are 'T79', the 4th digit indicates the location and users want to run the report for a specific location and district (5th number).

My issue is .....

Thanks,

ro





Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Turkbear]

Hi,
Sorry 'bout the lack of clarity..

In its simplest form, I need to use a Parameter to select one of our organizational units - The selection criteria is based on various parts of the 7 character Dept_nbr ( T79xxxx).
Because of how it is structured I can use a
startswith statement ( which is passed to Oracle as a LIKE
operator just fine),for all of our locations except for those units in our Central Office site..These are identified by the 4th character in the Dept_nbr but it can be 0,1,2,3,4,6, or 8, so the startswith function would need to be different for this special case. When I use any form of IF..Then..Else ( or Select Case ) statement in the record selection formula to distinguish which startswith statement to use, based on the parameters value , the criteria is not passed to the database.

Hope it clarified things..

 

[rosemaryl]

Dear Turkbear,

Yep - it is probably just me - I'm sick so my head is fuzzy.

I think the issue is probably the startswith so let's remove it from the equation.

So, would it be correct to state that we always want records that start with the first three characters 'T79'?

I am assuming that the parameter contains some portion of the string you are selection on but you didn't post what is in it?

What is actually in the parameter that is being passed?

Please,
ro



Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Turkbear]

Hi,
The parameters are
Value Description
T7991 - District 1
T7992 - District 2
T7993 - District 3
etc through T7998 - District 8
also
T79 - All
T797 - Metro District
T799 - All Districts except Metro

I need to pass
T790,T791,T792,T793,T794,T796 and T798 - Central Office

 

[synapsevampire]

I can't test right now, and I agree with Ro that it's all a little tricky to follow.

How do you determine what it is that you need to pass from the parameter chosen?

Anyway, here's an example that passes from a report here, just adopt the structure:

(
(
if minimum({?Course Range Requested}) <> 'CO' then
{PACOURSE.COURSE} startswith minimum({?Course Range Requested})
)
or
(
IF minimum({?Course Range Requested}) = 'CO' then
{PACOURSE.COURSE} startswith 'T790'
)
)
and
...

Passing feels like pure voodoo at times.

-k

 

[Turkbear]

Hi Synapse..
Your solution worked ( although I modified it to fit my stuff - see below).
Thanks..I am not sure how it differed from some I tried,
( maybe the OR part)
but, as I tell some of the folks I train


"I do not answer WHY questions"



Thanks


PS - Here is the code ( using actual field and parameter names) I settled on:

(
(
if {?OrgNbr} <> '00' then
{RCA_EMPLOYEE_TIMESHEETS.DEPTID} startswith {?OrgNbr}
)
or
(
IF {?OrgNbr} = '00' then
{RCA_EMPLOYEE_TIMESHEETS.DEPTID} startswith ['T790','T791','T792','T793','T794','T796','T797','T798']
)

)

and

{RCA_EMPLOYEE_TIMESHEETS.PAY_END_DT} In {?StartDate} to {?EndDate}

Thanks again..

 

[synapsevampire]

Nae worries, I've been through this so many times now that I just keep trying combinations of parens adn rethinking the logic until the %#^@(*& thing starts passing...

It seems to help to keep an eye of newt and some albino chicken blood on your monitor.

-k

 

[rosemaryl]

Dear Turkbear,

I am so glad that worked for you - kudos to SV.

But, now I am even more confused. Where did OrgNbr = 'OO' come from?

I was trying to work out a solution based on what you posted was in the parameter value ....

Also, the difference between what you posted earlier and SV's solution is that the whole statement with each or is wrapped in parenthesis forcing the evaluation of all the or statements within one statement.

Best regards,

ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Turkbear]

Hi,
Sorry, It is just a 'flag' to indicate that the CO criteria should be used. It could have been anything other than the ones passed if Central Office was not the selected location.
Remember ( many posts ago..) the default values and descriptions for this parameter are hard coded in the Report as:

Value Description
T7991 - District 1
T7992 - District 2
T7993 - District 3, etc.
through,
T7998 - District 8
also created 3 others for flexibility
T79 - All
T797 - Metro District
T799 - All Districts except Metro

So I just added -

00 - Central Office

that way the user sees Central Office as the choice and the 00 is seen by the selection formula.


Sorry if it clouded it up even more...

Thanks for the efforts..

 

[synapsevampire]

Ro: That's certainly my first instinct with any clause in the record selection formula, wrap it in parens. But I've had parens PREVENT pass through, so it's not always a given.

As Turk mentioned, I also changed it to an OR clause to allow for this type of construct.

-k

 

[rosemaryl]

Dear TurkBear:

Ah .. that certainly explains it ...
and

Dear SV:

Of course you are right there ... I have just gotten in the habit of parens and when that doesn't work ... start from scratch and build up.

I have actually had problems using startswith ... so usually try to find another solution!

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[fredp1]

Hi all,

I've been using 8.5 and SQl Server ODBC and haven't (never?) had any luck passing sql to the db whenever I had to use If then else structures as shown below.
(
if condition then
{fieldname} = "some value"
else
{fieldname =} "another value"
)

I suspect alot depends on the db driver as many people have used the faq that synapsevampire kindly posted with great results.

What had always worked for me is having the if then else of the 'right side' of the field name e.g

(
{fieldname} = if condition then
"some value"
else
"another value"
)

I don't know if its just my environment, but i've always had succes with the above format.
Its almost like having the if then else in a formalue field but hard coded in the selection criteria.

I hope that helps

Cheers
Fred

 

[synapsevampire]

I agree, Fred, that does a good job as well, although occasionally it hiccups as well...

The best methods I've found are to wrap everything in parens and distinctly qualify everything.

-k

 

[Turkbear]

Hi,
Thanks to everyone for diving in on this issue, it is what makes these forums the best resource I have found in 20 years of dealing with IT stuff.

I am more and more moving to Synapse's view that Stored Procedures and Views, when avaiable, are the better choice for insuring that the database does the work before returning the records. ( Of course, that requires well-written SPs and Views or you have just moved the problem to someplace else)..

Thanks again...