?
Solved

Best way to design my sql query

Posted on 2005-05-10
3
Medium Priority
?
203 Views
Last Modified: 2010-03-19

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
0
Comment
Question by:mani_sai
3 Comments
 
LVL 11

Accepted Solution

by:
lluthien earned 1200 total points
ID: 13969631
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
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 800 total points
ID: 13970021
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
 
LVL 8

Author Comment

by:mani_sai
ID: 13970620
Thanks Experts.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question