I am trying to write a stored procedure to add a County column to a report.

I am trying to write a stored procedure to add a county field in my reporting services project.  I have attached my sp I am working on, but I am getting an error that IMS.County can't be bound.

Please review my sp and let me know what I am missing?

I am working out of one database called db.IMS.  IMS has the County, Inmate, and Incarceration tables inside this database.  I am trying to pull County from the County table so it will show in my report.

I am creating a temporary table called FinalData.  Please review my sp and the table descriptions and tell me what I am doing wrong and what I need to change to get the county to pull?
IMS.png
Incarceration.png
Inmate.png
County.png
My-SP.doc
jjc9809Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

redeux-techCommented:
The CountyID in the County table is a tinyint whereas the CommitCountyID in the Incarceration table is a varchar.  You need to cast the as a varchar(2) so that you can join those tables.

Join ims..County Cty On cast(cty.CountyID) as varchar(2))= inc.CommitCountyID
0
jjc9809Author Commented:
I added the above in the revised sp below:

USE [IMS]
GO
/****** Object:  StoredProcedure [dbo].[pRptSexOffenderListForResearchWithCommitCounty]    Script Date: 10/07/2013 10:52:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
    STORED PROCEDURE: List of all sex offenders for Research,

              XXXXXXXXXXXXXXXX
          CREATED ON: XXXXXXXXXXXXXX

             USED BY: Sex Offender List For Research

MODIFICATION HISTORY: 

*/

ALTER PROCEDURE [dbo].[pRptSexOffenderListForResearchWithCommitCounty]

AS

Set NoCount On

Create Table #FinalData (Ais            VarChar(8),
                         Suf            Char(1),
                         InmateName     VarChar(30),
                         Race           Char(1),
                         Sex            Char(1),
                         ReleaseDate    VarChar(10),
                         Institution    VarChar(35),
                         SexOffenseFlag VarChar(1),
                         Offense        VarChar(40),
                         County         Varchar(12)) 

Insert Into #FinalData
Select  inc.Ais,
        inc.Suf,
        inm.InmateName,
        inm.RaceID,
        inm.Sex,
        ims.County,
        Case
            When inc.Ais Like '%Z%'
            Then 'Death Row'

            When inc.MinReleaseDate Is Null
            Then Case
                     When inc.HasLifeSentence = 1 And inc.IsEligibleForParole = 1
                     Then 'LIFE'

                     When inc.HasLifeSentence = 1 And inc.IsEligibleForParole = 0
                     Then 'LWOP'

                     Else 'UNK'
                 End

            Else Convert (varchar(10), inc.MinReleaseDate, 101)
        End,
        ins.Institution,
        Case
            When inc.StateOffenseID In 
                ('028F','047A','048B','059F','060F','071A','072C','073F','074A',
                 '075C','076M','077C','078M','079M','080F','081C','082F','083F',
                 '084F','085F','086F','087F','088A','575C','641B','642C','643C',
                 '644F','644M','661C','662M','663M','664C','665C','666B','667A',
                 '668A','669B','670B','705C','706F','A071','A072','A74B')
            Then '*'
            Else ' '
        End,
        so.StateOffense
  From  ims..Incarceration inc
        Join ims..Inmate inm On inm.Ais = inc.Ais
        Join ims..StateOffense so On so.StateOffenseID = inc.StateOffenseID
        Join ims..Institution ins On ins.InstID = inc.InstID
        Join ims..County Cty On cast(cty.CountyID) As varchar(2))= inc.CommitCountyID 
 Where  inc.InstID > 0
   And (inc.Suf In ('S', 'T', 'U')
    Or  Exists
       (Select *
          From ims..Sentence sent
         Where sent.Ais = inc.Ais
           And sent.StateOffenseID In
              ('028F','047A','048B','059F','060F','071A','072C','073F','074A',
               '075C','076M','077C','078M','079M','080F','081C','082F','083F',
               '084F','085F','086F','087F','088A','575C','641B','642C','643C',
               '644F','644M','661C','662M','663M','664C','665C','666B','667A',
               '668A','669B','670B','705C','706F','A071','A072','A74B')))

Select *
  From #FinalData
Order By Offense, AIS

Drop Table #FinalData

Return

Open in new window

