Solved

T-SQL Syntax Question

Posted on 2011-03-11
8
325 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

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:
ewangoya 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

776 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