CODEWITHSUNDEEP
sqlunit-testingsql-view
jchitel
jchitel

Reputation: 3169

Can queries on views be fully tested?

So I have a query:

select
  c.STRING,
  a.STRING,
  b.STRING,
  b.STRING3,
  a.STRING2,
from TABLE_A a,
  VIEW_B b,
  TABLE_C c
where b.STRING2 = a.STRING2
  and a.INT = #value#
  and c.INT = a.INT
  and c.STRING IN ('string1', 'string2')

VIEW_B is a view and TABLE_A and TABLE_C are normal tables. I am trying to unit test this query. The way I saw it was that because it is a select, I need to make some dummy data, insert it into the tables, and then run the above query and make sure it returns the same data I put in. However, VIEW_B is a view, and cannot be inserted into. So I need to get some real data from VIEW_B and its corresponding data in the other 2 tables. However, by that point I'm just running the same query, plus I have to grab an a.INT. So my conclusion is that if I have a query that uses views, it can't be fully tested.

Is this true? Regardless, what is the best way to test this query?

Keep in mind that this is a work project and that I can't change the schema of the tables or the query itself, and I have to run my tests against the actual database.

Upvotes: 0

Views: 55

Answers (1)

James Daily
James Daily

Reputation: 597

Why not just provide sample data for whatever tables make up VIEW_B?

Alternatively, replace "VIEW_B" with an inline entity declaration:

(I used oracle-specific DUAL dummy table, but use whatever works for you)

select
  c.STRING,
  a.STRING,
  b.STRING,
  b.STRING3,
  a.STRING2
from TABLE_A a,
  (select 'blah' string, 123 INT from dual) b,
  TABLE_C c
where b.STRING2 = a.STRING2
  and a.INT = #value#
  and c.INT = a.INT
  and c.STRING IN ('string1', 'string2')

Upvotes: 3

Related Questions