I am getting another error.  See Error2 in attachments.
Error2.png
0
redeux-techCommented:
Try this:

Join ims..County Cty On CAST(cty.CountyID as varchar(2)) = inc.CommitCountyID
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

jjc9809Author Commented:
redeux,

I tried this to test the pull.  The county comes in
select *
From  ims..Incarceration inc
        Join ims..Inmate inm On inm.Ais = inc.Ais
        Join ims..StateOffense so On so.StateOffenseID = inc.StateOffenseID
        Join ims..Institution ins On ins.InstID = inc.InstID
        Join ims..County Cty On cast(cty.CountyID As varchar(2))= inc.CommitCountyID



Would you look at the sp and let me know if I have indicated the setup of the tenporary table #Final data correctly?  The select and insert into fields may not be correct.  When I run the query it says it can't bound ims.county?  Why?


Create Table #FinalData (Ais            VarChar(8),
                         Suf            Char(1),
                         InmateName     VarChar(30),
                         Race           Char(1),
                         Sex            Char(1),
                         ReleaseDate    VarChar(10),
                         Institution    VarChar(35),
                         SexOffenseFlag VarChar(1),
                         Offense        VarChar(40),
                         County         Varchar(12))     (added this)

Insert Into #FinalData
Select  inc.Ais,
        inc.Suf,
        inm.InmateName,
        inm.RaceID,
        inm.Sex,
        ims.County,                          (added this)
        Case
            When inc.Ais Like '%Z%'
            Then 'Death Row'

            When inc.MinReleaseDate Is Null
            Then Case
                     When inc.HasLifeSentence = 1 And inc.IsEligibleForParole = 1
                     Then 'LIFE'

                     When inc.HasLifeSentence = 1 And inc.IsEligibleForParole = 0
                     Then 'LWOP'

                     Else 'UNK'
                 End

            Else Convert (varchar(10), inc.MinReleaseDate, 101)
        End,
        ins.Institution,
        Case
            When inc.StateOffenseID In 
                ('028F','047A','048B','059F','060F','071A','072C','073F','074A',
                 '075C','076M','077C','078M','079M','080F','081C','082F','083F',
                 '084F','085F','086F','087F','088A','575C','641B','642C','643C',
                 '644F','644M','661C','662M','663M','664C','665C','666B','667A',
                 '668A','669B','670B','705C','706F','A071','A072','A74B')
            Then '*'
            Else ' '
        End,
        so.StateOffense
  From  ims..Incarceration inc
        Join ims..Inmate inm On inm.Ais = inc.Ais
        Join ims..StateOffense so On so.StateOffenseID = inc.StateOffenseID
        Join ims..Institution ins On ins.InstID = inc.InstID
        Join ims..County Cty On cast(cty.CountyID As varchar(2))= inc.CommitCountyID
 Where  inc.InstID > 0
   And (inc.Suf In ('S', 'T', 'U')
    Or  Exists
       (Select *
          From ims..Sentence sent
         Where sent.Ais = inc.Ais
           And sent.StateOffenseID In
              ('028F','047A','048B','059F','060F','071A','072C','073F','074A',
               '075C','076M','077C','078M','079M','080F','081C','082F','083F',
               '084F','085F','086F','087F','088A','575C','641B','642C','643C',
               '644F','644M','661C','662M','663M','664C','665C','666B','667A',
               '668A','669B','670B','705C','706F','A071','A072','A74B')))

Select *
  From #FinalData
Order By Offense, AIS

Drop Table #FinalData

Return

Open in new window

0
redeux-techCommented:
Remember you aliased that column......

It should be cty.CountyID
0
jjc9809Author Commented:
redeux.te


I am learning Tsql with the inner joins and outer joins.  Since I have aliased that column, I can't use IMS.County, I have to use cty.CountyID instead.  This is what you mean?

Do I need to put CountyID    Tinyint     in this part below?

                         Suf            Char(1),
                         InmateName     VarChar(30),
                         Race           Char(1),
                         Sex            Char(1),
                         ReleaseDate    VarChar(10),
                         Institution    VarChar(35),
                         SexOffenseFlag VarChar(1),
                         Offense        VarChar(40),
                         County         Varchar(12))
0
redeux-techCommented:
Use, cty.CountyID and then for the temp table, County should be varchar(2).
0
jjc9809Author Commented:
redeux.tech,

