Relative SQL newbie here......this is probably easy, but....
Lets say I have a table (MainTable) that stores a list of input table names,
a primary key (PKey), and a field called "Configured " for each one. Each of
these input tables also contain a field called "Configured ", which is set to
true or false in another process based on an OrderNumber. (So an order's
inputs are stored in several input tables, and the MainTable is a summary
table that shows which input tables have been configured for any given
OrderNumber).
What I need to do is open each input table, and look for a record containing
a specific OrderNumber and where Configured=true . If a record is found, I
need to update the Configured field for that table in the MainTable, and
then move on to the next sub-table.
The way I'm doing it now is with simple SQL and loops. Here is the basic
code (ASP):
*************** *************** ***********
OrderNumber = "562613" ' the current order that is being processed
' reset all configured flags
sql = "UPDATE MainTable SET Configured = 0"
conn.execute sql, , &H00000080
' get list of all tablenames
sql = "SELECT InputTableName, PKey FROM MainTable WHERE InputTableName <>
'---'"
set rsTableNames = conn.execute(sq l)
while not rsTableNames.eo f
' test each input table for configured flag
sql = "SELECT Configured FROM " & rsTableNames("I nputTableName")
& _
" WHERE Configured = 1 AND OrderNumber = '" & OrderNumber &
"'"
set rs = conn.execute(sq l)
If Not rs.EOF Then
' update the main table
sql = "UPDATE MainTable SET Configured = 1 WHERE PKey='" &
rsTableNames("P rimaryKey") & "'"
conn.execute sql, , &H00000080
end if
set rs = nothing
rsTableNames.mo venext
wend
*************** *************** ***********
There has to be a faster way.. I think.... maybe something that could be
written as a stored procedure? I use a similar technique in a couple of
other places and it's a bit of a performance hit, especially as the number
of input tables grows.
TIA!
Calan 6 1850
calan (no**@nospam.co m) writes: Relative SQL newbie here......this is probably easy, but....
Lets say I have a table (MainTable) that stores a list of input table names, a primary key (PKey), and a field called "Configured " for each one. Each of these input tables also contain a field called "Configured ", which is set to true or false in another process based on an OrderNumber. (So an order's inputs are stored in several input tables, and the MainTable is a summary table that shows which input tables have been configured for any given OrderNumber).
What I need to do is open each input table, and look for a record containing a specific OrderNumber and where Configured=true . If a record is found, I need to update the Configured field for that table in the MainTable, and then move on to the next sub-table.
What is the purpose of all these input tables?
This is a somewhat unorthodox design. A table with table names to process,
is not the normal SQL idiom, at least not for an order system. (For a
database maintenance job it could be.)
You could certainly improve performace by putting the code into a stored
procedure, because you would be saved all the network roundtrips. But the
procedure should not really have a loop over the input table, rather it
should have an UPDATE statement per table.
Table are very much individual objects in SQL Server. Each object has its
set of statistics and indexes, which can result in two similar queries
on two tables can get very different query plans.
Now, I don't know your tables at all, but it is not uncommon when people
have several tables likes, that these tables should in fact be one
single table with one key added.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
<snip> What is the purpose of all these input tables?
This is basically an engineering configurator for a very complex product.
Each input table holds data for a specific "component" of the product.
Using the analogy of a car, a table called "Interior" may have fields such
as "Seat Color", "Stereo Type", "Number Of Speakers", and maybe tens or
hundreds more... A Suspension table could have "Wheel Size", "Spring Type",
"Load Rating", "Brake Type", etc etc.
If you have 100 components with maybe 30-50 configuration questions for each
one..and then multiple products tied into the same system...... well....
you end up with a massive number of input fields in one table to say the
least... Also, I need the ability to work on individual components with
seperate apps, so the multiple table approach seemd to make sense.
I'm always open for suggestions though :-)
Thanks
calan (no**@nospam.co m) writes: What is the purpose of all these input tables?
This is basically an engineering configurator for a very complex product. Each input table holds data for a specific "component" of the product.
Using the analogy of a car, a table called "Interior" may have fields such as "Seat Color", "Stereo Type", "Number Of Speakers", and maybe tens or hundreds more... A Suspension table could have "Wheel Size", "Spring Type", "Load Rating", "Brake Type", etc etc.
If you have 100 components with maybe 30-50 configuration questions for each one..and then multiple products tied into the same system...... well.... you end up with a massive number of input fields in one table to say the least... Also, I need the ability to work on individual components with seperate apps, so the multiple table approach seemd to make sense.
OK. To be perfectly honest, I still have a kind of feeling that the
arrangement is not the best. But I would have to looking into business
requirements etc to tell for sure. But that would maybe then be
with the placement of all these Congifured flags. Having separate tables
certainly makes sense, since else you would get a very wide table, as
I understand it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
<snip>
So back to the original question, is there an SQL example of this? I'm not
having any luck finding anything on using a subquery in a FROM clause that
returns a list of table names.
I thought something simple like this would work (This assumes that all
tables listed in "MyTables" will have a field called "Configured ".):
SELECT t2.TableName, t1.Configured
FROM (SELECT TableName FROM MyTables AS t2) t1
But it doesn't....
>> If you have 100 components with maybe 30-50 configuration questions for each one..and then multiple products tied into the same system...... well.... you end up with a massive number of input fields [sic] in one table to say the least... <<
Have you thought about a using a parts explosion/tree model? All the
options would be subordinates under a component in a master model that
gets traversed and pruned as decisons are made. You are looking at a
tree with 5000 nodes and you can use the hierarchy to enforce some
dependencies. I.e. If you get a radio, then you have to pick from five
models, if you do not get a radio, we do not show the models.
calan (no**@nospam.co m) writes: So back to the original question, is there an SQL example of this? I'm not having any luck finding anything on using a subquery in a FROM clause that returns a list of table names.
And you will never have. There is no indirection on table names in T-SQL.
In fact there is very little indirection at all in T-SQL. And for a good
reason.
I thought something simple like this would work (This assumes that all tables listed in "MyTables" will have a field called "Configured ".):
SELECT t2.TableName, t1.Configured FROM (SELECT TableName FROM MyTables AS t2) t1
But it doesn't....
Either you loop that table and build the list with dynamic SQL, or
you hard-code each table. I would recommend the latter.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ralph Guzman |
last post by:
I have to do a catalog search through multiple tables and columns for
product model number, description, and name. I realize that doing
pattern matching with multiple LIKE statements is slow so I found that
FULLTEXT searches is a better alternative.
I have added a FULLTEXT index to the tables I'm searching, but I get an
unkown error when I run my query:
SELECT p2c.categories_id, p.products_id, pd.products_name,
p.products_quantity,...
|
by: tom |
last post by:
I am new to SQL administration.
>From a list of IDs that are the primary key in one table (i.e. Customer
Table), I want to make changes in tables that use those IDs as a
foreign key.
Basically I want to say:
If fk_ID is in list then
do these statements to that record
|
by: Tim Pollard |
last post by:
Hi
I'm hoping someone can help me with an access problem I just can't get
my head around. I normally use access as a back end for asp pages,
just to hold data in tables, so queries within access are a mystery to
me, but I can't think of any other way of dealing with the problem.
I have six tables in my db:
tblCompanies (list of companies, primary key CompanyID)
tblOffices (list of office buildings including what company owns/uses
|
by: Geoff Jones |
last post by:
Hiya
I have a DataTable containing thousands of records. Each record has a
primary key field called "ID" and another field called "PRODUCT" I want to
retrieve the rows that satisy the following criteria:
I have a list of about 100 numbers which correspond to the ID field and also
another 40 say numbers corresponding to the numbers in the PRODUCT field. I
want to show the rows that correspond to both these criteria.
|
by: rbg |
last post by:
I did use query plans to find out more. ( Please see the thread BELOW)
I have a question on this, if someone can help me with that it will be
great.
In my SQL query that selects data from table, I have a where clause
which states :
where PermitID like @WorkType
order by WorkStart DESC
| |
by: Lee Brotzman |
last post by:
Hi,
I have a sequence of data tables in an ordered list, like this:
<ol>
<li><table></table></li>
<li><table></table></li>
</ol>
However it renders on the page with the list item number next to the
|
by: john |
last post by:
In my form I have a master table and a details table linked 1xM. I can
search through the whole parent table but I also like to be able to search
through the child table fields to find parent records. Should I design a new
form for this or can I somehow make this work in the same form.
Thanks in advance,
john
|
by: nyathancha |
last post by:
Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
The reason I ask is because in our application, the user can perform x
|
by: Lysander |
last post by:
I have not seen this feature documented before, so I thought I would share it with you, as I will be using it in a later article.
For a combo or list box, the source data is normally a Table/Query. Or it could be a value list, a static list of data.
But what if your information is not held in a table, and it is not a static list.
Examples I have used have been, “next 100 prime numbers after this one”, “Every third Friday from start of...
|
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...
|
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...
| |
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |
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...
| | |