Link to home
Start Free TrialLog in
Avatar of Mani Pazhana
Mani PazhanaFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of lluthien
lluthien

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mani Pazhana

ASKER

Thanks Experts.