Acc 'First'/'Last' query conversion (1 Viewer)

mattkorguk · Aug 9, 2010

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.

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];

Any pointers are much appreciated.

LPurvis · Aug 9, 2010

You have two issues.
Firstly TSQL doesn't allow you to refer to aliased columns within the same query.
If that's a shocking inconvenience (which it could be) you can perform your aggregation and aliasing first in a subequery, and then refer to the aliases just fine from the parent query.

e.g.
SELECT X1, X2
FROM
(SELECT Field1 As X1, Field2 As X2 FROM TableName)

However you've also substituted Min and Max aggregate functions for the First and Last that you were using in the Jet SQL.
These really aren't the same at all.
The big question is, what result was the Jet version giving you that you want to emulate in SQL Server? Pulling only the data from the related "Mr" row? Or pulling all rows - but showing the "Mr" row first?

Rather than the query that was being used - consider there might be an overall improvement to be had and perhaps just show some example data and desired formatted result?

Cheers.

mattkorguk · Aug 9, 2010

Thank you very much for your reply.

We have a Case table which is linked to the Client table by [Case Id], so you end up with multiple Clients per Case.
I'm trying to update the [Mail name] on the Case table when a new client is added to the Client table.
So if you had a client Mr James Bond and Mrs Jenny Bond was being added (3 seperate fields for Title, forename and Surname) the Acc query would return;
"Mr and Mrs J Bond" and update as required.

That's basically what I'm trying to achieve.

Acc 'First'/'Last' query conversion (1 Viewer)

mattkorguk

Registered User.

LPurvis

AWF VIP

mattkorguk

Registered User.

Similar threads

Users who are viewing this thread