Solved

T-SQL Syntax Question

Posted on 2011-03-11
8
328 Views
Last Modified: 2012-05-11
My knowledge of SQL is from Fox and Access.  I'm creating a view in T-SQl on the SQL Server and having trouble transcribing what I would have done in Fox SQL to T-SQL.

I’m picking up the address for these Leads from a table separate from the lead table where the “C”-Current address is on one line and “P” – Parent Address is on another.  AddressNumber=1 and =2 respectively.  So I need these addresses both on one line in the view.  I would have used iif() and grouped by the person but apparently that's not how it's done in T-SQL.  I was trying to use a Case statement as a replacement for iif, but can't figure out how to group it ... anyway... what's the best way to do this?  Thanks!

SELECT       IX.Co_activedate, IX.Co_name, U.Co_name as UcodeID,
      S.FirstName + ' ' + S.LastName as AD,IX.Co_invitationtype as InvType,CX.Co_chaptername as Chapter,
      A.customersizecode-199999 as tier, L.FirstName + L.LastName as Name, L.emailaddress1, IX.Co_prospecttype, SX.Co_seasoncode,
      CASE WHEN AddressNumber=1 THEN LA.Line1 END as Addr1C,
      CASE WHEN AddressNumber=1 THEN LA.Line2 END as Addr2C,
      CASE WHEN AddressNumber=1 THEN LA.City END as CityC,
      CASE WHEN AddressNumber=2 THEN LA.Line1 END as Addr1P,
      CASE WHEN AddressNumber=2 THEN LA.Line2 END as Addr2P,
      CASE WHEN AddressNumber=2 THEN LA.City END as CityP
FROM GKIHSINT_MSCRM.dbo.Co_InvitationExtensionBase IX WITH (NOLOCK)
      INNER JOIN GKIHSINT_MSCRM.dbo.LeadExtensionBase LX WITH (NOLOCK) ON LX.Co_primaryinvitationid = IX.Co_invitationid
      INNER JOIN GKIHSINT_MSCRM.dbo.LeadBase L WITH (NOLOCK) on LX.LeadId = L.LeadId
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_ChapterExtensionBase CX WITH (NOLOCK) ON LX.Co_primarychapterid = CX.Co_chapterid
      INNER JOIN GKIHSINT_MSCRM.dbo.AccountBase A WITH (NOLOCK) ON CX.Co_universityaccountid = A.AccountId
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_CountryExtensionBase COX WITH (NOLOCK) ON LX.Co_countryid = COX.Co_countryid
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_membershipdriveExtensionBase MX  WITH (NOLOCK) ON IX.Co_membershipdriveId = MX.Co_MembershipDriveId
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_seasonExtensionBase SX WITH (NOLOCK) ON MX.Co_SeasonId = SX.Co_seasonId
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_ucode U WITH (NOLOCK) ON CX.Co_ucodeid = U.Co_ucodeId
      LEFT OUTER JOIN GKIHSINT_MSCRM.dbo.LeadAddressBase LA with (NOLOCK) ON LX.LeadId = LA.ParentId
      LEFT OUTER JOIN GKIHSINT_MSCRM.dbo.SystemUserBase S WITH (NOLOCK) ON CX.Co_associatedirectorid = S.SystemUserId
WHERE COX.Co_name in ('USA', 'Bahamas') and coalesce(LX.Co_InvitationCount,0)<3 and      
      datediff(d, getdate(),IX.Co_activedate)  > -72  and IX.Co_invitationaccepted = 0
      AND L.statecode = 0
GROUP BY IX.Co_activedate, IX.Co_name, U.Co_name,
      S.FirstName + ' ' + S.LastName,IX.Co_invitationtype,CX.Co_chaptername,
      A.customersizecode-199999, L.FirstName + L.LastName,L.emailaddress1, IX.Co_prospecttype, SX.Co_seasoncode
0
Comment
Question by:sarahellis
8 Comments
 
LVL 3

Expert Comment

by:brd24gor
ID: 35110400
Microsoft has a great page on how to use CASE in T-SQL. There is a section on how you'd replace an IIF statement from ACCESS. It looks like you're very close with syntax. What kind of errors are you getting?

http://msdn.microsoft.com/en-us/library/ms181765.aspx
0
 

Author Comment

by:sarahellis
ID: 35110547
Yes, that's actually where I got the CASE syntax.  I need to be able to group it on all the other fields (I've since added LeadID to the select clause, and that's actually the unique identifier).  It doesn't like the CASE statement since has to be either an aggregate function or in the group by clause.  Which should it be?  I've tried clumsily to do both, to no avail.
0
 

Author Comment

by:sarahellis
ID: 35110611
In the address table, each lead has only one addressnumber = 1 and addressnumber = 2.    So there's never two lines addressnumber=2 for any singe lead.  So I would have probaly used max() to group.  Could coalesce() work to find the first not null?  If so, where exactly would I put that?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Expert Comment

