A basic problem: I am operating on a SQL query. Not a trivial question. I am attempting numerous strategies. I believe I might have found a resolution that operates. That produces the suitable resolution. How can I be absolutely sure? How can I attempt to additional refine the query even though keeping its integrity? I am still in a development period in which I make swift changes and want to get speedy suggestions. The SQL is not yet embedded in an application – that may take place afterwards. For now, I am just laboring away on the question.
Offered a test information established that I have set up in my database tables, I know what the query ought to create. So I can take a look at the query by evaluating that predicted final result with the actual consequence of my SQL statement. A quick – tool-free – way of executing that could be:
- produce a perspective from my SQL question, for example known as my_query
- make a check-question that compares the outcome from this look at with the inline outlined query result
That 2nd question could look like this:
with outcome_to_confirm as
( pick *
, predicted_consequence as
( choose ‘X’, ‘Y’, 42 from twin
select ‘A’, ‘B’, 3.141592 from twin
pick out *
from final result_to_confirm
pick out *
This query need to return zero rows.
Applied to SQL obstacle in this report SQL–Only Counting Documents Sufficiently Spaced aside making use of Analytics with Windowing Clause and Anti Sign up for – in which I established a answer that turned out to be incorrect for a absence of testing! – this performs out as follows:
Create Perspective for the Question that I believe that is the proper alternative:
Examination the final result of executing the query vs the anticipated final result. If the final result of this query is NO Data Discovered then this check has handed.
A next take a look at situation
All exam circumstances can be saved in a uncomplicated SQL Developer worksheet, a script file or even a PL/SQL purpose that returns an Alright or NOK.
Attempt out these statements in Live SQL.
Note: if I had established such a uncomplicated screening harness right before commencing out with making my query, I would not have sent my incorrect remedy to my colleague and to the planet. Of program the quality of the examination established – check knowledge and exam circumstances – is very important for the top quality of my checks.