Beginner - Help deciphering a SQL view

[skystar70]

Hello all.

I am a beginner working with SQL views. An old colleague of mine created a view that I am using in a crystal report (crystal 9, sql 2000). All of a sudden, since going to a new version of the application we are pulling data from, the report is not giving me all of the info I need.

Therefore, I am trying to decipher this view to see if I can find out if this is the problem. I suspect it is because the field I am looking at in the report is pulling from this view.

Here is the view:

create view ip.vw_ACE_Resource_Role_Rates (resource_code, rate_structure_code, rate_type_code, resource_role,
resroletype, costbilltype, billrate_start, billrate_finish,
billing_rate)
as
select distinct res.resource_code, ISNULL(br.structure_code, 'COST$NULL') , rate_type_code, role.description as resource_role,
'Role', 'BILL', br.billrate_start, br.billrate_finish, billing_rate
from ip.resources Res, ip.criteria cr, ip.billing_rate br, ip.structure role, ip.global_options go
where res.resource_code = cr.resource_code
and cr.structure_name = 'Obs20'
and cr.father_code = br.structure_code
and cr.father_code = role.structure_code
and br.rate_type_code = go.option_value
and go.option_id = '1305'
and cr.place = (SELECT Min(place) from ip.criteria cr2
where cr2.resource_code = cr.resource_code
and cr2.structure_name = cr.structure_name)
and res.resource_code NOT in (Select distinct structure_code
from ip.billing_rate br2, ip.global_options go2
where go2.option_id = '1306'
and br2.rate_type_code = go2.option_value
and br2.structure_code = res.resource_code)

I get the whole: create, as, where syntax of a view, but this view seems to get a bit more complex than that.

Can someone possibly write this view out in english for me?

Weird request, I know. It would be super helpful though!

Thanks in advance!!

 

[kskid]

To see what has changed,

Click on Database -> Verify Database

-lw

 

[skystar70]

Hi lw,

I am not sure I understand your response. When I click on Database>Verify Database, I get message "The database is up to date". Am I supposed to get something more?

Thanks!

 

[beltmanjr]

You got 2 subqueries in the view, the rest is very straight record selection and where statements.

cr.place = (
  SELECT Min(place) from ip.criteria cr2
  where cr2.resource_code = cr.resource_code
  and cr2.structure_name = cr.structure_name)

You can run this on itself to check the output. It simply finds the minimum 'place' from the ip.criteria table. It does this per record found in the main query. So for record 1 in the main query it checks if
resource_code (subquery) = resource_code (main query row 1)
and the same for the structure_name.

It probably has more 'place' entries per resource_code and structure_name, but only wants to find the records in the main query for which it finds the minmum 'place' in the subquery.

So in brief it does (row by row): get row from main query. Find value in subquery. If the 'place' found in the row in main query = the place found in the subquery -> display that row, else dont.

res.resource_code NOT in (
  Select distinct structure_code 
  from ip.billing_rate br2, ip.global_options go2
  where go2.option_id = '1306'
  and br2.rate_type_code = go2.option_value
  and br2.structure_code = res.resource_code)

Does basically the same as the first subquery. This time it tries to find a structure_code. It may even find more than 1. The main query will display rows for every row it finds where the structure_code != (<>) the one(s) found in the subquery.

I hope this helps you a bit....

Verify Database would tell you: Table XYZ has changed if there were any changes to a table/view.

So, in your case it doesn't look like anything changed/