SQL Alias (select [ab]) as Bob - need to reference later in same query (1 Viewer)

Rx_ · Oct 29, 2015

The SQL statement works perfectly in MS Access
There are 2 databases - one SQL Server and Oracle.
One Select statement looks at a Table.Field in SQL Server and a Table.Field in Oracle. It evaluates if they are the same data and returns a True/False under an Alias field name.

Then, several of the alias results are AND together to determine a total Pass/fail.

Not shown - the Where statement filters out only the false (unmatched records).

Problem: in the SQL Server editor - it just won't accept the alias field -- the one with the AND evaluation at the end

Code:

SELECT Wells.[Well_Name],
Wells.DtNavigatorHeadersCreated,  
vsrNavigatorSHLBHL.NavSH_QQ, 
[vSHLBHL_TopStatus].[SHL QQ] + [vSHLBHL_TopStatus].[SHL QQ2] AS QtrQtrSH, 
(Select case when isnull([NavSH_TS_NB],0)= isnull([SHL TWN],0) then 1 else 0 end) as SHTownship,
case when isnull([NavSH_TS_Dir],'')= isnull([SHL TWN2],'') then 1 else 0 end as SHtowndir,
case when isnull([NavSH_RG_Nb],0)= isnull([SHL Range],0) then 1 else 0 end as SHrange,
case when isnull([NavSH_Rg_Dir],'')= isnull([SHL Range2],'') then 1 else 0 end as SHrange2,
case when isnull([NavSH_Sec_NB],0)= isnull([SHL Sect],0) then 1 else 0 end as SHsection,
case when isnull([NavSHVert_Ft],0)= isnull([SHL Ft1],0) then 1 else 0 end as SHverticalfeet,
case when isnull([NavSHVert_AB],'')= isnull([SHL FtFNL],'') then 1 else 0 end as SHverticalfeet1,
case when isnull([NavSHLat_Ft],0)= isnull([SHL Ft2],0) then 1 else 0 end as SHlateralfeet,
case when isnull([NavSHLat_AB],'')= isnull([SHL Ft2FNL],'') then 1 else 0 end as SHlateralfeet1,
case when isnull([SHL QQ] + [SHL QQ2],'')= isnull([NavSH_QQ],'') then 1 else 0 end as shQQ,
-- The above reads a field in SQL and a field in Oracle
-- If they are the same return 0 if not the same return 1
-- later I want to know if all the fields pass/fail a match
-- this would be an example of checking 3 of the fields
(SELECT [SHTownship] AND [shQQ] AND [SHlateralfeet1])  AS SH_Matched,

-- The Alias fields are 0/1   The last select evaluates if all passed (using AND)
-- However the SQL editor doesn't recognize the Alias names - it can't process the Alias name in the same statement

Rx_ · Oct 29, 2015

Let me make this a little simpler with out the complex

Create an Alias field name STIP_WasaMatter
Then, can take the Alias output and reference it

Tried this in the SQL Server editor. The line commented out just before the From statement won't recognize the Alias field above.

Code:

SELECT [ID_APD_Stips1]
      ,[Stip_Abv]
      ,(select [Stip_Name]+'WazaMatter') as STIP_WasaMatter
      --  , (select [STIP_WasaMatter] & 'you') as Stip_WasaMatter_you  -- This field is not recognized
  FROM [RegDB].[dbo].[APD_Stips1]
GO
-- However the SQL editor doesn't recognize the Alias name

Rx_ · Oct 29, 2015

OK, played around with it and discovered the Table - not just the row has to be processed with the Alias (including a formula).
I think this is called a nested select statement

Code:

SELECT [ID_APD_Stips1], [Stip_Abv],  STIP_WasaMatter +'You' as Stip_WasaMatter_you 
	FROM
	(SELECT
	[ID_APD_Stips1], [Stip_Abv], STIP_WasaMatter = [Stip_Name]+'WazaMatter'      
FROM [RegulatoryDB].[dbo].[APD_Stips1]) 
as FinalOutput

STIP_WasaMatter = [Stip_Name]+'WazaMatter' represents a simple formula
STIP_WasaMatter +'You' as Stip_WasaMatter_you represents taking the formula to conduct another simple formula on it.

