Store Procedures, SQL statements
I am a newby to SQL and I am currently creating a store procedures for Crystal Reports. I have ran into a Problem. Here is some of my coding:
SELECT @Address = ISNULL(Address,'') from tblAddresses WHERE (ClientNumber = @ClientNumber) AND (AddressType = 'Current')
IF LTRim(RTrim(@Address)) <> ''
SELECT @Address = isnull(Address,'') + char(13) + char(10) + isnull(City,'') + ', ' + isnull(State,'') + ' ' + isnull(Zip1,'')
FROM tblAddresses
WHERE (ClientNumber = @ClientNumber) AND (AddressType = 'Current')
ELSE
SELECT @Address = isnull(Address,'') + char(13) + char(10) + isnull(City,'') + ', ' + isnull(State,'') + ' ' + isnull(Zip1,'')
FROM tblAddresses
WHERE (ClientNumber = @ClientNumber) AND (AddressType = 'Mailing')
I must have Current Address and if there is no Current Address I must get the mailing address. I keeping getting null values. I have tried it many different ways and result is null. In AddressType I have 3 different criterias but I only need 2 in this stored procedures. Can anyone help?
Software/Hardware used:
Software/Hardware used:
ASKED:
September 14, 2006 6:45 PM
UPDATED:
September 14, 2006 9:47 PM
Answer Wiki:
For efficiency, you want to try to do this all in a single query, without variables and IFs. Don't return CRLFs to Crystal. Let Crystal do the formatting. Process as follows:
SELECT CASE WHEN current.address IS NULL THEN
mailing.address ELSE current.address END address,
CASE WHEN current.address IS NULL THEN mailing.city ELSE
current.city END city,
CASE WHEN current.address IS NULL THEN mailing.state
ELSE current.state END state,
CASE WHEN current.address IS NULL THEN mailing.zip1 ELSE
current.zip1 END zip1,
mailing.clientNumber
FROM tblAddresses mailing
LEFT OUTER JOIN tblAddresses current ON
current.addressType='Current' AND
mailing.clientNumber=current.clientNumber AND
LENGTH(TRIM(current.address))>0
WHERE mailing.addressType='Mailing'
You can set this up in Crystal by drawing a connection, and then selecting it, then LEFT. You can get everyone's address at once with this, or add a criterion, and get just one.
---
Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
