473,231 Members | 1,575 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

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
Mar 8 '06 #1
4 1493
"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.
Mar 8 '06 #2
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
Mar 9 '06 #3
"Marc DVer" <ma**@not.in.use> wrote in message
news:bL************@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.
Mar 9 '06 #4
Bill Karwin wrote:
"Marc DVer" <ma**@not.in.use> wrote in message
news:bL************@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
Mar 10 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: kon george | last post by:
Can somebody assist. I build this code on a dev laptop and copied across the entire code to a Windows 2003 server with 1.1 framework. It is basic ASP.NE T that uses web service for SQL Server...
3
by: MLH | last post by:
I have a query, qryAppend30DayOld260ies that attempts to append records to tblCorrespondence. When run, it can result in any of the following: appending no records, appending 1 record or appending...
6
by: Lenn | last post by:
Hi, Could someone clarify my confusion regarding passing reference types to a method with ref keyword and explain when it's practical to use it. It's my understanding that in .NET reference...
0
by: Richard Gregory | last post by:
Hi, I have the wsdl below, for an Axis web service, and when I select Add Web Refernce in Visual Studio the proxy is missing a class representing the returnedElementsType (see reference.cs below...
3
by: deko | last post by:
This will take a bit of explanation, so please bear with me... The code below dynamically builds hyperlinks using two queries - query A and query B. I want to optimize the code so I can omit...
5
by: Tom | last post by:
If I have a container class that has a map member which stores pointers to objects that have been created via the new operator and I have a method that returns a entry in the map, would it be best...
20
by: fniles | last post by:
I am using VB.NET 2003, SQL 2000, and SqlDataReader. As I read data from tblA, I want to populate tblB. I use SQLDataReader for both tables. I do not use thread. When I ExecuteReader on tblB, I...
4
by: jith87 | last post by:
Hi, I ve retrieved values from MySql db. From the result set i ve used the getXXX() to get the values in seperate variables. Earlier i used getParameter() to get the values from JSP page to be...
6
by: =?Utf-8?B?U1VOTlk=?= | last post by:
Hi, i am uploading a .docx file into sql2005 and later when i retrieve the file from the database and i open it, i get a error message "The file is corrupted and cannot be open". I am not facing...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.