Difficult query: return recordset from concatenated strings?

Hi All,

I have what seems to me to be a difficult query request for a database
I've inherited.

I have a table that has a varchar(2000) column that is used to store
system and user messages from an on-line ordering system.

For some reason (I have no idea why), when the original database was
being designed no thought was given to putting these messages in
another table, one row per message, and I've now been asked to provide
some stats on the contents of this field across the recordset.

A pseudo example of the table would be:

custrep, orderid, orderdate, comments

1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer asked
for a brown model
2, 10002, 2004-04-12, :Comment 3:Comment 4:
1, 10003, 2004-04-12, :Comment 2:Comment 8:
2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:
2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled order

So, what I've been asked to provide is something like this:

orderdate, custrep, syscomment, countofsyscomments
2004-04-12, 1, Comment 1, 1
2004-04-12, 1, Comment 2, 2
2004-04-12, 1, Comment 3, 1
2004-04-12, 1, Comment 8, 1
2004-04-12, 2, Comment 1, 1
2004-04-12, 2, Comment 3, 1
2004-04-12, 2, Comment 4, 2
2004-04-12, 2, Comment 6, 2
2004-04-12, 2, Comment 7, 1

I have a table in which each of the system comments are defined.
Anything else appearing in the column is treated as a user comment.

Does anyone have any thoughts on how this could be achieved? The end
result will end up in an SQL Server 2000 stored procedure which will
be called from an ASP page to provide order taking stats.

Any help will be humbly and immensely appreciated!

Much warmth,

MurrayAssuming your tables look something like this:

CREATE TABLE Orders (custrep INTEGER NOT NULL, orderid INTEGER, orderdate
DATETIME NOT NULL, comment1 VARCHAR(2000) NULL, comment2 VARCHAR(2000) NULL,
comment3 VARCHAR(2000) NULL, comment4 VARCHAR(2000) NULL /*, PRIMARY KEY ?
*/)

CREATE TABLE SystemComments (comment VARCHAR(2000) PRIMARY KEY)

Try this:

SELECT O.orderdate, O.custrep, O.comment,
COUNT(S.comment) AS count_of_syscomments
FROM
(SELECT orderdate, custrep, comment1
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment2
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment3
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment4
FROM Orders)
AS O (orderdate, custrep, comment)
LEFT JOIN SystemComments AS S
ON O.comment = S.comment
GROUP BY O.orderdate, O.custrep, O.comment

--
David Portas
SQL Server MVP
--|||On Fri, 14 May 2004 15:51:25 +0100, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>Assuming your tables look something like this:
>CREATE TABLE Orders (custrep INTEGER NOT NULL, orderid INTEGER, orderdate
>DATETIME NOT NULL, comment1 VARCHAR(2000) NULL, comment2 VARCHAR(2000) NULL,
>comment3 VARCHAR(2000) NULL, comment4 VARCHAR(2000) NULL /*, PRIMARY KEY ?
>*/)
>CREATE TABLE SystemComments (comment VARCHAR(2000) PRIMARY KEY)
>Try this:
>SELECT O.orderdate, O.custrep, O.comment,
> COUNT(S.comment) AS count_of_syscomments
> FROM
> (SELECT orderdate, custrep, comment1
> FROM Orders
> UNION ALL
> SELECT orderdate, custrep, comment2
> FROM Orders
> UNION ALL
> SELECT orderdate, custrep, comment3
> FROM Orders
> UNION ALL
> SELECT orderdate, custrep, comment4
> FROM Orders)
> AS O (orderdate, custrep, comment)
> LEFT JOIN SystemComments AS S
> ON O.comment = S.comment
> GROUP BY O.orderdate, O.custrep, O.comment

Hi David,

Thanks for the suggestion, unfortunately that's not how the table is
defined.

Sorry, I should have posted a pseudo create table statement as well.

It looks something like:

CREATE TABLE OrderComments (custrep INTEGER NOT NULL, orderid INTEGER,
orderdate DATETIME NOT NULL, comments VARCHAR(2000))

The create table statement you have for SystemComments is fine.

So, in the OrderComments table, the comments column might contain:

':Comment 1:Comment 2: Comment 8:Comment whatever'

So, each of the system and user generated comments for a particular
order are concatenated into a string and are put into a single column
(comments column) for that order.

Sorry for the confusion...

Much warmth,

Murray|||>So, in the OrderComments table, the comments column might contain:
>':Comment 1:Comment 2: Comment 8:Comment whatever'
>So, each of the system and user generated comments for a particular
>order are concatenated into a string and are put into a single column
>(comments column) for that order.
>Sorry for the confusion...
>Much warmth,
>Murray