I will try the coding at work tomorrow.
0
jjc9809Author Commented:
redeus.tech,

I made the changes you suggested, but I am getting no data and another error which I have attached.  I also attached the revsied SP with changes I made.  

Please review.

Thanks
SP--With-Your-Suggested-changes.doc
New-Error.png
0
redeux-techCommented:
You also have to cast the countyID in your insert into statement....

Insert Into #FinalData
Select  inc.Ais,
        inc.Suf,
        inm.InmateName,
        inm.RaceID,
        inm.Sex,
        Cast(cty.CountyID as varchar(2)) as CountyID,
            Case
            When inc.Ais Like '%Z%'
            Then 'Death Row'.......

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jjc9809Author Commented:
Redeux,

You are correct.  I discovered that I had to place the cty.county in the correct order of the case statement.

Please review this coding which now works too.

Create Table #FinalData (Ais            VarChar(8),
                         Suf            Char(1),
                         InmateName     VarChar(30),
                         Race           Char(1),
                         Sex            Char(1),
                         ReleaseDate    VarChar(10),
                         Institution    VarChar(35),
                         SexOffenseFlag VarChar(1),
                         Offense        VarChar(40),
                County         Varchar(12))   County must be defined as Varchar(12)                  

Insert Into #FinalData
Select  inc.Ais,
        inc.Suf,
        inm.InmateName,
        inm.RaceID,
        inm.Sex,                                            
            Case
            When inc.Ais Like '%Z%'
            Then 'Death Row'

            When inc.MinReleaseDate Is Null
            Then Case
                     When inc.HasLifeSentence = 1 And inc.IsEligibleForParole = 1
                     Then 'LIFE'

                     When inc.HasLifeSentence = 1 And inc.IsEligibleForParole = 0
                     Then 'LWOP'

                     Else 'UNK'
                 End

            Else Convert (varchar(10), inc.MinReleaseDate, 101)
        End,
        ins.Institution,
        Case
            When inc.StateOffenseID In
                ('028F','047A','048B','059F','060F','071A','072C','073F','074A',
                 '075C','076M','077C','078M','079M','080F','081C','082F','083F',
                 '084F','085F','086F','087F','088A','575C','641B','642C','643C',
                 '644F','644M','661C','662M','663M','664C','665C','666B','667A',
                 '668A','669B','670B','705C','706F','A071','A072','A74B')
            Then '*'
            Else ' '
        End,
        so.StateOffense,
        cty.county                                You have to add cty.County here        
  From  ims..Incarceration inc
        Join ims..Inmate inm On inm.Ais = inc.Ais
        Join ims..StateOffense so On so.StateOffenseID = inc.StateOffenseID
        Join ims..Institution ins On ins.InstID = inc.InstID
        Join ims..County Cty On cast(cty.CountyID As varchar(2))= inc.CommitCountyID                

 Where  inc.InstID > 0
   And (inc.Suf In ('S', 'T', 'U')
    Or  Exists
       (Select *
          From ims..Sentence sent
         Where sent.Ais = inc.Ais
           And sent.StateOffenseID In
              ('028F','047A','048B','059F','060F','071A','072C','073F','074A',
               '075C','076M','077C','078M','079M','080F','081C','082F','083F',
               '084F','085F','086F','087F','088A','575C','641B','642C','643C',
               '644F','644M','661C','662M','663M','664C','665C','666B','667A',
               '668A','669B','670B','705C','706F','A071','A072','A74B')))

Select *
  From #FinalData
Order By Offense, AIS

Drop Table #FinalData

Return
0
jjc9809Author Commented:
Redeux,

Thanks very much for your help with this.  I am learning TSql.  I am weak in this area, but maybe overtime I can get better with it.  I am an old mainframe programmer trying to learn the new stuff which really isn't new anymore.  HTML5 and CSS3 is also a weakness.
0
redeux-techCommented:
Yes! You'll get it.  Just get some books and keep going at it.  I still have to go back to my books sometimes and I have been doing this for awhile. :-)

Cheers!
Nikki
Redeux Technology
0
jjc9809Author Commented:
redeux-tech,

I hope you see some more of my posts.  I will probably need some more help along.  Look for me out there.  

jjc9809
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.