Hi All,
Here's a challenge.
If there is a 'one word' answer to this, i.e. the name of a built in
SQL Server function I can use, that would be great! However...
I have a standard 1:m relationship, e.g. tblHeader and tblDetail.
I want to create a view of records from 'tblHeader' and within that
view have a column (called e.g. DetailTypes) that provides a single
comma separated varchar of values from a varchar field (called e.g.
DetailType) that appears in related records in the 'tblDetail' table.
(hope you understood that :)
e.g. the contents of my 'tblDetail' table could be....
Detail ID | Header ID | DetailType
-----------------------------------
1 | 1 | A
2 | 1 | A
3 | 1 | B
4 | 2 | A
5 | 2 | C
6 | 3 | B
Therefore, the view I want to create should return:
Header ID | DetailTypes
-------------------------------
1 | 2A, 1B
2 | 1A, 1C
3 | 1B
i.e. the first row can be read as "Header 1 has 2 'A' detail records
and 1 'B' detail record."
I have created a view e.g. 'vqryHeaders' which calls a user defined
function that takes the HeaderID and opens a cursor on another view,
e.g. 'vqryDetailGrou ped'. The other view groups the records in
tblDetail so that I can get a count of each DetailType for each Header
ID. The cursor then loops through the returned records concatenating
the count and detail type into a comma separated string (as shown
above).
However, when I run this across 20k records it is soooo sloooooww. I
have indexes on the relationship fields and I am using realistically
sized varchars - neither made any difference in speed. It is
definately the function that I wrote that slows it down as the view is
lightning fast when I remove my function call.
I can supply source code if necessary, but I think that this is a
kind-of generic problem so I don't see the point - yet.
I really hope you can help.
Regards,
Jezz 11 14873
"Jeremy Pridmore" <go*********@pr idmorej.freeser ve.co.uk> wrote in message
news:9b******** *************** ***@posting.goo gle.com... Hi All,
Here's a challenge.
If there is a 'one word' answer to this, i.e. the name of a built in SQL Server function I can use, that would be great! However...
I have a standard 1:m relationship, e.g. tblHeader and tblDetail.
I want to create a view of records from 'tblHeader' and within that view have a column (called e.g. DetailTypes) that provides a single comma separated varchar of values from a varchar field (called e.g. DetailType) that appears in related records in the 'tblDetail' table. (hope you understood that :)
e.g. the contents of my 'tblDetail' table could be....
Detail ID | Header ID | DetailType ----------------------------------- 1 | 1 | A 2 | 1 | A 3 | 1 | B 4 | 2 | A 5 | 2 | C 6 | 3 | B
Therefore, the view I want to create should return:
Header ID | DetailTypes ------------------------------- 1 | 2A, 1B 2 | 1A, 1C 3 | 1B
i.e. the first row can be read as "Header 1 has 2 'A' detail records and 1 'B' detail record."
I have created a view e.g. 'vqryHeaders' which calls a user defined function that takes the HeaderID and opens a cursor on another view, e.g. 'vqryDetailGrou ped'. The other view groups the records in tblDetail so that I can get a count of each DetailType for each Header ID. The cursor then loops through the returned records concatenating the count and detail type into a comma separated string (as shown above).
However, when I run this across 20k records it is soooo sloooooww. I have indexes on the relationship fields and I am using realistically sized varchars - neither made any difference in speed. It is definately the function that I wrote that slows it down as the view is lightning fast when I remove my function call.
I can supply source code if necessary, but I think that this is a kind-of generic problem so I don't see the point - yet.
I really hope you can help.
Regards,
Jezz
The fastest (and easiest) way would probably be in a front end application,
as this sort of thing is very awkward in pure SQL. You might find these
links useful for more information: http://www.aspfaq.com/show.asp?id=2279 http://tinyurl.com/bib2
Simon
The fastest way I know is to use the TSQL extension to the update
statement.
UPDATE table
SET @var = col = expression which manipulates col
WHERE condition
I won't go into the details because it's rather lengthy and my wife is
waiting for me... I've included sample code and the output. -- Louis
-----------------------------------------------------------------------------
create table #H (headerID smallint)
insert into #H values(1)
insert into #H values(2)
insert into #H values(3)
create table #D (detailID smallint,header ID smallint,detail Type
char(1))
insert into #D values(1,1,'A')
insert into #D values(2,1,'A')
insert into #D values(3,1,'B')
insert into #D values(4,2,'A')
insert into #D values(5,2,'C')
insert into #D values(6,3,'B')
select
d.headerid,
cast(count(d.de tailtype) as varchar)+cast(d etailtype as varchar) as
detailtypes,
identity(int,1, 1) as i
into #T
from #H as h
join #D as d
on h.headerid=d.he aderid
group by d.headerid,d.de tailtype
order by d.headerid,d.de tailtype
select headerid,identi ty(int,1,1) as i
into #Cursor
from #T
group by headerid order by headerid
declare @i int, @text varchar(8000)
set @i=1
while exists(select * from #cursor where i=@i) begin
set @text=''
update #T
set @text=detailtyp es=@text +',' + detailtypes
where headerid=(selec t headerid from #cursor where i=@i)
set @i=@i+1
end
select a.headerid,righ t(detailtypes,l en(detailtypes)-1) as detailtypes
from #T as a
join
(
select headerid,max(i) as i from #T group by headerid
) as b
on a.i=b.i
OUTPUT
headerid detailtypes
-------- ------------------------------------------------------------
1 2A,1B
2 1A,1C
3 1B
>> I think that this is a kind-of generic problem .. <<
It is generic in the sense that newbies who don't understand what
First Normal Form (1NF) mean keep posting for a way to do front end
displays and reports in the SQL backend. This comes from not having
worked with a client/server architecture before, so you want to see
everything for the app written in one monolithic program.
The only safe way is to use a cursor and reutrn to slow, procedural
programming instead of declarative SQL programming. Any kludge you
use will not port to another SQL product, will run like glue and will
be almost unpredictable. jo*******@north face.edu (--CELKO--) wrote in message news:<a2******* *************** ****@posting.go ogle.com>... I think that this is a kind-of generic problem .. <<
It is generic in the sense that newbies who don't understand what First Normal Form (1NF) mean keep posting for a way to do front end displays and reports in the SQL backend. This comes from not having worked with a client/server architecture before, so you want to see everything for the app written in one monolithic program.
The only safe way is to use a cursor and reutrn to slow, procedural programming instead of declarative SQL programming. Any kludge you use will not port to another SQL product, will run like glue and will be almost unpredictable.
Thanks for confirming that the world still has some complete w@nkers
in it.
1. Don't make assumptions. I'm not a newbie - as my post said I
already have an answer, albeit a slow one.
2. Don't make assumptions. I have worked on several successful C/S
apps.
3. Don't make assumptions. I don't necessarily want my app complied
into one large monolithic program. Also, If it is possible to create
a reusable function into which you can pass several parameters and get
a concatenated string, that procedure would be classed as 'generic'.
4. Don't make assumptions. I don't want it to port to any other
platform. Why do you think I posted in comp.databases. ms-sqlserver
and not in comp.databases?
I already have code that runs like glue, which is why I posted. I
would expect any *intelligent* person to give a reasonable response
(such as Louis) - not a rant.
Although an appropriate procedure can be created in 'front-end' code -
in my case i'm using vbscript (more fuel?), I would expect that
varchar 'lookup' and manipulation in the database would be quicker
than having to retrieve all the data into the front end and process it
there.
Oh, and lets not forget the most inportant thing - the reason why I
want to do this! It will give me a searchable field in a view to
which I can apply my 'generic' filtering procedures.
So, how is your snow coming along?
"You're not much use to me alive are you" CELKO?
- Bricktop.
La, la, la - I'm ignoring you now.
What was that? Whatever. lo************@ hotmail.com (louis nguyen) wrote in message news:<b0******* *************** ***@posting.goo gle.com>... The fastest way I know is to use the TSQL extension to the update statement. UPDATE table SET @var = col = expression which manipulates col WHERE condition
I won't go into the details because it's rather lengthy and my wife is waiting for me... I've included sample code and the output. -- Louis
<snip>
Thanks Louis - you reminded me about temporary tables. That will go
along way in helping me - my problem probably lays with the fact that
I want to return this concatenated field in a view - not in a stored
proc, so the only way i can think of doing it is to use a function
that it aliased and pass in the primary key, select the records i need
in the function and then concatenate them.
Thanks again,
Jeremy
Jeremy Pridmore (go*********@pr idmorej.freeser ve.co.uk) writes: lo************@ hotmail.com (louis nguyen) wrote in message
news:<b0******* *************** ***@posting.goo gle.com>... The fastest way I know is to use the TSQL extension to the update statement. UPDATE table SET @var = col = expression which manipulates col WHERE condition
I won't go into the details because it's rather lengthy and my wife is waiting for me... I've included sample code and the output. -- Louis <snip>
Thanks Louis - you reminded me about temporary tables. That will go along way in helping me - my problem probably lays with the fact that I want to return this concatenated field in a view - not in a stored proc, so the only way i can think of doing it is to use a function that it aliased and pass in the primary key, select the records i need in the function and then concatenate them.
Beware that any "fast" solution to your problem will rely on
undocumented and undefined behaviour. The only safe way to this
in SQL is to it with a cursor or some other iterative method.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
>> The fastest way I know is to use the TSQL extension to the update statement. UPDATE table SET @var = col = expression which manipulates col WHERE condition
Erland:Beware that any "fast" solution to your problem will rely on undocumented and undefined behaviour. The only safe way to this in SQL is to it with a cursor or some other iterative method.
Erland - are you referring to the code above here? What do you mean
exactly? Is there some sneakiness of undocumented goodness you are
keeping from us?
I'll admit the code I posted is not standard sql. The extension to
the UPDATE statement is written about in Inside SQL Server 7.0 in the
brain teasers section. It was used to calculate running totals.
WangKhar (Wa******@yahoo .com) writes: Erland - are you referring to the code above here? What do you mean exactly? Is there some sneakiness of undocumented goodness you are keeping from us?
No, the problem is that you have no guarantee that the code will always
produce the expected result. A change of query plan or whatever. Could
break with the next service pack.
Here is a KB article on a similar trick. Pay particular attention to
first paragraph under CAUSE: http://support.microsoft.com/default.aspx?scid=287515.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. 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: Edward |
last post by:
SQL Server 7.0
If I run the following in Query Analyzer I get no records returned:
exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176, 183'
If, however, I run either :
exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176'
|
by: Neil |
last post by:
I have a very puzzling situation with a database. It's an Access 2000 mdb
with a SQL 7 back end, with forms bound using ODBC linked tables. At our
remote location (accessed via a T1 line) the time it took to go to a record
was very slow. The go to mechanism was a box that the user typed the index
value into a combo box, with very simple code attached:
with me.RecordsetClone
.FindFirst " = " & me.cboGoTo
If Not .NoMatch Then
Me.Bookmark...
|
by: AK |
last post by:
Once upon a time there was a table:
CREATE TABLE VENDOR(VENDOR_ID INT, NAME VARCHAR(50), STATE CHAR(2))@
in a while the developers realized that a vendor may be present in
xseveral states, so the structure was changed to
CREATE TABLE VENDOR(VENDOR_ID INT, NAME VARCHAR(50), STATE_LIST
VARCHAR(150))@
|
by: Martin Evans |
last post by:
Hi,
I'm getting:
DBD::DB2::db do failed: SQL0440N No
authorized routine named "CONCAT" of type "FUNCTION" having compatible
arguments was found. SQLSTATE=42884
for some SQL like this:
|
by: wgblackmon |
last post by:
I'm currently running the following statement that is used in a Crystal
Report. Basically, a record is returned when the T_PAYMENT.amount
has a record in the database based on the value of the T_MULTILIST.code
field. Currently, if there is no record returned, there is no listing
in the report for the given T_MULTILIST.code.
The user now wants a record to be displayed on the report when there is
no record in the database - she wants it...
| |
by: Cindy |
last post by:
I'm trying to use the NEWID function in dynamic SQL and get an error
message Incorrect syntax near the keyword 'ORDER'. Looks like I can't
do an insert with an Order by clause.
Here's the code:
SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,
Client_ID, SelectDate, SelectType,RecordChosen)'
SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + '
Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen...
|
by: Bart op de grote markt |
last post by:
Hello,
I have a very simple problem which I will illustrate with an example:
I have the following records in my table:
A 1 C
A 2 C
A 3 C
B 8 K
B 9 K
|
by: waqasahmed996 |
last post by:
hi to all
i have three fields of name in database named as fnam,mname,lname. fname and lname is mandatory field and mname is optional.
i want to make a search query on name
mysql_query("SELECT * FROM abc where CONCAT(fname,' ',mname,' ',lname) like'" . $q . "%' order by id");
in records where mname is not empty then above code is working properly but records in which mname is empty then i have to enter double space between fname and...
|
by: ramprakashjava |
last post by:
hi,
i hav "java.lang.NullPointerException" error while Deleting table records using checkbox in jsp here i enclosed files help quickly plzzz..
Regards
Ramprakash
/*Adminpage.jsp*/
<%@ page language="java" import="java.sql.*"%>
<html>
<head>...
|
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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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: 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();...
|
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: 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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |