I have the following string expression in a PowerShell script:
"select count(*) cnt from ${schema}.${table} where ${col.column_name} is null"
The schema and table resolve to the values of $schema and $table, respectively. However, an empty string is supplied for ${col.column_name}. How can I dot into the member of a variable as part of a string substitution?
How about:
"select count(*) cnt from $schema.$table where $($col.column_name) is null"
I think the problem you're having is mainly syntax related. If you have a variable named $foo, ${foo} references the same variable. So, the ${table} and ${schema} references in your sql string work ok.
The issue is with ${col.column_name}. Your variable (I assume) is called $col, and has a member named column_name. As Robert and Steven both indicate in their answers, to refer to this, you should use $($col.column_name). In general, $(expression) will be replaced with the value of the expression.
The reason for allowing braces in variable names is so that variables can have unusual characters in their names. I would recommend not using the ${} syntax (unless you have a compelling reason), and replacing it with straight $var references for variables and $($var.member) for member references in strings.
One way would be:
"select count(*) cnt from $schema.$table where $($col.column_name) is null"
Another option would be
"select count(*) cnt from {0}.{1} where {2} is null" -f $schema, $table, $col.column_name
