Best way to design my sql query


Hello Experts,

This my SQL string:
-----------------------------------------

theSqlString = "SELECT    ReleaseInfo.RelPBPPOffenseCode, ReleaseInfo.ParoleNumber, ReleaseInfo.WangAgentName,   NameAlias.NmAlsID, NameAlias.NmAlsLastName, "
theSqlString = theSqlString & " NameAlias.NmAlsFirstName, NameAlias.NmAlsMidName, sots_paroleAssign.ParoleeAcceptFlag, sots_paroleAssign.providerAdminId, "
theSqlString = theSqlString & " PA_USER.USER_NBR, PA_USER.COMPANYNAME_VC"
theSqlString = theSqlString & " FROM PBPPDSF.DSF22_SOTS.dbo.PA_USER PA_USER INNER JOIN"
theSqlString = theSqlString & " sots_paroleAssign ON PA_USER.USER_NBR = sots_paroleAssign.providerAdminId RIGHT OUTER JOIN"
theSqlString = theSqlString & " ReleaseInfo ReleaseInfo INNER JOIN"
theSqlString = theSqlString & " Offender Offender ON ReleaseInfo.ParoleNumber = Offender.ParoleNumber INNER JOIN"
theSqlString = theSqlString & " NameAlias NameAlias ON ReleaseInfo.ParoleNumber = NameAlias.ParoleNumber ON "
theSqlString = theSqlString & " sots_paroleAssign.paroleNumber = ReleaseInfo.ParoleNumber"
theSqlString = theSqlString & " WHERE     (ReleaseInfo.RelPBPPOffenseCode = '608' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '700' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '702' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '705' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '715' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '716' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '723' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '724' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '725' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '726' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '727' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '728' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '729' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '730' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '731' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '733' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '734' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '735' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '736' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '737' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '738' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '739' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '740' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '741' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '742' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '743' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '744' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '745' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '746' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '747' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '750' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '751' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '770' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '781' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '782' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '931' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '938' OR"
theSqlString = theSqlString & " ReleaseInfo.RelPBPPOffenseCode = '956') AND (NameAlias.NmAlsID = 0) AND (ReleaseInfo.WangAgentName LIKE '%" & Right(trim(AMU_Pos_No),6) & "%')"


I want to remove the hard coded values in the above query(700, 702,705,715 etc...) and store in  DB.

i need help to modify the above query with new design .

Thanks
LVL 8
mani_saiAsked:
Who is Participating?
 
lluthienConnect With a Mentor Commented:
hi,

insert these values into a simple table
fornow, it need only have one column.
like so

mytable
---------
ID

then instead of your whereclause
do this:

where ReleaseInfo.RelPBPPOffenseCode in (select ID from mytable)
0
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
Create the table (I'll refer to it as OffenseCode) as you suggest and then just join to it

SELECT    ReleaseInfo.RelPBPPOffenseCode, ReleaseInfo.ParoleNumber, ReleaseInfo.WangAgentName,   NameAlias.NmAlsID, NameAlias.NmAlsLastName, "
theSqlString = theSqlString & " NameAlias.NmAlsFirstName, NameAlias.NmAlsMidName, sots_paroleAssign.ParoleeAcceptFlag, sots_paroleAssign.providerAdminId, "
theSqlString = theSqlString & " PA_USER.USER_NBR, PA_USER.COMPANYNAME_VC"
theSqlString = theSqlString & " FROM PBPPDSF.DSF22_SOTS.dbo.PA_USER PA_USER INNER JOIN"
theSqlString = theSqlString & " sots_paroleAssign ON PA_USER.USER_NBR = sots_paroleAssign.providerAdminId RIGHT OUTER JOIN"
theSqlString = theSqlString & " ReleaseInfo ReleaseInfo INNER JOIN"
theSqlString = theSqlString & " Offender Offender ON ReleaseInfo.ParoleNumber = Offender.ParoleNumber INNER JOIN"
theSqlString = theSqlString & " NameAlias NameAlias ON ReleaseInfo.ParoleNumber = NameAlias.ParoleNumber ON "
theSqlString = theSqlString & " sots_paroleAssign.paroleNumber = ReleaseInfo.ParoleNumber"
theSqlString = theSqlString & " INNER JOIN OffenseCode ON ReleaseInfo.RelPBPPOffenseCode = OffenseCode.Code"
theSqlString = theSqlString & " WHERE NameAlias.NmAlsID = 0) AND (ReleaseInfo.WangAgentName LIKE '%" & Right(trim(AMU_Pos_No),6) & "%')"
0
 
mani_saiAuthor Commented:
Thanks Experts.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.