Constructing A string with Repeated Words or Phrases

[ATE-EGNE]

I would like to create a vi that fills out a long line of text with repeated variables. Something like 

select t.[B], t.[C], t.[D]
from [A] t
inner join (
    select [B], max([C]) as Max[C]
    from [A]
    group by [B]
) tm on t.[B] = tm.[B] and t.[C] = tm.[C]

Although I could wire the string variables to a format into string block, is there a better way that would be:

1. more easy to read
2. less prone to error
3. acknowledge that the inputs are just 4 inputs repeated 13 times

[thoult · Accepted solution]

On the subject of #3, have you considered numbering your inputs?

From Format Into String's help:

By default, this function uses the order of the inputs to populate the format specifiers, or percent codes in the Format String. However, you can use a number followed by a dollar sign ($) within a percent code to specify exactly which input to use for that percent code. For example, the percent code %3$d uses the third input regardless of how many percent codes appear before %3$d in the format string.

For example...

[PiDi]

Something like that:

[ATE-EGNE]

---

@PiDi wrote:

Something like that:

---

Yep! That will work fine, although I imagine it's significantly more resource intensive than concatenating the string. 

[ATE-EGNE]

---

@thoult wrote:

On the subject of #3, have you considered numbering your inputs?

From Format Into String's help:

By default, this function uses the order of the inputs to populate the format specifiers, or percent codes in the Format String. However, you can use a number followed by a dollar sign ($) within a percent code to specify exactly which input to use for that percent code. For example, the percent code %3$d uses the third input regardless of how many percent codes appear before %3$d in the format string.

For example...

---

Yes! This is exactly what I would be looking for!

[PiDi]

---

ATE-EGNE wrote:

Yep! That will work fine, although I imagine it's significantly more resource intensive than concatenating the string. 

---

Hmm... A quick benchmark (attached) shows that search&replace version works about 1/3 faster than format into string version.

[thoult]

Sounds about right, and I bet it scales better too as you increase the number of inputs.

If I knew the incoming data were always in the correct data format, and I were building a set of static SQL queries, I'd still pick Format Into String as it aids in enforcing datatypes for each input. Incidentally, this is currently what I do for production software rather than using stored procedures, as I can version control and validate any queries through application versions, and there's no obviously sensible method for managing SP versions.

If I were making a generic query tool, I'd go with the Search and Replace approach, with the data conversion external to it.

[ATE-EGNE]

---

@thoult wrote:

Sounds about right, and I bet it scales better too as you increase the number of inputs.

If I knew the incoming data were always in the correct data format, and I were building a set of static SQL queries, I'd still pick Format Into String as it aids in enforcing datatypes for each input. Incidentally, this is currently what I do for production software rather than using stored procedures, as I can version control and validate any queries through application versions, and there's no obviously sensible method for managing SP versions.

If I were making a generic query tool, I'd go with the Search and Replace approach, with the data conversion external to it.

---

I'm making a set of predefined queries to use in my .vi's that display data from a SQL server. Interesting that you mentioned stored procedures as I had thought about abandoning this in favor of using them. From what I understand, there's a significant improvement in efficiency when using stored procedures, is this not the case?

[Mark_Yedinak]

I would use stored procedures. I just add them to source code control and they are fully versioned. They are much more flexible than embedding the SQL since you can change the layout of the DB without impacting your application. Think of them as a documented API to the DB. In addition, they are easier to have people use who are not very familiar with SQL. In addition, you can often off load quite a bit of processing to the DB server which is optimized for various things like joins and sorting. You can put quite a bit of complexity in the stored procedures and hide that from the applications who simply want to get/set data.

[thoult]

It's a horses for courses thing.

Yes, I whole-heartedly agree that leveraging the database server for processing rather than query-filter-query is ideal. And yes, you can version control your stored procedures...but you can't immediately state which version of the stored procedure is actually deployed on the system for any given application version.

For lots of SQL interactions, this isn't a problem. Many of our internal data viewing and reporting tools call standard procedures for statistical calculations. We also have scheduled tasks which run entirely on the server to populate tables and views.

For software which requires rigorous validation - e.g. production software for saleable products, especially ones with regulatory constraints such as Copy Exactly! and Functional Safety - my preference will almost always be to embed SQL queries in the application, to ensure that those versions are strictly controlled and part of the validation process. That doesn't preclude me from using more advanced queries techniques, mind!

I'm sure you've encountered similar issues in your role - I'd be genuinely interested to hear how you and others approach issues like this, maybe on Breakpoint.