mattkorguk
Registered User.
- Local time
- Today, 12:53
- Joined
- Jun 26, 2007
- Messages
- 301
Hi All,
I'm trying to transfer an old acc query across to SQL, but I'm having a few issues, here's what I have so far; I'm trying to build a 'mailname' based on the 2 clients associated with a case, ensuring that 'Mr' goes first.
Above is it's current state in SQL and receiving the 'Invalid Column Name' for all of my aliases.
Am I missing something here as I thought these would be ok on a Group by query?
Here's the acc version which works fine;
Any pointers are much appreciated.
I'm trying to transfer an old acc query across to SQL, but I'm having a few issues, here's what I have so far; I'm trying to build a 'mailname' based on the 2 clients associated with a case, ensuring that 'Mr' goes first.
Code:
SELECT MIN(dbo.client.Title) AS t1, MIN(dbo.client.Forename) AS f1, MIN(dbo.client.Surname) AS s1, MAX(dbo.client.Title) AS t2, MAX(dbo.client.Forename) AS f2,
MAX(dbo.client.Surname) AS s2, CASE WHEN [s1] = [s2] AND [t1] = 'Mr' THEN [t1] + ' ' + LEFT([f1], 1) + ' & ' + [t2] + ' ' + LEFT([f2], 1)
+ ' ' + [s1] END + CASE WHEN [s1] = [s2] AND [t2] = 'Mr' THEN [t2] + ' ' + LEFT([f2], 1) + ' & ' + [t1] + ' ' + LEFT([f1], 1)
+ ' ' + [s1] END + CASE WHEN [s1] <> [s2] THEN [t1] + ' ' + LEFT([f1], 1) + ' ' + [s1] + ' & ' + [t2] + ' ' + LEFT([f2], 1) + ' ' + [s2] END AS MailName
FROM dbo.client INNER JOIN
dbo.[case] ON dbo.client.[Case Id] = dbo.[case].[Case Id]
Above is it's current state in SQL and receiving the 'Invalid Column Name' for all of my aliases.
Am I missing something here as I thought these would be ok on a Group by query?
Here's the acc version which works fine;
Code:
SELECT IIf([s1]=[s2] And [t1]="Mr",[t1] & ' ' & Left([f1],1) & ' & ' & [t2] & ' ' & Left([f2],1) & ' ' & [s1],IIf([s1]=[s2] And [t2]="Mr",[t2] & ' ' & Left([f2],1) & ' & ' & [t1] & ' ' & Left([f1],1) & ' ' & [s1],[t1] & ' ' & Left([f1],1) & ' ' & [s1] & ' & ' & [t2] & ' ' & Left([f2],1) & ' ' & [s2])) AS mailname, IIf([s1]=[s2] And [t1]="Mr",[t1] & ' & ' & [t2] & ' ' & [s1],IIf([s1]=[s2] And [t2]="Mr",[t2] & ' & ' & [t1] & ' ' & [s1],[t1] & ' ' & [s1] & ' & ' & [t2] & ' ' & [s2])) AS Salute, First(client.Title) AS t1, Last(client.Title) AS t2, First(client.Forename) AS f1, Last(client.Forename) AS f2, First(client.Surname) AS s1, Last(client.Surname) AS s2, case.[Case Id]
FROM [case] INNER JOIN client ON case.[Case Id] = client.[Case Id]
GROUP BY case.[Case Id];
Last edited: