Where clause not using a calculated AS field

[twgonder]

I may have asked this kind of question in another thread here, after hours of work I found a workaround,


However, it's now getting to be a pain to deal with this common type of SQL statement.

I've got a search form that builds a SQL string to use as record source.
There is a "calculated" (if that's the proper name here) field that is a concatenation of 4 fields and it gets a AS name.
When the WHERE part runs the Enter Parameter Value box pops up.
It seems the WHERE clause is lost in figuring out what the "calculated" field is.
What am I doing wrong, or is this just another "come on Access!" thing?

Check the highlighted part:

[June7]

I think it's a "come on SQL!" thing.

You must repeat the calculation in WHERE clause. Or don't use WHERE clause and either build another query using the first as data source or apply filter to form or report.

[twgonder]

I think it's a "come on SQL!" thing.

You must repeat the calculation in WHERE clause. ...

Okay thanks, that's another head-sized dent in the wall.

[CJ_London]

Alternatives are:

use or’s instead of a concatenated string in the where clause

wrap the bulk of your query in an alias and have the like clause outside of it

[Minty]

Just as an FYI, SQL Server (and most other larger scale DBMS) doesn't allow you to refer to aliased fields names at all in the same query.
So if you were ever to upscale you would be into a re-write.

I have recently had to re-write an Access query for use in SQL server and it went from 400 characters to about 1500...

[twgonder]

Just as an FYI, SQL Server (and most other larger scale DBMS) doesn't allow you to refer to aliased fields names at all in the same query.
So if you were ever to upscale you would be into a re-write.

I have recently had to re-write an Access query for use in SQL server and it went from 400 characters to about 1500...

I guess I'm no longer in Kansas Dorthy. Imagine, I wrote hundreds of aliases in my old query language (more than a 50-year-old design), and could use in it an alias in an infinite number of queries without having to duplicate the expression. I upscaled through five platforms and never had to rewrite any alias expression or the query itself for that matter. Imagine! Oh, but I didn't have hundreds of color options for the forms, so I guess...

[twgonder]

QUOTE=CJ_London;505156]Alternatives are:

use or’s instead of a concatenated string in the where clause

wrap the bulk of your query in an alias and have the like clause outside of it[/QUOTE]

I'm not sure what all that means. In the program, I've broken the SQL statement into three parts. Then it is assembled into one statement that serves the particular search purpose, and in some cases, all I change is the Order By in the form, without having to do a requery. Here's how it turned out from my example above:

[

[Minty]

I think you may have misunderstood me, in SQL Server you can use the alias in another query, just not the same one you created it in.
So you can do this

SELECT Q1.MyCalc , Q1.MyOtherCalc , Q1.MyCalc - Q1.MyOtherCalc as NewResult 
FROM 
(
SELECT Price1 * Uplift as MyCalc, Price2 * Uplift as MyOtherCalc
From MyTable
) As Q1

But not

SELECT Price1 * Uplift as MyCalc, Price2 * Uplift as MyOtherCalc,  MyCalc - MyOtherCalc as NewResult
From MyTable
As Q1

The latter would work in Access however, it doesn't object provided you do things in the correct order.

[CJ_London]

if you provided your sql as text rather than an image, I could show you

but basically

SELECT * FROM (SELECT tbl_3TranIt.ID blah, blah
FROM table1 inner join table2 on blah, blah) as q
WHERE lblsMsgHlp like "*caci*"

[ArviLaanemets]

I think you may have misunderstood me, in SQL Server you can use the alias in another query, just not the same one you created it in.
...
But not
SELECT Price1 * Uplift as MyCalc, Price2 * Uplift as MyOtherCalc, MyCalc - MyOtherCalc as NewResult
From MyTable
As Q1
...

But you can have (this works for both Access and SQL Server)

SELECT Q1.Price1 * Q1.Uplift as MyCalc, Q1.Price2 * Q1.Uplift as MyOtherCalc,  Q1.Q1.Price1 * Q1.Uplift - Q1.Price2 * Q1.Uplift as NewResult
From MyTable Q1

But probably you did mean field aliases, not table alias? Those you can't use in same query in SQL Server. I'm not sure about Access, as I never tried to use this kind of syntax there.

[NoellaG]

if you know how T-SQL works, it's rather obvious why you need to repeat the calculation:
-First the from clause is evaluated
-Then secondly the WHERE clause
-Then the SELECT is evaluated and here the alias is build
-and last of all the ORDER BY
So on the moment the WHERE clause is executed the alias field doesn't exist yet. But you can use the alias in the ORDER BY.

If you don't want your DBA to get a heart attack: rewrite your Access queries when switching to SQL server.
General rules:
- don't use select *, but only use the columns you really need,
- write queries so the indexes can be use, example don't use calculations in the first part of the criteria (example WHERE year(Mydate) > ...)
- try to avoid sub queries: use joins instead or use a CTE (example: with qry1 as (select ...)
- SQL server has a great tool to check your query performance: the query store