What is the reaction you expect here?
- truncate?
- throw an exception?

Do you want to precalculate length at runtime
- for all of the results
- per row
- per column
- only valid for the caller(i.e. each call has different length limitiations depending on context) ?

you sure could do some nasty things at runtime

You could pass an extra parameter that has a list of length elements for each column like
Query ClientList(pLengthList as %List = { $lb(10,10,50) })

SELECT SUBSTRING(f1,1,$listget(pLengthList,1,50)) as f1,
...

A simple sample could look like this

/// dynamic length restriction
Class User.DynaPar
{

ClassMethod RunTest(pMax As %Integer = 5)
{
#dim tSQL as %String
#dim tIdx as %Integer
#dim tRS as %SQL.StatementResult
set tSQL = "SELECT * FROM myQuery()"
for tIdx = 1 : 1 : 10
{
set tRS = ##class(%SQL.Statement).%ExecDirect(,tSQL)
if tRS.%Next()
{
write !,$j(tIdx,3),": "_tRS.%GetData(1)
}
}
}

Query myQuery(pListOfLengths As %List = {..GetLengths()}) As %SQLQuery [ SqlName = MyQuery, SqlProc ]
{
SELECT TOP 1 SUBSTRING('abcdef',1,$LISTGET(:pListOfLengths,1,1))
}

ClassMethod GetLengths() As %List
{
return $lb(1+$random(6))
}

}

Does that do what you want?

I thought this might do the trick but I am not 100% sure...

SELECT *
FROM PET.ImageStudy_Injection isi
INNER JOIN PET.RadioTracer rt
ON rt.ID = isi.Injection_RadioTracer
INNER JOIN PET.ImageFile if
ON if.Study = isi.ImageStudy
WHERE rt.TracerName = 'roadrunner'

Try

select * from A 
  left join 
    ( B inner join C on B.y = C.y )
  on A.x = B.x

That should give you only the Bs that have C match on y

2017.2 requires Xenial (16.04) and up which come with kernel 4.4 and up.

Your Ubuntu versions (precise and trusty) are older and the kernel versions are 3.* instead of 4.*

Conclusion is that you might be better off trying to install on a supported version of Ubuntu. 

HTH

Gerd

I fear you will have to give us more information before a helpful advice can be given.

• what is the table definition?
• how do the statistics (selectivities) look like?

And last not least

• which SQL do you want to improve and how does the query plan look like?

I also would like to understand the dummy number - what does that mean?

If you want to use %VID for pagination of large sets you might want to consider working with keysets in a snapshot rather than doing pseudo-windowing with %VID. This can become quite costly if you are enforcing an ordered output,

e.g.

SELECT *
  FROM
  (SELECT ID,Name
     FROM Sample.Person
    WHERE  Name LIKE ? )
WHERE %VID BETWEEN ? AND ?
ORDER BY Name

if your dataset is very large  this can be quite expensive if you plan to step through this pagewise.