by:Sharath
ID: 35112234
Can you post the result of your query and the expected result?
0
 

Author Comment

by:sarahellis
ID: 35112654
Well the result of the query above was giving me two rows.   One for each address.  I really should have posted a more simplified version of what I'm asking.  Maybe the complexity has muddied the water.

Basically what I’m trying to do is get the following information into one record for each LeadID:

From the Lead Table:   Lead.LeadID              

Then from the Address table joined on LeadID:
Where addressnumber = 1  select  Address as CAddress
Where addressnumber = 2  select  Address as PAddress

There never more than 2 address records (addressnumber = 1 and addressnumber = 2) for each LeadID .

In Fox I would have...

select leadid, max(iif(addressnumber=1,Address,’’))as CAddress, max(iif(addressnumber=2,Address,''))as PAddress
from lead left join address on lead.leadid=address.leadid
group by LeadID

How would this be best accomplished in T-SQL?
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 35119223
try

SELECT IX.Co_activedate, IX.Co_name, U.Co_name as UcodeID,
      S.FirstName + ' ' + S.LastName as AD,IX.Co_invitationtype as InvType,CX.Co_chaptername as Chapter,
      A.customersizecode-199999 as tier, L.FirstName + L.LastName as Name, L.emailaddress1, IX.Co_prospecttype, SX.Co_seasoncode,
      LA1.Line1 Addr1C,
      LA1.Line2 Addr2C,
      LA1.City CityC,
      LA2.Line1 Addr1P,
      LA2.Line1 Addr2P,
      LA2.Line1 CityP
FROM GKIHSINT_MSCRM.dbo.Co_InvitationExtensionBase IX WITH (NOLOCK)
      INNER JOIN GKIHSINT_MSCRM.dbo.LeadExtensionBase LX WITH (NOLOCK) ON LX.Co_primaryinvitationid = IX.Co_invitationid
      INNER JOIN GKIHSINT_MSCRM.dbo.LeadBase L WITH (NOLOCK) on LX.LeadId = L.LeadId
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_ChapterExtensionBase CX WITH (NOLOCK) ON LX.Co_primarychapterid = CX.Co_chapterid
      INNER JOIN GKIHSINT_MSCRM.dbo.AccountBase A WITH (NOLOCK) ON CX.Co_universityaccountid = A.AccountId
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_CountryExtensionBase COX WITH (NOLOCK) ON LX.Co_countryid = COX.Co_countryid
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_membershipdriveExtensionBase MX  WITH (NOLOCK) ON IX.Co_membershipdriveId = MX.Co_MembershipDriveId 
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_seasonExtensionBase SX WITH (NOLOCK) ON MX.Co_SeasonId = SX.Co_seasonId
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_ucode U WITH (NOLOCK) ON CX.Co_ucodeid = U.Co_ucodeId
      LEFT OUTER JOIN GKIHSINT_MSCRM.dbo.LeadAddressBase LA1 with (NOLOCK) ON LX.LeadId = LA1.ParentId
      LEFT OUTER JOIN GKIHSINT_MSCRM.dbo.LeadAddressBase LA2 with (NOLOCK) ON LA2.ParentId = LA1.ParentID
      LEFT OUTER JOIN GKIHSINT_MSCRM.dbo.SystemUserBase S WITH (NOLOCK) ON CX.Co_associatedirectorid = S.SystemUserId
WHERE COX.Co_name in ('USA', 'Bahamas') and coalesce(LX.Co_InvitationCount,0)<3 and      
      datediff(d, getdate(),IX.Co_activedate)  > -72  and IX.Co_invitationaccepted = 0
      AND L.statecode = 0
GROUP BY IX.Co_activedate, IX.Co_name, U.Co_name,
      S.FirstName + ' ' + S.LastName,IX.Co_invitationtype,CX.Co_chaptername,
      A.customersizecode-199999, L.FirstName + L.LastName,L.emailaddress1, IX.Co_prospecttype, SX.Co_seasoncode

Open in new window

0
 

Author Comment

by:sarahellis
ID: 35121384
Thanks!  Great idea.  Coincidentally, someone else I know came up with the same idea of joining on the table twice with different aliases.   Although I still have to identify the address line using the AddressNumber field, so I'll probably try something like:

LEFT OUTER JOIN GKIHSINT_MSCRM.dbo.LeadAddressBase LA1 with (NOLOCK) ON LX.LeadId = LA1.ParentId and addressnumber=1
LEFT OUTER JOIN GKIHSINT_MSCRM.dbo.LeadAddressBase LA2 with (NOLOCK) ON LX.LeadId = LA2.ParentId and addressnumber=2

Hopefully that will work.  I'll let you know on Tuesday when I'm back on that job.
0
 

Author Closing Comment

by:sarahellis
ID: 35319258
So sorry for the delay in posting the points.  I completely forgot.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

730 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