473,836 Members | 1,491 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

column definition that references other data in same row

I realize that this probably isn't a best practice, but I'm working with
legacy code that has a query stored in one column of a table. Because
the queries vary, the JSP page that selects a query and runs it cannot
add any additional information (like a WHERE clause).

I need to add a few more records to the the table, and would like the
query to include a value from another field in the current row in a
WHERE clause -- something like 'WHERE id = this.idlist' to use a
Java-like syntax, where 'this' would mean a value retrieved from the
current row of this table.

I.e., the table will have rows like the following:

| id | idlist | query |
------ --------- --------------------------------------------
3 12 'SELECT * FROM X WHERE id = this.idlist'
So that the query value ends up as:

'SELECT * FROM X WHERE id = 12'

Is such a thing possible?
Jun 17 '06 #1
6 2436
>I realize that this probably isn't a best practice, but I'm working with
legacy code that has a query stored in one column of a table. Because
the queries vary, the JSP page that selects a query and runs it cannot
add any additional information (like a WHERE clause).
Since when is anything in a WHERE clause a *definition* of a column?
I need to add a few more records to the the table, and would like the
query to include a value from another field in the current row in a
Define: current row. Is this anything besides the row you are adding?
WHERE clause -- something like 'WHERE id = this.idlist' to use a
Java-like syntax, where 'this' would mean a value retrieved from the
current row of this table.
Define: current row. Is this anything besides the row you are adding?

Isn't this adding a WHERE clause like you said you couldn't do?

You can have a query:
SELECT * FROM X WHERE id = idlist

which selects rows where id and idlist in the same row are equal.
It does *not* select based on the row where the query came from,
as there is no concept of "the row where the query came from" in
SQL, and the vast majority of queries do not come from rows.


I.e., the table will have rows like the following:

| id | idlist | query |
------ --------- --------------------------------------------
3 12 'SELECT * FROM X WHERE id = this.idlist'
So that the query value ends up as:

'SELECT * FROM X WHERE id = 12'

Is such a thing possible?


If you want to substitute values into queries, substitute values into
queries. This normally involves changing the code.

Gordon L. Burditt
Jun 17 '06 #2
Gordon Burditt wrote:
I realize that this probably isn't a best practice, but I'm working with
legacy code that has a query stored in one column of a table. Because
the queries vary, the JSP page that selects a query and runs it cannot
add any additional information (like a WHERE clause).
Since when is anything in a WHERE clause a *definition* of a column?


It isn't. But one column of this table contains queries as text. I.e.,
the query I'm referring to is data in the table. The JSP retrieves the
appropriate query from the table and runs it.
I need to add a few more records to the the table, and would like the
query to include a value from another field in the current row in a
Define: current row. Is this anything besides the row you are adding?


No, other than semantics. By current row I mean the row I have added,
but at the time I retrieve it as opposed to at the time I add it.
WHERE clause -- something like 'WHERE id = this.idlist' to use a
Java-like syntax, where 'this' would mean a value retrieved from the
current row of this table.
Define: current row. Is this anything besides the row you are adding?


See above.

Isn't this adding a WHERE clause like you said you couldn't do?
No, because the WHERE clause is in the contents of the field as opposed
to in the JSP.

You can have a query:
SELECT * FROM X WHERE id = idlist

which selects rows where id and idlist in the same row are equal.
It does *not* select based on the row where the query came from,
as there is no concept of "the row where the query came from" in
SQL, and the vast majority of queries do not come from rows.
No, but data does, and there are certainly plenty of functions that work
on data from the current row (IF, CONCAT, etc.). What I'd like to do is
embed some of that type of logic into a field at the time the record is
written as opposed to having to write external logic to handle the task.

The queries I'm speaking are quite varied, which is why they are stored
in a table. The problem is that for the simpler queries, the WHERE
clause simply uses the autonumber field for this record. My current
solution is to add a record, then edit it so I can see what the
autonumber field was and hard-code that into the query stored in that
row. I'd like something a little more dynamic.

I.e., the table will have rows like the following:

| id | idlist | query |
------ --------- --------------------------------------------
3 12 'SELECT * FROM X WHERE id = this.idlist'
So that the query value ends up as:

'SELECT * FROM X WHERE id = 12'

Is such a thing possible?


If you want to substitute values into queries, substitute values into
queries. This normally involves changing the code.

Gordon L. Burditt

Jun 18 '06 #3
I'm trying to do this same thing. Have a table that has a column
definition that finds it's value based on values in the same row.

This sort of practice is used on Access very often.

