Open

Mundane tasks helper tasks

  • 20 June 2019
  • 5 replies
  • 77 views

Userlevel 3
Badge +4
The SF tries to help me in many ways to quickly get on with my work.

One area that still takes a lot of copy/paste or manual typing, is writing SQL. Either for templates of checking stuff on the database interactively. I get repetitively SQL insulted by typing - yet again - the full semantic key in a join of three tables somewhere at the bottom of the data model hierarchy.

Why is that? It is not because the SF does not know which columns I want to join; 97%
+/-3% of the time it is the full key that those tables share. The problem is that I can not tell the SF to write a query skeleton for me without me instructing the SF so via SQL.

What I want to do is:
  1. select four tables
  2. click the 'write join clause to clipboard' button
  3. paste the generated 'select t1.*, t2.*..tn* from t1 join t2 on available keys... tn on available keys' in the SQL editor of choice in the dialect configured for this project.
Of course I also like to have an insert- and update statement in the same manner, with all columns mentioned and defaults filled out (might be as comment).

The tasks could be made available in the data modeller. In that case the join-generator could take in account the references that one highlighted.I may have a draft of half this functionality that works on the system tables of DB2 instead of on the SF.

5 replies

Userlevel 6
Badge +11
If I could vote this multiple times, I would. This is a great idea!
Userlevel 5
Badge +3
You have my vote. Automating repetitive tasks is exactly what we need. I like how you are actually insulted by writing repetitive SQL
But maybe it would be even better to have templated queries, where the SF is in control of part of the query (the joins) en the rest of the query is in the devs hands. This would mean you don't have to update your joins when the key structure changes

And that what Mark says 😉
Userlevel 4
Badge +1
This, to me, feels not really software factory releated, but should be part of your IDE. Perhaps have a look at add-ins like this one: https://www.apexsql.com/sql-tools-complete.aspx
Userlevel 3
Badge +4
This, to me, feels not really software factory releated, but should be part of your IDE. Perhaps have a look at add-ins like this one: https://www.apexsql.com/sql-tools-complete.aspx

Close, but not quite. That one generates (amongst others) the snippets that the SF also (already) generates.

In browsing through their features, I did not see a reference to generating of join clauses based on a (written, for my part) selection of tables.

Are you familiar enough with the (SQLServer-specific) plugin to state whether something like that is available or not?
Userlevel 4
Badge +1

Are you familiar enough with the (SQLServer-specific) plugin to state whether something like that is available or not?


Nope, unfortunately not. But I do feel there is mileage in looking into these tools rather than trying to re-invent the wheel. Companies like Apex and Redgate do exist for a reason and their tools could be useful in helping us writing (SQL) code more quickly.

Reply