Converting SQL to English

[RunTime91]

Greetings...



I have been tasked with cleaning up a database eliminating linked tables, moving what I can into stored procedures etc.

One of the things taking the most time is encountering nested queries and being unable to translate

the query into English so I can understand better what is is doing, and just as important, in what sequence it is doing it??

Below is an example of one such query. The code below is actually a Query called: Qry_UpdSales

UPDATE Qry_DailySales RIGHT JOIN Tbl_DailySalesTemp 
ON Qry_DailySales.date = Tbl_DailySalesTemp.Date 
SET Tbl_DailySalesTemp.RefrlTtl = Qry_DailySales.RefrlTtl, Tbl_DailySalesTemp.Refrl2days = Qry_DailySales.Refrl2days
Tbl_DailySalesTemp.Refrl3days = Qry_DailySales.Refrl3days;

What I am trying to translate with this query is something like: This query begins by... by UPDATING this query... Then its does this... Then it does that...

What it looks like is a Query that updates itself while grabbing the dates from Tbl_Daily then SETs the values in Tbl_Daily Sales with the values of the nested query or something... Honestly, I'm just lost with this

Thank you so much for any help...

[isladogs]

I don't know of any utility that can do what you want.

It won't help in this case as the SQL comes from a query ...
... BUT if you have lengthy SQL code equivalent to an action 'query' it often helps to paste this into a query window then change to design view.

To assist with that, have a look at my SQL to VBA & back again utility - see post 5 in this thread for the latest version:
https://www.access-programmers.co.uk...d.php?t=293372

[CJ_London]

the sql language is fairly simple in that it is a small language set, however developers can make things look confusing with long/ambiguous/unclear table and field names which perhaps they understand, but others don't.

There are basically two types of queries - select queries and action queries.

Select queries does what it says, selects data. Action queries are ones the do something with data - append, update, delete

The order of each section is the same for each type of query. e.g. a select query would be. Sections in green are required

SELECT (these fields)
FROM (these tables)
WHERE (this criteria is met)
GROUP BY - these fields
HAVING - grouped values meeting this criteria)
ORDER BY (these fields)

An update query is

UPDATE (these tables)
ON (this linking)
SET (these fields to these values)
WHERE (this criteria is met)

these is some shorthand such as

SELECT *

which means select all fields

there are special joins (INNER/LEFT/RIGHT)
there are other predicates (SELECT TOP for example)
there are functions that are used such as Left, Right to manipulate field values


The only way is to learn the language - but my point is there is not a lot to learn, so stick with it. You may find some reference sites helpful, this is one https://www.w3schools.com/sql/default.asp, but just google/bing 'sql tutorial' or similar to find plenty more

[Micron]

Another item that might help in understanding. A query is based on one or more tables. In your posted case, a table is joined to what I suspect is a select QUERY, which is a "table" of records that must be predetermined before anything can be equated to any table that is part of your main query. Since I can't see the sql for that query, I have to guess that it is a SELECT query - makes the most sense based on the rest of it. Several queries could be "used" as tables - you have just one. In fact, a query can be based only on other queries and not use any table in the db.

A Right join or Left join is an outer join. When Right, all values in the joined field on the right side of the "equation" (the right or 2nd table involved) are included, even if there is no match on the other table. If Left, it is the opposite. Inner means that both sides of the link have to contain the same data or else the non equal records are ignored. Joined fields must contain the same data type, so if you know one side is a number, the other side has to be a number type as well.

So, the order of execution as you inquired, is (more or less) queries that are part of a query (they are nested, not sub-queries) open first. Then the joins are evaluated and the records returned or acted upon as the case may be.

The UPDATE syntax you see is for when the values from a table (or query, right?) provide the values that will be used to update the other table/query. There is another syntax for when the values come from somewhere else, like a form, so don't let that throw you if you see it. This presumes that a query involved can be updated - not all can. I should stop now - getting too deep?