Here is my SQL with the hardcoded length of 4 - The last parameter to substring (see entire

SQL at bottom);

In my script, I declare a local variable

int li_len

li_len = 4

and substitute:

select max(convert(integer, rtrim(substring(workorderrequest.user_seq, 4, :li_len))))

...

This script compiles but does not produce the same value as in the db painter when I submit it with the parameters hardcoded.

select max(convert(integer, rtrim(substring(workorderrequest.user_seq, 4, 4))))
into :ll_wor_count
from workorderrequest
where workorderrequest.siteseq = :siteseq and
year(workorderrequest.wo_req_date) = :li_year and
rtrim(substring(workorderrequest.user_seq, 4, 4)) NOT LIKE '%[^0-9]%'
using sqlca;

Paul;

The following PowerScript code executed in a command button's clicked event works for me using PB 2017 R2 and MS SQL Server:

Long ll_count, ll_length = 4

SELECT COUNT(*)
  INTO :ll_count
  FROM dbo.Table t
 WHERE SUBSTRING(t.Name,1,:ll_length) = '????'
 USING SQLCA;

If you still are unable to get this to work, maybe you can show us your code?

-John

I know that Mid is just a PB function that does the same as substring - I am just not seeing how in my script I could do what you are saying. How would I setup my string variable ls_string based on a dynamic length?

Hi Paul;

  It should work OK by substringing the data into a PB "String" variable by using the MID() method. Then pass the PB entire String variable into the inline SQL as one argument.

Regards ... Chris