You could use CSV() like this... (sorry for the weird formatting)

Your output then looks like this

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'

You might want to restructure your multiple conditions per $case in a somewhat unusual fashion like this

Class User.FunWithCASE
{
ClassMethod CaseDemo(pNum as %Integer, pBoole as %Boolean = 0, pStr as %String) as %String
{
    return $case(1,  /* whatever is true first */
                 (pNum>100)   :"num gt than 100",
                 ((pBoole=1) && (pStr["Green Bay Packers")) : "True Superbowl Champion found!!!!",
                 ((pNum<=2) || ($length(pStr)>40))          : "num lt 2 or a looong string",
                                                            : ..NestedCase(pNum,pBoole,pStr))    
}

ClassMethod NestedCase(pNum as %Integer, pBoole as %Boolean = 0, pStr as %String) as %String
{
        return $case(1,  /* whatever is true first */
                 (pBoole=0)               : "not true ",
                 (pStr["Miami Dolphins")  : "Whatever",
                 ($e(pStr,*-4,*)="kees")  : "Mon or Yan found",
                                          : "Nothing rhymed in here")_" (nested)"
}

ClassMethod RunTest()
{
    for tNum=1,2,10,101
    {
        for tBoole = 0,1
        {
            set tList= $lb( "Green Bay Packers",
                            "MiamiDolphins",
                            "New York Yankees",
                            "Maxwell's Silver Hammer",
                             $tr($j(" ",41)," ","*")
                           )
            set tPos=0,tStr=""
            while ($listnext(tList,tPos,tStr))                 
            {
                write !,"CaseDemo("_tNum_","_$case(tBoole,1:"true",:"false")_","_$$$quote(tStr)_")"
                       ,?40,..CaseDemo(tNum,tBoole,tStr)
            } 
        }
    }
}
}

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 would probably go for some extra hashing safety and add a CRC or something similar to the hash. There may be more subtle hashing methods or combinations thereof that will make duplicates "almost impossible" ;)

I don't think that checking for duplicates can be solved other than have a reliable index per hash.

Note you can have more subtle hashing (hash sub-pieces, etc.) but you cannot have a reliable hasDuplicate property at runtime that goes beyond the current snapshot of data during INSERT.  IOW, it does not mean much in a system with lots of concurrent access.

In the end if will be 
 

SELECT COUNT(*) AS CountDuplicates
  FROM Test.Duplicates
 GROUP BY myHash
  HAVING COUNT(*) > 1

If you want to avoid duplicates you can make the index unique.

Tell your trigger that it shall be pulled on SQL + OO events like this

Trigger OnUpdate [ Event = UPDATE,Foreach = row/object ]
{

[...]

}

That should to the trick,  by default it is SQL only.

HTH

Gerd

One of the really cool features of SQuirreL is the ability to have multiple different drivers of the same vendor. E.g. if you need to work with drivers from 2010.* to 2016.* you can have different drivers for each connection. 

In Java, it is a little bit of a challenge to use different versions of the same JDBC driver simultaneously, SQuirreL does that job very conveniently.

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.