Modified multi param not showing in SQL

[pandpp]

Hello.

Using CR10 on Oracle 8.

I have a parameter, allowing multi discrete string values, which are freetype.

These values are work orders, which are stored in the DB as 8 char, zero padded strings (eg. '00000001', NOT '1').

Now, I can't make my users ensure they pad the entries to 8 chars. As such, I have the following code in the Record Selection:

    (
        NumberVar intNumElements := Count({?Work Order});
        NumberVar intCount;

        StringVar Array strWO;

        ReDim strWO[intNumElements];

        For intCount := 1 to intNumElements Step 1 Do
            strWO[intCount] := Right('00000000' + {?Work Order}[intCount], 8);

        {MSF620.WORK_ORDER} = strWO
    )

What I notice though, is that this does NOT then exist in the SQL query CR passes on & ALL of the Work Orders are returned & then filtered.

Does anyone know a way to make the SQL incorporate this selection?

I can't use a Sub Report, seeing as the next section of my reports needs to use Sub Reports itself, and seeing as how they can't be nested, that causes something of an issue.

All thoughts/suggestions gratefully accepted & tested.

 

[lbass]

Instead of converting the parmeter to match the field, maybe you could convert the field to match the parameter using a SQL expression {%wo}:

abs({MSF620.WORK_ORDER})

Then in your record selection formula use:

{%wo} = {?work order}

The abs() converts the string to a number. This will pass to the SQL.

The other approach you could try is to use an edit mask when setting up the parameter, using: 00000000

This will force the user to enter 8 digits. You could instruct them to enter zeros preceding the non-zero digits.

-LB

 

[pandpp]

Thanks LB.

I thought about converting the DB.Field as you suggest only, then what if the user ACTUALLY tries to put the zeros in to help out.

The edit mask approach also crossed my mind, but, as I understand it, they do not want to HAVE to put them in, if they don't feel like it...
The other problem with the edit mask approach is that it is possible for the WO to NOT be numeric, hence not require the leading zeros at all.

Thanks for your ideas.

At this point, I think I'll have to stick with this approach, seeing as how I am JUST bring back the WO number in the main report & shooting out to a number of sub reports for the deeper stuff, to reduce the network traffic.