Jun 22 '06 #4
Steve wrote:
| id | idlist | query |
------ --------- --------------------------------------------
3 12 'SELECT * FROM X WHERE id = this.idlist'

So that the query value ends up as:

'SELECT * FROM X WHERE id = 12'

Is such a thing possible?


Certainly it is possible, but you'll have to parse the query string and
substitute values into it. Probably involving coming up with your own
notation in the query string.

But this seems like perpetuating -- and even extending -- a code design
that was a mistake to begin with. Sounds like the normalization of this
database is completely broken, if idlist can reference different things
_per row_.

If this were an object-oriented system like Java, it'd be like using a
Collection of objects to store objects of different types. This is a
not good to do, because it's prone to errors that are hard to diagnose.
Hence Java 5 introduced generics, to catch such mistakes at compile-time.

What you're describing doing with SQL is similarly a bad design. Any
set (e.g. a column) should contain only objects of the same type. I
don't mean primitive datatypes like INT, I mean the entities should be
members of the same logical domain.

For instance, you wouldn't use a single INT to store either a person's
age, or the number of dogs they own, or a foreign key to some other
table. It's too confusing to tell which of these meanings to use on a
given row; if you make a mistake, you can make false conclusions about
the attribute.

If idlist can reference multiple things in other tables, this is a clue
that idlist should be multiple columns. Each column references one
_type_ of thing, and those that are not relevant on a given row should
have a NULL state.

If your database is properly normalized, you shouldn't need to store
different queries per row. You'd be better off spending your time
fixing this legacy code than extending it.

Regards,
Bill K.
Jun 22 '06 #5
I think you're misunderstandin g my app, based on a column name. idlist
is not a list of id's -- therefore no collection is needed. It is the
id of a list, since this is a distribution list application. For a
particular project, the distribution list might be "everyone in plant
engineering for plant 123 and everyone on capital expenditures committee
and everyone in finance over a certain grade level". So the table is
basically storing decision rules in the form of queries.

But, there are a lot of manually created lists for which the
distribution is "everyone on this list", which is where the
self-referencing part comes in -- for a row in this table with idlist of
12, a matching table would have a set of entries each with an employee
id and the idlist of 12 for this list.

And yes, this design could be normalized, but that would be by using
some sort of criteria object, and a self-referencing table that and-ed
and or-ed existing criteria into new criteria, etc. For the amount of
this that needs to be done, creating and debugging everything associated
with it would be far more effort and risk than the current approach.

I did come up with a solution -- since there is no case where there is
both a complex criteria and a simple one, I just checked the query
column for NULL, and if it was, manually create the simple criteria from
the idlist field. Resulting in a minimal and controlled amount of
messing with the JSP code.

Bill Karwin wrote:
Steve wrote:
| id | idlist | query |
------ --------- --------------------------------------------
3 12 'SELECT * FROM X WHERE id = this.idlist'

So that the query value ends up as:

'SELECT * FROM X WHERE id = 12'

Is such a thing possible?


Certainly it is possible, but you'll have to parse the query string and
substitute values into it. Probably involving coming up with your own
notation in the query string.

But this seems like perpetuating -- and even extending -- a code design
that was a mistake to begin with. Sounds like the normalization of this
database is completely broken, if idlist can reference different things
_per row_.

If this were an object-oriented system like Java, it'd be like using a
Collection of objects to store objects of different types. This is a
not good to do, because it's prone to errors that are hard to diagnose.
Hence Java 5 introduced generics, to catch such mistakes at compile-time.

What you're describing doing with SQL is similarly a bad design. Any
set (e.g. a column) should contain only objects of the same type. I
don't mean primitive datatypes like INT, I mean the entities should be
members of the same logical domain.

For instance, you wouldn't use a single INT to store either a person's
age, or the number of dogs they own, or a foreign key to some other
table. It's too confusing to tell which of these meanings to use on a
given row; if you make a mistake, you can make false conclusions about
the attribute.

If idlist can reference multiple things in other tables, this is a clue
that idlist should be multiple columns. Each column references one
_type_ of thing, and those that are not relevant on a given row should
have a NULL state.

If your database is properly normalized, you shouldn't need to store
different queries per row. You'd be better off spending your time
fixing this legacy code than extending it.

Regards,
Bill K.

Jun 28 '06 #6
Steve wrote:
I think you're misunderstandin g my app, based on a column name.


No, I assumed that idlist was a value, not a list of values. You hadn't
mentioned that it was a list, and the way you were using it in the
example in your original post showed that it was a single value.

What I was cautioning against was using the criteria differently on each
row. You have a column which is a string forming a SQL statement. This
suggests that the criteria are potentially different on each row.
Possibly idlist is used differently on each row.

