Cannot resolve collation conflict for UNION operation

Hi
I am getting the above error with my SP. Why would I get this error?
Many Thanks
Jaco
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[qfm_RptgetAsbLevelGeog]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[qfm_RptgetAsbLevelGeog]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[qfm_RptgetAsbLevelGeog]
@.EventID varchar(10),
@.Config int
AS
SET NOCOUNT ON
--If we want to add the main event as a row in the sql below (to make it
have a 'from event' equivalent to QFM3),
--then only need to add the @.eventid to the select.
Declare @.parID int
Declare @.GeogID varchar(5)
declare @.sql nvarchar(4000)
set @.sql=' '
if @.Config = 0 or @.Config = 1 or @.Config = 3 or @.Config = 5 or @.Config = 7
or @.Config = 9 or @.Config = 11 or @.Config = 13 or @.Config = 15
--1. find all other details of asb found to the exact same geog (so if found
at site/locn/unit, then exactly this level), including current event.
begin
set @.sql="
select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Found'
as asbestoststaus, es.surveydate, esd.samplenumber,
esd.materialdescription, esda.description + '[' +
ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
ac.description as classification, esd.risk as hazard, esd.remarks,
g.geographyid, g.parentid, g.siteid, g.level, g.region,
ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
(select site + ' ' + location + ' ' + unit + ' ' + partition from geography
where geography.geographyid = es.geographyid) as surveygeog --gja 5.12.04
from geography g, event e, eventsurvey es, eventsurveydetail esd,
asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
where g.geographyid = e.geographyid --gja5.12.04
-- where g.geographyid = es.geographyid --gja5.12.04
and e.eventid = es.currenteventid
and es.eventsurveyid = esd.eventsurveyid
and ac.asbestosclassificationid = esd.classificationid
and esd.analysisid = esda.EventSurveyDetailAnalysisid
and e.geographyid in
(select geographyid from event where eventid = " + rtrim(@.EventID) + ")
-- select geographyid from event where eventid = 38590)
and c.uniqueid =* esd.materialareaunitid "
end
if @.Config = 2 or @.Config = 3 or @.Config = 6 or @.Config = 7 or @.Config = 10
or @.Config = 11 or @.Config = 14 or @.Config = 15
--2. if asb was found at at ie bridge court/2s/test, then include all for
bridge court/2s/anything, but not bridge court/2s/nothing
--ie if found at a room on a floor, report on other rooms on same floor
(with the same parent and level)
--Get all geogs for the same level (ie rooms on the same floor) as the geog
of a specific event.
begin
select @.parID = parentid from geography g, event e where g.geographyid =
e.geographyid and e.eventid = rtrim(@.EventID)
if @.parID is not null
begin
--If we are not at the highest level (site) then we can check for a parent
set @.sql = @.sql + " union
select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Found'
as asbestoststaus, es.surveydate, esd.samplenumber,
esd.materialdescription, esda.description + '[' +
ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
ac.description as classification, esd.risk as hazard, esd.remarks,
g.geographyid, g.parentid, g.siteid, g.level, g.region,
ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
(select site + ' ' + location + ' ' + unit + ' ' + partition from
geography where geography.geographyid = es.geographyid) as surveygeog --gja
5.12.04
from geography g, event e, eventsurvey es, eventsurveydetail esd,
asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
where g.geographyid = e.geographyid --gja5.12.04
-- where g.geographyid = es.geographyid --gja5.12.04
and e.geographyid in
(select g2.geographyid
from geography g2, geography g
where g.geographyid in (select geographyid from event where eventid = " +
rtrim(@.EventID) + ")
and g.parentid = g2.parentid
and g.level = g2.level) and e.eventid = es.currenteventid
and es.eventsurveyid = esd.eventsurveyid
and ac.asbestosclassificationid = esd.classificationid
and esd.analysisid = esda.EventSurveyDetailAnalysisid
and c.uniqueid =* esd.materialareaunitid "
-- print 'first'
-- print 'Config=2'
end
else
begin
set @.sql = @.sql + " union
select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Found'
as asbestoststaus, es.surveydate, esd.samplenumber,
esd.materialdescription, esda.description + '[' +
ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
ac.description as classification, esd.risk as hazard, esd.remarks,
g.geographyid, g.parentid, g.siteid, g.level, g.region,
ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
(select site + ' ' + location + ' ' + unit + ' ' + partition from
geography where geography.geographyid = es.geographyid) as surveygeog --gja
5.12.04
from geography g, event e, eventsurvey es, eventsurveydetail esd,
asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
where g.geographyid = e.geographyid --gja5.12.04
-- where g.geographyid = es.geographyid --gja5.12.04
and e.eventid = es.currenteventid
and es.eventsurveyid = esd.eventsurveyid
and ac.asbestosclassificationid = esd.classificationid
and esd.analysisid = esda.EventSurveyDetailAnalysisid
and g.parentid is null
and g.geographyid = (select geographyid from event where eventid = " +
rtrim(@.EventID) + ")
and c.uniqueid =* esd.materialareaunitid "
end
end
if @.Config = 4 or @.Config = 5 or @.Config = 6 or @.Config = 7 or @.Config = 12
or @.Config = 13 or @.Config = 14 or @.Config = 15
--3. if asb was found at at ie red lion court/3rd/, then include all for red
lion court/3rd/anything below.
--Do we not then include the current event? (as it is obviously not below
the level of geog for the current event)?
--JF says maybe, maybe not, so does.
--Use the eventid to get the geography level
begin
select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
set @.sql = @.sql + " union
select eventid ,
site ,
location ,
unit ,
partition ,
asbestoststaus ,
surveydate datetime,
samplenumber ,
materialdescription ,
analysis ,
classification ,
hazard ,
remarks ,
geographyid ,
parentid ,
siteid ,
level ,
region ,
extent
,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
AsbestosImagePath,
surveygeog --gja5.12.04
from AsbLowestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
--print 'Config=3'
end
if @.Config = 8 or @.Config = 9 or @.Config = 10 or @.Config = 11 or @.Config =
12 or @.Config = 13 or @.Config = 14 or @.Config = 15
--4. as 3 only cant have anything below 2s (if unit or part then dont
include)
--ie if asb was found at at ie bridge court/2s/test, then include all geogs
at bridge court/2s/nothing.
--Do we not then include the current event? (as it is obviously not below
the level of geog for the current event)?
--JF says maybe, maybe not, so does.
begin
select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
set @.sql = @.sql + " union
select eventid ,
site ,
location ,
unit ,
partition ,
asbestoststaus ,
surveydate datetime,
samplenumber ,
materialdescription ,
analysis ,
classification ,
hazard ,
remarks ,
geographyid ,
parentid ,
siteid ,
level ,
region ,
extent
,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
AsbestosImagePath,
surveygeog --gja5.12.04
from AsbHighestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
end
if left(@.sql, 7) = ' union'
set @.sql = substring(@.sql, 8, len(@.sql) - 7)
set @.sql = @.sql + " order by 1"
exec sp_executesql @.sql
--end
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOJaco,
The UNION operator will scan the inputs to remove duplicates (comparing all
members of the select list). There should be columns, in the tables involved
,
with different collation. You can use COLLATE apply a collation cast to the
character expression. See this example:
Example:
use northwind
go
create table t1 (
colA varchar(25) collate SQL_Latin1_General_CP1_CI_AS
)
go
create table t2 (
colA varchar(25) collate SQL_Latin1_General_CP1_CS_AS
)
go
insert into t1 values('microsoft')
insert into t1 values('Microsoft')
go
-- this will fail with the error mentioned
select colA from t1
union
select colA from t2
go
select colA from t1
union
select colA collate SQL_Latin1_General_CP1_CI_AS from t2
go
drop table t1, t2
go
AMB
"Jaco" wrote:

> Hi
> I am getting the above error with my SP. Why would I get this error?
> Many Thanks
> Jaco
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[qfm_RptgetAsbLevelGeog]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[qfm_RptgetAsbLevelGeog]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
>
> CREATE PROCEDURE [dbo].[qfm_RptgetAsbLevelGeog]
> @.EventID varchar(10),
> @.Config int
> AS
>
> SET NOCOUNT ON
> --If we want to add the main event as a row in the sql below (to make it
> have a 'from event' equivalent to QFM3),
> --then only need to add the @.eventid to the select.
> Declare @.parID int
> Declare @.GeogID varchar(5)
> declare @.sql nvarchar(4000)
> set @.sql=' '
> if @.Config = 0 or @.Config = 1 or @.Config = 3 or @.Config = 5 or @.Config = 7
> or @.Config = 9 or @.Config = 11 or @.Config = 13 or @.Config = 15
> --1. find all other details of asb found to the exact same geog (so if fou
nd
> at site/locn/unit, then exactly this level), including current event.
> begin
> set @.sql="
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Found
'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from geograp
hy
> where geography.geographyid = es.geographyid) as surveygeog --gja 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and e.geographyid in
> (select geographyid from event where eventid = " + rtrim(@.EventID) + ")
> -- select geographyid from event where eventid = 38590)
> and c.uniqueid =* esd.materialareaunitid "
>
> end
>
> if @.Config = 2 or @.Config = 3 or @.Config = 6 or @.Config = 7 or @.Config =
10
> or @.Config = 11 or @.Config = 14 or @.Config = 15
> --2. if asb was found at at ie bridge court/2s/test, then include all for
> bridge court/2s/anything, but not bridge court/2s/nothing
> --ie if found at a room on a floor, report on other rooms on same floor
> (with the same parent and level)
> --Get all geogs for the same level (ie rooms on the same floor) as the geo
g
> of a specific event.
> begin
> select @.parID = parentid from geography g, event e where g.geographyid =
> e.geographyid and e.eventid = rtrim(@.EventID)
> if @.parID is not null
> begin
> --If we are not at the highest level (site) then we can check for a paren
t
> set @.sql = @.sql + " union
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Foun
d'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from
> geography where geography.geographyid = es.geographyid) as surveygeog --gj
a
> 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.geographyid in
> (select g2.geographyid
> from geography g2, geography g
> where g.geographyid in (select geographyid from event where eventid = "
+
> rtrim(@.EventID) + ")
> and g.parentid = g2.parentid
> and g.level = g2.level) and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and c.uniqueid =* esd.materialareaunitid "
>
> -- print 'first'
> -- print 'Config=2'
> end
> else
> begin
> set @.sql = @.sql + " union
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Foun
d'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from
> geography where geography.geographyid = es.geographyid) as surveygeog --gj
a
> 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and g.parentid is null
> and g.geographyid = (select geographyid from event where eventid = " +
> rtrim(@.EventID) + ")
> and c.uniqueid =* esd.materialareaunitid "
>
> end
> end
>
> if @.Config = 4 or @.Config = 5 or @.Config = 6 or @.Config = 7 or @.Config =
12
> or @.Config = 13 or @.Config = 14 or @.Config = 15
> --3. if asb was found at at ie red lion court/3rd/, then include all for r
ed
> lion court/3rd/anything below.
> --Do we not then include the current event? (as it is obviously not below
> the level of geog for the current event)?
> --JF says maybe, maybe not, so does.
> --Use the eventid to get the geography level
> begin
> select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
> set @.sql = @.sql + " union
> select eventid ,
> site ,
> location ,
> unit ,
> partition ,
> asbestoststaus ,
> surveydate datetime,
> samplenumber ,
> materialdescription ,
> analysis ,
> classification ,
> hazard ,
> remarks ,
> geographyid ,
> parentid ,
> siteid ,
> level ,
> region ,
> extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
> AsbestosImagePath,
> surveygeog --gja5.12.04
> from AsbLowestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
>
> --print 'Config=3'
> end
>
> if @.Config = 8 or @.Config = 9 or @.Config = 10 or @.Config = 11 or @.Config
=
> 12 or @.Config = 13 or @.Config = 14 or @.Config = 15
> --4. as 3 only cant have anything below 2s (if unit or part then dont
> include)
> --ie if asb was found at at ie bridge court/2s/test, then include all geo
gs
> at bridge court/2s/nothing.
> --Do we not then include the current event? (as it is obviously not below
> the level of geog for the current event)?
> --JF says maybe, maybe not, so does.
> begin
> select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
> set @.sql = @.sql + " union
> select eventid ,
> site ,
> location ,
> unit ,
> partition ,
> asbestoststaus ,
> surveydate datetime,
> samplenumber ,
> materialdescription ,
> analysis ,
> classification ,
> hazard ,
> remarks ,
> geographyid ,
> parentid ,
> siteid ,
> level ,
> region ,
> extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
> AsbestosImagePath,
> surveygeog --gja5.12.04
>
> from AsbHighestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
>
> end
>
> if left(@.sql, 7) = ' union'
> set @.sql = substring(@.sql, 8, len(@.sql) - 7)
> set @.sql = @.sql + " order by 1"
>
> exec sp_executesql @.sql
>
>
> --end
> SET NOCOUNT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||Hi
Without DDL for the tables it is hard to say where the conflict occurs, but
the way to debug it would be to use profile to get the final SQL statement
and then you can find out which part of the union is causing the conflict an
d
use the COLLATE option to force the collation.
John
"Jaco" wrote:

> Hi
> I am getting the above error with my SP. Why would I get this error?
> Many Thanks
> Jaco
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[qfm_RptgetAsbLevelGeog]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[qfm_RptgetAsbLevelGeog]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
>
> CREATE PROCEDURE [dbo].[qfm_RptgetAsbLevelGeog]
> @.EventID varchar(10),
> @.Config int
> AS
>
> SET NOCOUNT ON
> --If we want to add the main event as a row in the sql below (to make it
> have a 'from event' equivalent to QFM3),
> --then only need to add the @.eventid to the select.
> Declare @.parID int
> Declare @.GeogID varchar(5)
> declare @.sql nvarchar(4000)
> set @.sql=' '
> if @.Config = 0 or @.Config = 1 or @.Config = 3 or @.Config = 5 or @.Config = 7
> or @.Config = 9 or @.Config = 11 or @.Config = 13 or @.Config = 15
> --1. find all other details of asb found to the exact same geog (so if fou
nd
> at site/locn/unit, then exactly this level), including current event.
> begin
> set @.sql="
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Found
'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from geograp
hy
> where geography.geographyid = es.geographyid) as surveygeog --gja 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and e.geographyid in
> (select geographyid from event where eventid = " + rtrim(@.EventID) + ")
> -- select geographyid from event where eventid = 38590)
> and c.uniqueid =* esd.materialareaunitid "
>
> end
>
> if @.Config = 2 or @.Config = 3 or @.Config = 6 or @.Config = 7 or @.Config =
10
> or @.Config = 11 or @.Config = 14 or @.Config = 15
> --2. if asb was found at at ie bridge court/2s/test, then include all for
> bridge court/2s/anything, but not bridge court/2s/nothing
> --ie if found at a room on a floor, report on other rooms on same floor
> (with the same parent and level)
> --Get all geogs for the same level (ie rooms on the same floor) as the geo
g
> of a specific event.
> begin
> select @.parID = parentid from geography g, event e where g.geographyid =
> e.geographyid and e.eventid = rtrim(@.EventID)
> if @.parID is not null
> begin
> --If we are not at the highest level (site) then we can check for a paren
t
> set @.sql = @.sql + " union
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Foun
d'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from
> geography where geography.geographyid = es.geographyid) as surveygeog --gj
a
> 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.geographyid in
> (select g2.geographyid
> from geography g2, geography g
> where g.geographyid in (select geographyid from event where eventid = "
+
> rtrim(@.EventID) + ")
> and g.parentid = g2.parentid
> and g.level = g2.level) and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and c.uniqueid =* esd.materialareaunitid "
>
> -- print 'first'
> -- print 'Config=2'
> end
> else
> begin
> set @.sql = @.sql + " union
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Foun
d'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from
> geography where geography.geographyid = es.geographyid) as surveygeog --gj
a
> 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and g.parentid is null
> and g.geographyid = (select geographyid from event where eventid = " +
> rtrim(@.EventID) + ")
> and c.uniqueid =* esd.materialareaunitid "
>
> end
> end
>
> if @.Config = 4 or @.Config = 5 or @.Config = 6 or @.Config = 7 or @.Config =
12
> or @.Config = 13 or @.Config = 14 or @.Config = 15
> --3. if asb was found at at ie red lion court/3rd/, then include all for r
ed
> lion court/3rd/anything below.
> --Do we not then include the current event? (as it is obviously not below
> the level of geog for the current event)?
> --JF says maybe, maybe not, so does.
> --Use the eventid to get the geography level
> begin
> select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
> set @.sql = @.sql + " union
> select eventid ,
> site ,
> location ,
> unit ,
> partition ,
> asbestoststaus ,
> surveydate datetime,
> samplenumber ,
> materialdescription ,
> analysis ,
> classification ,
> hazard ,
> remarks ,
> geographyid ,
> parentid ,
> siteid ,
> level ,
> region ,
> extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
> AsbestosImagePath,
> surveygeog --gja5.12.04
> from AsbLowestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
>
> --print 'Config=3'
> end
>
> if @.Config = 8 or @.Config = 9 or @.Config = 10 or @.Config = 11 or @.Config
=
> 12 or @.Config = 13 or @.Config = 14 or @.Config = 15
> --4. as 3 only cant have anything below 2s (if unit or part then dont
> include)
> --ie if asb was found at at ie bridge court/2s/test, then include all geo
gs
> at bridge court/2s/nothing.
> --Do we not then include the current event? (as it is obviously not below
> the level of geog for the current event)?
> --JF says maybe, maybe not, so does.
> begin
> select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
> set @.sql = @.sql + " union
> select eventid ,
> site ,
> location ,
> unit ,
> partition ,
> asbestoststaus ,
> surveydate datetime,
> samplenumber ,
> materialdescription ,
> analysis ,
> classification ,
> hazard ,
> remarks ,
> geographyid ,
> parentid ,
> siteid ,
> level ,
> region ,
> extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
> AsbestosImagePath,
> surveygeog --gja5.12.04
>
> from AsbHighestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
>
> end
>
> if left(@.sql, 7) = ' union'
> set @.sql = substring(@.sql, 8, len(@.sql) - 7)
> set @.sql = @.sql + " order by 1"
>
> exec sp_executesql @.sql
>
>
> --end
> SET NOCOUNT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>