Hi All,
I am attempting to complete a Stored Procedure to produce reports utilizing Crystal Reports. I am stumbling on a join where I need to base the join on results/values in the select section. I have tried declaring and set/select @variable(s), however, this returns an error that it cannot be used in a select statement that returns data. When I attempt to use the tablenames.fieldnames from the primary select statement in the subquery, I receive and error of column prefix does not match with table name used in query. I've also tried helps and searching this forum, though I must not be searching for the correct term(s).
I have two select statements combined with "Union all". The join I am attempting is shown below, where I am trying to find the top 1 row of table4 that matches 1) the value in either table1.R_table6 or table2.R_table6 depending on the value in table1.field1 and 2) with the most current effective date that does not exceed the date of table2.datefield. Once I find the row in table4, I can join table5.
How should I search for this? What is the correct term(s)? Is a reference available I could use to guide me in how to complete this? Is it even possible?
Any assistance greatly appreciated!
Chris
.....
union all
select
table1.field1
table1.field2
table1.field3
table2.field1
table2.field2
table2.field3
table3.field1
table3.field2
table3.field3
table5.field1
table5.field2
group=2
from table1
left outer join table2 on table2.R_table1=table1.rowid
join (
select top 1
table4.rowid
table4.begindate
From table4
where table4.R_table6=case
when table1.field1='1' then table1.R_table6
when table1.field1 is null then table1.R_table6
when table1.field1='2' then table2.R_table6
end
and datediff(d,table3.begindate,table2.datefield)>=0
order by table3.begindate DESC
)
join table5 on table5.R_table4=table4.rowid
and table5.R_table7=table3.R_table7