So for example, I was anticipating that something the following might occur:

| id | idlist | query |
---- -------- --------------------------------------------
3 12 'SELECT * FROM user_directory WHERE user_id = this.idlist'
4 8675309 'SELECT * FROM department_user s WHERE dept_no = this.idlist'

The usage of the idlist column to reference logically different
attributes in other tables is against database normalization. It's
extremely fragile, depends on the application-level logic knowing the
conventions perfectly. Frequently designs like this unravel as more
complex conditions are added.

Also if schema changes occur, like a table or column being renamed, it's
hard to debug when some of the SQL is in your application, and some of
it is in the database. You may not find errors for weeks or months,
because some of these per-row queries are not run regularly.

It's better to keep separate columns for the separate criteria, and use
them consistently. Name the columns for the logical attribute they
reference. If one or the other is irrelevant in a certain case, then
enter NULL.

| id | user_id | dept_no | committee_id | ...etc... |
---- --------- --------- ------------------------------
3 12 NULL NULL
4 NULL 8675309 NULL

This requires that you codify _all_ the possible complex criteria for
your user groups. And the above table does not distinguish between AND
and OR, so if you list non-null values in more than one column, it's up
to the application to figure that out.

Most traditional email distribution lists are effectively "OR" only.
You can list individual recipients, or even additional distribution
lists. But the only way to combine them is inclusion; there's no
feature of making a list like, "everyone in list A except those who are
in list B". If you need that level of control, then perhaps you do need
to do it the way you are doing it.

I do understand that real-life scenarios are complex collections of
exception cases, and do not follow patterns. Designing a database in a
well-normalized way requires some kind of consistency. You're trying to
model an organization in which you cannot assume any patterns.

Anyway, I'm glad you found a solution.

Regards,
Bill K.
Jun 28 '06 #7

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

Similar topics

1
11495
by: Lannsjo | last post by:
I need to change my primary key column type from smallint to int. I have tried: ALTER TABLE livegroup MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT; But get an error message certainly since my id-column is primary key and references other tables as well. How can I come around this problem? Need help /Martin
2
27009
by: adammitchell | last post by:
How can you indicate that a FOREIGN KEY constraint references two columns in two different tables? "SQL Server Books Online" show an example of how to reference two columns in the SAME table: REFERENCES ref_table ) Here is the error and the 'bad' SQL code: Server: Msg 8148, Level 16, State 1, Line 4
8
11603
by: pb648174 | last post by:
I have a single update statement that updates the same column multiple times in the same update statement. Basically i have a column that looks like .1.2.3.4. which are id references that need to be updated when a group of items is copied. I can successfully do this with cursors, but am experimenting with a way to do it with a single update statement. I have verified that each row being returned to the Update statement (in an...
7
1815
by: Sam | last post by:
Hi, I would like a piece of advice. I have 3 foreign keys in a table used as primary keys for this table. Is it useful in that case to have just one identity column that would be used as the unique primary key, thus no need to have 3 primary keys ? Advantages vs Drabacks ? Regards
12
3691
by: Philip Sherman | last post by:
I'm trying to copy production statistics to a test database and can't set the column statistics for a VARGRAPHIC column to match what RUNSTATS generated on production. The reason code and some testing I did indicates that the length of the low2key value is too long. It almost looks like the potential length of data to be stored is being calculated on the length of the hex string; without consideration that two bytes of the string...
6
27286
by: Giacomo | last post by:
Hi, I've the following problem. I must delete a column DEFAULT from a table, but I must do it with a script, independently from the server where it'll be executed. Locally I've tried with: ALTER TABLE . DROP CONSTRAINT GO
5
5568
by: SQLMan_25 | last post by:
Hi All, I am trying to create a user defined function that fetches the price of an item. I have written a scalar function that takes itemid and returns its price. Simple version of tables would be like this: Item( itemid int,--pk price decimal(18,4)
3
4839
by: Shestine | last post by:
I am trying to add a column to a current table, with data in it. I am only learning, and i have no idea how to change this to make it work. Here is the script I have right now it, but what it does is delete the whole table and recreates it, adding in the extra column. I don't want that. I want the data that is currently there to stay there and then add anew column. How do I reword this (If possible) to make it work? if exists (select * from...
275
12488
by: Astley Le Jasper | last post by:
Sorry for the numpty question ... How do you find the reference name of an object? So if i have this bob = modulename.objectname() how do i find that the name is 'bob'
0
9825
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10859
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10260
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7795
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6984
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5653
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4463
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.