Bill Karwin wrote:
"Marc DVer" <ma**@not.in.use> wrote in message
news:wr************@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