Reference to already retrieved values in query

As a simple example, say there is table 'namelist' with column 'names'
as char(20). I would like to do something akin to:

select namelist.names as mynames, left(mynames,2) as initials;

In this example, I could just do left(namelist.names,2), but in more
complex cases a value retrieved may have had a more complex logic behind
it, e.g., if a bunch of nested if() statements. It would seem logical
that if a value is already retrieved then I should be able to refer to
it within the same query, assuming that the original value does not need
to be recalculated. I know I could use a view to do this, but it adds
another layer of complexity, and I am not sure that the values would not
be recalculated each time the value is needed.

If someone could point out to me the correct terminology for this kind
of thing I believe I should then be able to look up the information
myself. My efforts to find this information on google and the mysql
website were unsuccessful.

Marc

Re: Reference to already retrieved values in query

"Marc DVer" wrote in message
news:wrFPf.12$KO5.0@fe11.lga...
> select namelist.names as mynames, left(mynames,2) as initials;

You could also put your mynames results in a temp table and then query the
temp table.

Or you could use a "derived table":
select mynames, left(mynames, 2) as initials
from (select ...expression... as mynames from namelist) as mynamelist

Or you could create the initials in your application after you fetch the
result set. A 2-character substring is a pretty simple operation to do in
any programming language.

Regards,
Bill K.

Re: Reference to already retrieved values in query

Bill Karwin wrote:
> "Marc DVer" wrote in message
> news:wrFPf.12$KO5.0@fe11.lga...
>> select namelist.names as mynames, left(mynames,2) as initials;
>
> You could also put your mynames results in a temp table and then query the
> temp table.
>
> Or you could use a "derived table":
> select mynames, left(mynames, 2) as initials
> from (select ...expression... as mynames from namelist) as mynamelist
>
> Or you could create the initials in your application after you fetch the
> result set. A 2-character substring is a pretty simple operation to do in
> any programming language.
>
> Regards,
> Bill K.
>
>

I'll have to think about how I can use one of these solutions into my
project.

Just to be clear, the example I gave was meant solely for communication
purposes, i.e., just enough information to make my question clear.
However, I believe that might have been a mistake.

In my intended use I would like to use a set of nested if statements to
decide which value, if any, among a given set of previously known
fields, should be presented as another field.

E.g., in a table 'namelist' with fields "FirstName", and "LastName", the
query would get both and then present a third field being either
"firstname" or "lastname", perhaps called "intendedName". The logic for
intendedName might be if(right(firstname,1)="A",firstname,lastname) as
intendedname.

Again, this just an example for illustrative purposes. In my intended
application I need to choose from among several fields depending on the
day of week. If its Monday, it would return whatever happens to be in
the field I choose for Monday, and a different one for Tuesday, etc.
The fields that are based on days are basically a cross tabulation
query, in that the contents of the field for Monday are based on
criteria that are Monday specific, and are not Tuesday's criteria.
While I could probably utilize the second suggestion for this it would
appear to require the logic for the day based fields to be duplicated in
the from clause, which would defeat the point in this case. At that
point it would probably be faster to duplicate the logic in the select
clause. In regards to the temp table, the query will be executed
hundreds of times a day. I can't begin to imagine how bogged down the
server would get when using a temp table, even using one of the RAM
based types. Also, the query itself will be run by different
simultaneous users, most of whom will be getting the information using a
separate query that gets only the data relevant to that person's login.

What I am trying to do is make the query as simple as possible to the
application seeing it without causing an undue burden on the server.

While this could be done on the application side, I am trying to put as
much of the business logic as possible on the database, mainly to make
program maintenance easier. I don't have the luxury of implementing a
3-tier solution, or anything resembling that.

I am very appreciative of the advice I have received on this.

Marc

Re: Reference to already retrieved values in query

"Marc DVer" wrote in message
news:bLXPf.11$S%3.7@fe10.lga...
> Again, this just an example for illustrative purposes. In my intended
> application I need to choose from among several fields depending on the
> day of week. If its Monday, it would return whatever happens to be in
> the field I choose for Monday, and a different one for Tuesday, etc.

You might want to use the CASE function instead of IF.

> The fields that are based on days are basically a cross tabulation query,
> ...

Cross-tab queries are not easy to implement in plain SQL. I'd recommend
that you do some Google searches for some solutions and examples. I've seen
a few articles on this subject but I rarely have a need for a cross-tab
query so I don't have a lot of knowledge about implementing them.

Regards,
Bill K.

Re: Reference to already retrieved values in query

Bill Karwin wrote:
> "Marc DVer" wrote in message
> news:bLXPf.11$S%3.7@fe10.lga...
>> Again, this just an example for illustrative purposes. In my intended
>> application I need to choose from among several fields depending on the
>> day of week. If its Monday, it would return whatever happens to be in
>> the field I choose for Monday, and a different one for Tuesday, etc.
>
> You might want to use the CASE function instead of IF.
>

Sounds like a good idea. I'll have to work on that.

>> The fields that are based on days are basically a cross tabulation query,
>> ...
>
> Cross-tab queries are not easy to implement in plain SQL. I'd recommend
> that you do some Google searches for some solutions and examples. I've seen
> a few articles on this subject but I rarely have a need for a cross-tab
> query so I don't have a lot of knowledge about implementing them.
>
> Regards,
> Bill K.
>
>

I already have to cross-tab query working. I was hoping to be able to
use results from the cross-tab within the same query as opposed to
redoing the data fetching.

Marc