Why is this good?
Step 1 nested select statement processes formula first
Process formula first. Each one of the results have a Alias Name.

Step 2
Only one Where clause - reduces round trip call to Oracle ODBC over VPN, the slower network.

My reading indicates that only using 1 Where clause (in the sub query) is much faster. The outer query can take the results of the sub query and apply a formula to the results.

AnthonyGerrard · Nov 2, 2015

Rx_ said:
OK, played around with it and discovered the Table - not just the row has to be processed with the Alias (including a formula).
I think this is called a nested select statement

Code:

SELECT [ID_APD_Stips1], [Stip_Abv], STIP_WasaMatter +'You' as Stip_WasaMatter_you FROM (SELECT [ID_APD_Stips1], [Stip_Abv], STIP_WasaMatter = [Stip_Name]+'WazaMatter' FROM [RegulatoryDB].[dbo].[APD_Stips1]) as FinalOutput

STIP_WasaMatter = [Stip_Name]+'WazaMatter' represents a simple formula
STIP_WasaMatter +'You' as Stip_WasaMatter_you represents taking the formula to conduct another simple formula on it.

Why is this good?
Step 1 nested select statement processes formula first
Process formula first. Each one of the results have a Alias Name.

Step 2
Only one Where clause - reduces round trip call to Oracle ODBC over VPN, the slower network.

My reading indicates that only using 1 Where clause (in the sub query) is much faster. The outer query can take the results of the sub query and apply a formula to the results.

Is it not that in your erroring code - you reuse an alias in the select statement - which in the logical process ordering will error.

In the fix - the alias is moved to the from , from here the select understands what the alias is.

Couldn't you just make each alias up from its non alias components in the select statement?

Rx_ · Nov 2, 2015

I think your right. Some of the SQL Server Central users schooled me on the order of processing.

However, a senior person On SQL Server Central on his 6,444 post also suggested this:

Since the FROM clause is processed before the SELECT clause, you can use a CROSS APPLY to create a reusable alias.

Code:

SELECT [ID_APD_Stips1], [Stip_Abv],  STIP_WasaMatter +'You' as Stip_WasaMatter_you 
FROM [RegulatoryDB].[dbo].[APD_Stips1]
CROSS APPLY ( VALUES([Stip_Name]+'WazaMatter') ) calc(STIP_WasaMatter)
/[Code]

Another TSQL keyword that isn't part of MSAccess query language.

Rx_ · Nov 2, 2015

Sorry to keep coming back to this subject. Another expert at SQL Server Central provided yet another solution to the nested query. This might actually work in MS Access.

First of all, he included this excellent poster Logical Query Processing provided in the Inside Microsoft T-SQL Querying.
http://tsql.solidq.com/books/insidetsql2008/Logical Query Processing Poster.pdf

On the poster, look at the Order By - next to the last process on the poster's flowchart.
Ten Centuries posted this helpful suggestion:
SQL was written to be as close to English as possible which is why the SELECT statement comes first but the SELECT statement isn't processed first. This throws people. The major (keeping this simple for brevity): logical query processing phases are:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

So although SELECT appears first in a query, it's actually processed after FROM, WHERE, GROUP BY, etc... This is why you can't use an alias in your joins, WHERE clause, etc.. because SQL Server has not processed your SELECT statement and therefore the alias has not been created.
You can, however, use an alias in the ORDER BY clause because that is processed after SELECT.

These are some great tips that will change my approach for creating views in SQL Server for the future.

AnthonyGerrard · Nov 2, 2015

I think that's essentially doing the same thing as your solution , in moving it earlier in the processing order so its recognised.

But there is no need, just calculate the select from the basics.

If it optimises better fair enough.

SQL Alias (select [ab]) as Bob - need to reference later in same query (1 Viewer)

Rx_

Nothing In Moderation

Rx_

Nothing In Moderation

Rx_

Nothing In Moderation

AnthonyGerrard

Registered User.

Rx_

Nothing In Moderation

Rx_

Nothing In Moderation

AnthonyGerrard

Registered User.

Similar threads

Users who are viewing this thread