Can I assume that these are free form and free for all type of
comments and not standardized ?

Is there some kind of unique seperator between comments ?

Been there, done this real recently and it wasn't pretty at all.

Randy
http://members.aol.com/rsmeiner|||You can try this:

SELECT O.orderdate, O.custrep, O.comments,
COALESCE(SUM((LEN(O.comments)-LEN(REPLACE(O.comments,S.comment,'')))
/LEN(S.comment)),0)
FROM OrderComments AS O
LEFT JOIN SystemComments AS S
ON O.comments LIKE '%'+S.comment+'%'
GROUP BY O.orderdate, O.custrep, O.comments

Don't expect great performance though!

--
David Portas
SQL Server MVP
--|||On 14 May 2004 15:20:02 GMT, rsmeiner@.aol.comcrap (RSMEINER) wrote:

[snip]

>>
>Can I assume that these are free form and free for all type of
>comments and not standardized ?
>Is there some kind of unique seperator between comments ?
>Been there, done this real recently and it wasn't pretty at all.

Hi Randy,

Pretty much, except that I have a reference table of the exact wording
of each of the system comments that might be found in the concatenated
value in the comments column.

The comments are delimited by a colon character, but I can't assume
that user comments, which get concatenated in the same field, will
always be lacking colon characters.

The only thing I can think to do is create a temp table in a stored
procedure and do multiple update...select statements to populate the
temp table, using the values in the predfined comments table.

I hear you that it isn't pretty.

Much warth,

Murray|||>Hi Randy,
>Pretty much, except that I have a reference table of the exact wording
>of each of the system comments that might be found in the concatenated
>value in the comments column.
>The comments are delimited by a colon character, but I can't assume
>that user comments, which get concatenated in the same field, will
>always be lacking colon characters.
>The only thing I can think to do is create a temp table in a stored
>procedure and do multiple update...select statements to populate the
>temp table, using the values in the predfined comments table.
>I hear you that it isn't pretty.
>Much warth,
>Murray

Since you have a table of the system comments, it makes it
much easier. I'm thinking on this.

How big are these tables ?

Randy
http://members.aol.com/rsmeiner|||Did you try my second solution?

--
David Portas
SQL Server MVP
--

different image source for online and PDF reports?

In my current job, I've inherited a website and some ReportViewer
reports, and I've figured out how to poke around and make some changes
requested by my employer, but there's still a lot that I don't
understand. Any input would be appreciated.
Here's the current problem: the reports that we make available on our
website are mostly made up of images. Up until now, we've been saving
the images at the size that they need to be when the reports are
viewed online at 100%. Of course, this means that when exporting the
report to PDF, or printing it, the quality is unacceptable. I've tried
saving larger images and scaling them proportionately in the reports,
which results in beautiful PDFs and illegible online reports due to
resampling. Naturally, and reasonably, my employer wants our reports
to look good online AND when they're printed. I've looked around
online and found that other people have wrestled with this problem,
with no clear solutions.
The one idea that I've come up with is to use different images for the
online and printed versions (so the online report would use /preview/
image01.png and the PDF would use /print/image01.png or something). Do
you think this is possible? Any idea where I would start?
Thanks in advance for any advice.
SarahOn Jun 4, 2:37 pm, "s. o." <sarah.oh.li...@.gmail.com> wrote:
> In my current job, I've inherited a website and some ReportViewer
> reports, and I've figured out how to poke around and make some changes
> requested by my employer, but there's still a lot that I don't
> understand. Any input would be appreciated.
> Here's the current problem: the reports that we make available on our
> website are mostly made up of images. Up until now, we've been saving
> the images at the size that they need to be when the reports are
> viewed online at 100%. Of course, this means that when exporting the
> report to PDF, or printing it, the quality is unacceptable. I've tried
> saving larger images and scaling them proportionately in the reports,
> which results in beautiful PDFs and illegible online reports due to
> resampling. Naturally, and reasonably, my employer wants our reports
> to look good online AND when they're printed. I've looked around
> online and found that other people have wrestled with this problem,
> with no clear solutions.
> The one idea that I've come up with is to use different images for the
> online and printed versions (so the online report would use /preview/
> image01.png and the PDF would use /print/image01.png or something). Do
> you think this is possible? Any idea where I would start?
> Thanks in advance for any advice.
> Sarah
That sounds like the best option. You should have one report that can
be viewed and then when the user goes to export it, change the report
behind the scenes in the application to point to the smaller imaged
report. This should be fairly straight forward in terms of pointing to
one report in a report viewer and then pointing to another report once
the user selects something like a print preview aspx page or
something. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant