cortical

ExecuteSQL Count

Discussion created by cortical on Dec 7, 2015
Latest reply on Aug 29, 2016 by beverly

This returns a count:

 

Let([

vA = $_investigation_id ;

vB = $_gpb_id ;

vC = $_side_opposite ;

 

~query = "

SELECT  COUNT (value_nr_id)

FROM    ValueNR

WHERE   investigation_id = ? AND group_b_id= ?  AND nr_side = ?

"

];

ExecuteSQL ( ~query ; "," ; "¶" ; vA ; vB ; vC   )

)


 

But when the calculation is expanded to use a standard SQL grammar I am using successfully in a quantity of other scripts , it fails to return a count. Without the COUNT in below all works as expected.

I have tried several variations on including the COUNT function, in the RSLT variable for example, suitably quoted... and checked the ~query and ~sqlquery results,   but the below at least returns the same string as what does work in the above Let.


Let([

 

//input

va = $_investigation_id ;

vb = $_gpb_id ;

vc = $_side_opposite ;

 

//result

RSLT = sqlFN ( ValueNR::value_nr_id ) ;

 

// from table

TBL = sqlTN ( ValueNR::value_nr_id ) ;

 

//where field:

fa = sqlFN( ValueNR::investigation_id) ;

fb = sqlFN( ValueNR::group_b_id) ;

fc = sqlFN( ValueNR::nr_side) ;

 

~query = "

SELECT COUNT( ~result )

FROM ~table

WHERE ~fa =? AND ~fb = ? AND ~fc = ?

"

;

~sqlQuery = Substitute( ~query;

["~result" ; RSLT ] ;

["~table" ; TBL ] ;

["~fa" ; fa ] ;

["~fb" ; fb ] ;

["~fc" ; fc ]

)

];

ExecuteSQL ( ~sqlQuery ; "," ; "¶" ; va ; vb ; vc )

)


 

examining the ~query and the ~sqlquery, thinking am I missing some invisible characters that are causing the fail, the text has some spacing differences, so applying a Trim4, leaves some horiziontal tab &#x9 characters , also substituted out to return what appears to be identical strings


Or is it a simple syntax issue?  wrapping the ~result variable in the Count for example.


  trimming out all wjite space characters to examine the query string:

 

~sqlQuery = Trim4(Substitute( ~query;

["~result" ; RSLT ] ;

["~table" ; TBL ] ;

["~fa" ; fa ] ;

["~fb" ; fb ] ;

["~fc" ; fc ]

))

;

A = Trim4(Substitute( ~sqlquery ; [" " ; ""] ; ["¶" ; ""] )) ;

B = Substitute( A; " "; "")

];

ExecuteSQL ( B ; "," ; "¶" ; va ; vb ; vc   )

)

 

returns strings that appear identical

 

SELECTCOUNT(value_nr_id)FROMValueNRWHEREinvestigation_id=?ANDgroup_b_id=?ANDnr_side=?


 

SELECTCOUNT(value_nr_id)FROMValueNRWHEREinvestigation_id=?ANDgroup_b_id=?ANDnr_side=?

Outcomes