Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Store Procedure Where Clause

Posted on 2005-05-15
5
Medium Priority
?
226 Views
Last Modified: 2010-03-19
In the following Stored Procedure, I want to retrieve Foreign Customers only.
To do so, I added the statement ON C.State <> S.StateFS.
However, I use a series of If statements that peform a date calculation in which I use the values in the
S table where StateFS = 'DE'.

I believe my SQL statement is flawed because I am getting back a result set that includes customers in the U.S

Do you know how I would rewrite this query ?

I should be selecting customers where C.CustomerNumber = P.CustomerNumber and
C.State is NOT IN S.StatesALL.

In addtion, the If statements, (see example as follows) the S.BondsFS value is retrieved from the S table where S.StatesFS = 'DE'. For all the If clauses I peform a Date calculation which uses the tblStatesAll record where S.StatesFS = 'DE. Should the S.StatesFS = DE be handled in each of the IF statements individually or can it be handled before the If statement ?
--------------------------------------------
If @Prop = 'B'
   BEGIN
     SET @SQL = @SQL + '  AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.BondsFS) AS VARCHAR) + ''-12-31'' AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) '
   END
 
----------------------------------------------------------------------------------------------

Set @SQL = @SQL +
      'SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid],
       C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State,
       C.Zip, C.SSN, Null AS [DDA Number], Null As [Check/Cert Number], C.DateLost AS [Date Lost], Null As [Date Opened], Right(C.OfficeNumber,3) + '' '' + C.CustomerNumber AS [Account Number],
       C.DateOfBirth, P.PropertyType AS [Property Type], Null As [Property Status], P.CUSIP, P.SecurityName AS [Security Name], Null AS [Sub-Issue], P.MarketValue AS [Market Value],
       P.ClosePrice AS [Market Price], P.Quantity AS [Shares], P.CashBalance AS [Dollar Amount], C.DateOfBirth AS [Date Of Birth], P.IraCode, P.PlanNumber
       FROM tblStatesAll
       AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON C.State <> S.StateFS  WHERE S.StateFS = ''DE'' '

If @Prop = 'A'
   BEGIN
     SET @SQL = @SQL + '  AND ((C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-12-31'' AND  P.PropertyType=''OTHER'' AND LEN(P.IraCode) = 0) '  
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.BondsFS) AS VARCHAR) + ''-12-31''  AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) '
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-12-31'' AND P.PropertyType=''MUTUAL FUND'' AND PlanNumber = ''NETWORKED'' AND LEN(P.IraCode) = 0) '
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-12-31'' AND P.PropertyType=''MUTUAL FUND'' AND P.PlanNumber <> ''NETWORKED'' AND LEN(P.IraCode) = 0) '
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.CashFS) AS VARCHAR) + ''-12-31''  AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '  
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.StocksFS) AS VARCHAR) + ''-12-31''  AND P.PropertyType=''EQUITY'' AND LEN(P.IraCode) = 0)) '
   END

If @Prop = 'B'
   BEGIN
     SET @SQL = @SQL + '  AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.BondsFS) AS VARCHAR) + ''-12-31'' AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) '
   END

If @Prop = 'MFA'
   BEGIN
     SET @SQL = @SQL + ' AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-12-31''  AND P.PropertyType=''MUTUAL FUND'' AND P.PlanNumber <> ''NETWORKED'' AND LEN(P.IraCode) = 0) '
   END

If @Prop = 'MFN'
   BEGIN
     SET @SQL = @SQL + ' AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-12-31''  AND P.PropertyType=''MUTUAL FUND'' AND PlanNumber = ''NETWORKED'' AND LEN(P.IraCode) = 0) '
   END

If @Prop = 'O'
   BEGIN
      SET @SQL = @SQL + ' AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-12-31''  AND P.PropertyType=''OTHER'' AND LEN(P.IraCode) = 0) '  
   END

If @Prop = 'E'
   BEGIN
     SET @SQL = @SQL + '  AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.StocksFS) AS VARCHAR) + ''-12-31''  AND P.PropertyType=''EQUITY'' AND LEN(P.IraCode) = 0) '  
   END

If @Agg = 'A' AND (@Prop = 'ALL' OR @Prop = 'C')
   BEGIN
     SET @SQL = @SQL + '  OR (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.CashFS) AS VARCHAR) + ''-12-31'' AND (P.CashBalance>=[S].[AggregFS] OR P.Quantity>=[S].[AggregFS]) AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '      
   END

If @Agg = 'B' AND (@Prop = 'A' OR @Prop = 'C')
   BEGIN
     SET @SQL = @SQL + ' OR (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.CashFS) AS VARCHAR) + ''-12-31''  AND (P.CashBalance<[S].[AggregFS] AND P.Quantity<[S].[AggregFS]) AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '      
   END

