Breaking down SQL's (1 Viewer)

[disgruntled]

I have to write this ludicrussly long SQL statement so was thinking about doing it in steps for easier debugging.
IE. Get a record set as per some criteria then with new criteria search that recordset not the whole table.

i have never seen this before so is it even apossibility and if so what is the syntax??

?? "SELECT * FROM recordSet Where ??????

 

[Rich]

Why write it? use the qbe grid to design and test it

 

[Rog]

As above, using QBE you can do a query to make your first selection, then make a second query to run on the first query. A query doesn't have to be based on a table. It can be based on an existing query.

 

[Fornatian]

I would add(and others will agree), that saved queries ALWAYS execute faster than SQL statements - therefore if your SQL is ludicrously long then you should build it using querie built on queries built on queries etc...

Additionally, the order on which you 'mount' these queries will also effect the speed - put the query that wittles the recordset down to the smallest amount of records first
The subsequent queries will run faster because they have a smaller recordset to interrogate.

In my not so vast experience, SQL code is only better than queries when you are dynamically creating the SQL to user input or creating a temporary recordset that need not be saved.

I'm sure others will have opinions on the above paragraph so I'll wait and watch the replies.

Ian

 

[pcs]

i weigh in with Ian. i have found saved queries to be the fastest. as Ian noted, the order is important. i prefer queries to vba code as well. i generally use vba only to get additional granularity. a query is my 'chain saw' and vba is my 'scalpel'.

the more you use the QBE grid, the more you appreciate its simplicity and power. my background is ISAM, and i often smile after creating a complex nested query, thinking about how many lines of code it would have taken to extract the same recordset.

if you do choose to work with saved queries, it is important to establish a rigorous naming convention and stick to it. also, use the description area for additional explanations. if you have a hundred or more queries, it's nice to have some idea of what they do

.

al