Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012
    Location
    Greenville, NC
    Posts
    3

    Question Unanswered: Using COALESCE and retrieving data

    I'm preparing a report that will display provider number, provider name, and a single field that will show all the counties that specific provider serves. I realize from researching the coalesce posts that this can be done. However, when I try to retrieve data in the same select statement as my coalesce, I get the error: "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

    The listing of the counties must be specific to the provider, so my original code was:

    DECLARE @Counties varchar(1000)
    SET @Counties = ''

    SELECT a.PROV_ID, a.PROV_NAME,
    @Counties=coalesce(@Counties,'') + b.COUNTY + ','
    from ECBH.dbo.tbl_PROVIDERS a inner join ECBH.dbo.tbl_Provider_Serv_Regions c
    on a.PROV_ID = c.PROV_ID
    inner join ECBH.dbo.tbl_Regions b
    on b.REGION_ID = c.REGION_ID
    where c.PROV_ID = a.PROV_ID
    and a.MASTER = 1

    I thought about creating a table to hold the coalesced values (need to coalesce two other fields as well), but wouldn't an insert to a table fail for the same reason?

    The counties table does not relate to the provider table, but does relate to a provider_county table (which in turn relates to the provider table).

    Any suggestions? Is there a better way to do this?

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    SELECT 
        a.PROV_ID, 
        a.PROV_NAME,
        SUBSTRING( (SELECT ',' + b.COUNTY
                    from ECBH.dbo.tbl_Provider_Serv_Regions c
                    inner join ECBH.dbo.tbl_Regions b on b.REGION_ID = c.REGION_ID
                    where c.PROV_ID = a.PROV_ID
                    FOR XML PATH('') ), 2, 200000) AS Counties
    from ECBH.dbo.tbl_PROVIDERS a 
    where a.MASTER = 1
    Hope this helps.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •