SQL SELECT with variable repetition field as value

[eoneon123]

I'm not sure why this isn't working, but I have a dynamic SQL SELECT statement where both the number of fields/arguments and field values is dynamic. The problem I'm running into is when one of those values is a button container field with multiple reps. The following only represents the part of the calc that is not working. I've pulled this out and having playing with in the data viewer and I can't seem to figure out what's going wrong. Any help would be much appreciated. 

 

Let([

$SELECT_Items = " SELECT " & GFN (Items::kp_ITEMid ) & 

" FROM " & GTN (Items::kf_Title ) & "WHERE ";

  $Button = GFN (Items::Button_Log) & " = ? ";

  $Status = GTN (Items::Icon_Log) & "::" & GFN (Items::Icon_Log[2])

 

];

 

//This returns values

ExecuteSQL ( $SELECT_Items & $Button; ""; ¶; Items::Icon_Log[2] ))

This returns nothing

ExecuteSQL ( $SELECT_Items & $Button; ""; ¶; $Status ))

[Wim Decorte]

(first off: not a good idea to delcare local variables in your let like this: they will live for the remainder of the script.  Calc variables for the scope of the calc should not have the $ or $$ prefix.  Many developers use the underscore as the prefix for calc variables)

 

The issue is probably in this section:

$Status = GTN (Items::Icon_Log) & "::" & GFN (Items::Icon_Log[2])

 

if you mean that status should be equal to the value of the field you should do something like below.  Note that I broke out the SQL syntax into its own variable (_sql) so that you can more easily see it.

 

Let([

_SELECT_Items = " SELECT " & GFN (Items::kp_ITEMid ) & " FROM " & GTN (Items::kf_Title ) & "WHERE ";

_Button = GFN (Items::Button_Log) & " = ? ";

_Status = evaluate( GTN (Items::Icon_Log) & "::" & GFN (Items::Icon_Log[2])) ;

_sql = _SELECT_Items & _Button

 

];

 

ExecuteSQL ( _sql ; ""; ¶; _Status ))

 

)