I'm trying to get the fracture frequency based on a fairly normal sample table and a discontinuity table. In the query tool, I can get the correct number of discontinuities (21) if I use the following:
Select Count ([DISCONTINUITY].[Angle]) From [DISCONTINUITY],[SAMPLE] Where [DISCONTINUITY].[Depth] >= [SAMPLE].[Depth] And [DISCONTINUITY].[Depth] < [SAMPLE].[Depth]+[SAMPLE].[Length]/12 And [SAMPLE].[Type]='R' And [DISCONTINUITY].[PointID]=[SAMPLE].[PointID] And [SAMPLE].[Depth]=28.2
Translating this to the log report has been challenging, though.
<<Count(<<DISCONTINUITY.Angle>>,<<DISCONTINUITY.Depth>> >= <<SAMPLE.Depth>>)>><<Join(<<DISCONTINUITY.PointID>>,<<SAMPLE.PointID>>)>>
seems to work the way it should:
but
<<Count(<<DISCONTINUITY.Angle>>,<<DISCONTINUITY.Depth>> >= <<SAMPLE.Depth>> And <<DISCONTINUITY.Depth>> < <<Calc(<<SAMPLE.Depth>>+(<<SAMPLE.Length>>/12))>>)>><<Join(<<DISCONTINUITY.PointID>>,<<SAMPLE.PointID>>)>>
generates a lot of extra entries:
I feel that if only I add the right parentheses, this ought to work. Any ideas?
I also tried:
<<SqlCount(_ Select [DISCONTINUITY].[Angle] _ From [DISCONTINUITY] _ Where [DISCONTINUITY].[Depth] >= [SAMPLE].[Depth] And [DISCONTINUITY].[Depth] < [SAMPLE].[Depth]+[SAMPLE].[Length]/12 _)>>
but that just gives zeroes. I think the log engine is using depth as a key, so it doesn't work.
You just need to take the Query that you have working and make a few changes so that it will work on a Log report.
Here is your Query:
Select Count ([DISCONTINUITY].[Angle]) From [DISCONTINUITY],[SAMPLE] Where [DISCONTINUITY].[Depth] >= [SAMPLE].[Depth] And [DISCONTINUITY].[Depth] < [SAMPLE].[Depth] + [SAMPLE].[Length] / 12 And [DISCONTINUITY].[PointID] = [SAMPLE].[PointID] And [SAMPLE].[PointID] = 'B-1' And [SAMPLE].[Depth] = 28.2 And [SAMPLE].[Type] = 'R'
Here is the equivalent Log report expression:
<<Sql(_ Select Count ([DISCONTINUITY].[Angle]) _ From [DISCONTINUITY] Inner Join [SAMPLE] _ On [DISCONTINUITY].[PointID] = [SAMPLE].[PointID] _ Where [DISCONTINUITY].[Depth] >= <<SAMPLE.Depth>> _ And [DISCONTINUITY].[Depth] < <<SAMPLE.Depth>> + <<SAMPLE.Length>> / 12 _ And [SAMPLE].[PointID] = '<<PointID>>' _ And [SAMPLE].[Depth] = <<Depth>> _ And [SAMPLE].[Type] = 'R' _)>>
You need to use an Inner Join because you are using data from two different tables.
You did that in your Query, with the following line:
From [DISCONTINUITY],[SAMPLE]
but the format is a bit different for the SQL expression:
From [DISCONTINUITY] Inner Join [SAMPLE] _ On [DISCONTINUITY].[PointID] = [SAMPLE].[PointID] _
Also, you should use <<DISCONTINUITY.Depth>> for the Depth Expression in the Text vs Depth entity on your Log report so that you don't get any "zeros" printed where there are no corresponding Discontinuity Angles at the same depths you have data in the Sample table.
I just wanted to say that I keep coming back to look at this post. It's provided the solution to pretty much every problem that I have with gINT reports, not just the one stated above. Thanks, Dave.
You're welcome. Glad to hear that it helped. Writing SQL's can be tricky sometimes, and once you have something that works you can often just make slight changes and use it for other output needs.