SQL Query storage

[DavidBechtel]

I've developed a Ruby script app that pulls data from a legacy app. In trying to refactor and get the code shorter, I've run into the text of the queries taking up a page and a half. I'm wondering if there is a best practice for storing these strings, possibly in a text file. Thoughts? Remember "legacy" so here is a single query and I have six or seven of them.

    fullQuery = "SELECT Breed, Farm_Name, Grower_Name, 
                        Kill_Date, (DOA_Lbs/Live_Wt*100) as DOA_Pct, 
                        (WBC_Lbs/Live_Wt*100) as WBC_Pct, 
                        (Parts_Lbs/Live_Wt*100) as Parts_Pct, 
                        Age, Avg_Wt, Live_Wt
                   FROM Grower_Data 
                  WHERE Farm_Name = '" + farm + "'
                    AND Kill_Date > '" + fy14Start + "'
                    AND Kill_Date < '" + fy14End + "' 
                    AND Thin IS Null
                    AND ABF IS Null
                    AND Organic IS Null
               ORDER BY Kill_Date"

[billgathen]

I'd suggest creating a queries.rb file, with one method for each query. Each method would accept the args you need to complete your query. Make the whole method return a heredoc with the full text of the query and the interpolated args.

You'd call it with fullQuery(farm, st_dt, end_dt) in your main code, keeping the details of the query separate from the code that handles the result.

If the file gets unwieldy with multiple queries, you might explore putting each query in a file and wrapping it all in a module, then calling it with Queries::fullQuery, etc

Hope that helps!