If @Agg Is Null AND (@Prop = 'A' OR @Prop = 'C')
   BEGIN
     SET @SQL = @SQL + ' OR (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.CashFS) AS VARCHAR) + ''-12-31'' AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '      
   END

If @IRA Is Not Null
   BEGIN
     SET @SQL = @SQL + ' OR ((C.DateOfBirth <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.IRAFS-71) AS VARCHAR) + ''-06-30'' AND LEN(P.IRACode) > 0 ) OR (LEN(C.DateOfBirth) = 0 AND LEN(C.Zip) = 0)) '
   END

PRINT @SQL
EXEC(@SQL)
GO
0
Comment
Question by:zimmer9
  • 3
5 Comments
 
LVL 19

Accepted Solution

by:
Melih SARICA earned 2000 total points
ID: 14006682
with this statement
ON C.State <> S.StateFS  WHERE S.StateFS = ''DE'' u get the all Statelist except DE it means all but DE..

and i see did join Where Clauses with Or clause.. ..

u must check ur SQL statement... or SQL Design...
0
 

Author Comment

by:zimmer9
ID: 14006777
I have a table named tblStatesAll (AS S) that contains the fields StateFS and BondsFS.

I have a table named tblCustomers (AS C) that contains the fields CustomerNumber (Primary Key), and State.

I have a table named tlbProducts (AS P) that contains a field CustomerNumber (Foreign Key), PropertyType.

There is a 1 to many relationship between C and P.

1 customer can have many products.
------------------------------------------------------------------------------------------------------------------
I want to create a join that meets the following conditions:

Display all fields in tables C (tblCustomers)  and P (tblProducts) where  
C.CustomerNumber = P.CustomerNumber and
C.State NOT IN S.StateFS (Customers who reside outside of the U.S.). The table S contains all valid State Codes.

Even though I am looking only for Foreign country customers, I use a calculated date field S.MutualFS that will always retrieve the record from the S table where S.StaeFS = 'DE' (Deleware). For ex: I use a series of If
statements such as the following in which I use the calculated date field:

If @Prop = 'A'
   BEGIN
       SET @SQL = @SQL + '  AND ((C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' -                        S.MutualFS) AS   VARCHAR) + ''-12-31'' AND  P.PropertyType=''OTHER'' AND LEN(P.IraCode) = 0) '
   END

I tried the following revision to my SQL statement:

FROM tblStatesAll
       AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON C.State <> S.StateFS   WHERE C.State NOT IN (SELECT StateFS From
tblStatesAll)  

However, I am still retrieving records with a C.State value which is one of the U.S. states and I am trying to SELECT records without a state code or a state code (C.State) that is not in the tblStatesAll table which has a field
S.StatesFS containing all the valid state codes.
0
 

Author Comment

by:zimmer9
ID: 14007264
How 'bout if I start over with the following question:

How would you write the following query ?

I have a table of Customers AS C with a CustomerNumber (Primary Key) , State , DateA
I have a table of Products AS P with a CustomerNumber (Foreign Key) and Property Type
I have a table of StateCodes AS S with a State.
I have a table of StateDormancy as D with a State and PropertyDateB.

I want to select records from the Customer table (foreign customers) that do not have a matching value in the StateCodes table -->>C.State NOT IN (S.State).  

I also want to select a calculated date, DateX which equals C.DateA minus D.PropertyDateB where D.State = 'DE'

Thus, I want to SELECT C.CustomerNumber, C.State, DateX (a calculated field)  
0
 

Author Comment

by:zimmer9
ID: 14007599
select C.*  
from tblStatesAll,
(tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)
WHERE C.State NOT IN (SELECT States From tblStatesOnly)
AND (C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-12-31' AND  P.PropertyType='MUTUAL FUND' AND LEN(P.IraCode) = 0 AND S.StateFS = 'DE')  
0
 

Expert Comment

by:david_chiu
ID: 14007706
Hi, zimmer

Here is some suggestion, I hope it will help!

1. your sql: "FROM tblStatesAll AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON C.State <> S.StateFS  WHERE S.StateFS = ''DE'' '  "
change to : "from tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber where c.state not in (select distinct statefs from tblStatesAll"
2. About using "S.BondsFS": declare @BondsFS CHAR(2)
                                         SET @BondsFs = (select bondsFS from tblStatesAll where StateFS = ''DE'')

I think it will work, If you still cannot solve this problem, please write down the part of the tables and let me know about the details

Best regards,
David Zhao
     
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

810 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