473,366 Members | 1,197 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,366 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 1500
"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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

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.