Writing a “If” & “Then” Statement in Crystal Reports 9
Hi,
I'm new to the site and reletively new to writing code so, please bare with me. I'm trying to pull duplicate records from a field that has multiple member types {Member_Types.MEMBER_TYPE}. What I want to do is pull only the duplicate records that contain a valid member type.
DJandLO
Software/Hardware used:
Crystal Reports 9, iMIS 10.6
I'm new to the site and reletively new to writing code so, please bare with me. I'm trying to pull duplicate records from a field that has multiple member types {Member_Types.MEMBER_TYPE}. What I want to do is pull only the duplicate records that contain a valid member type.
- For example 2 records meet my duplicate criteria & have a member type of GEN & NM - I want to show on the report.
- But if 2 records meet my duplicate criteria & have a member type of NM & 01 - I don't want it to show on the report.
DJandLO
Software/Hardware used:
Crystal Reports 9, iMIS 10.6
ASKED:
June 8, 2011 7:52 PM
UPDATED:
June 9, 2011 1:52 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
How are you checking your duplicate criteria ?
Currently we’re using the match key below:
UPPER(SUBSTRING(Name.ZIP,1,5) + (CASE WHEN Name.LAST_NAME<>” THEN (SUBSTRING(Name.LAST_NAME,1,4) + SUBSTRING(Name.FIRST_NAME,1,1)) ELSE SUBSTRING(Name.COMPANY,1,5) END))
I’m not sure to understand. That doesn’t check for duplicates at all.
I would expect something in your report’s query.
ok, I viewed the stored procedure it’s calling and it’s below:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].
BEGIN
SELECT N.ID, N.LAST_NAME, N.FIRST_NAME, NA.ADDRESS_1, NA.ADDRESS_2, NA.CITY, NA.STATE_PROVINCE,
NA.ZIP, NA.COUNTRY, N.MEMBER_TYPE
FROM NAME N, NAME_ADDRESS NA, ( SELECT LAST_NAME, STATE_PROVINCE, COUNTRY, COUNT(1) COUNT1
FROM NAME
WHERE LAST_NAME <> ” AND STATE_PROVINCE <> ”
GROUP BY LAST_NAME, STATE_PROVINCE, COUNTRY
HAVING COUNT(1) > 1) T
WHERE T.LAST_NAME = N.LAST_NAME
AND T.STATE_PROVINCE = NA.STATE_PROVINCE
AND T.STATE_PROVINCE = N.STATE_PROVINCE
AND T.COUNTRY = NA.COUNTRY
AND N.ID = NA.ID
AND N.MAIL_ADDRESS_NUM = NA.ADDRESS_NUM
– AND T.LAST_NAME = ‘HARRISON’
ORDER BY N.LAST_NAME, N.STATE_PROVINCE, N.COUNTRY
END