• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

Query a result that should first be concatenated based on a sku & sequence number

I have an asp script connecting to a db through and ODBC connection.
The actual database type is ProvideX.
The table that I am having the most difficulty contains 4 fields:  Sku,Description,Size,SequenceNumber.

Comma separated example data of two products may be observed below;
ITEM01,This is a test descr,,001
ITEM01,iption for your info,,002
ITEM02,This is another one ,,001

The Description field has a maximum character length of 20 in my example.  As shown, the first row specifies the total size of the complete product description, but does not contain a value in the Description field and always has a SequenceNumber of 000.  Following rows contain the complete description which is broken up in increments of 20 characters of description per row with a SequenceNumber incremented according to the number of rows needed to account for the complete description.

The first, main issue I am having a problem with is, I need sample code that will query / build a single, complete description string which can then be keyword searched & return the complete description.  So for ITEM01, my intent is to use a keyword of "description" successfully & return the complete description of the result(s).

My current query which is far off from satisfactory looks like this;

<form name="frmQuery" action="<%= strURL %>" method="get">
<input name="search" value="<%= strSearch %>">
<input value="Search" type="submit">
If strSearch <> "" Then
<!--       #include file = "db_connect.asp" -->
      ' Build our query based on the input.
      strSQL = "SELECT Sku, SequenceNumber, Description
            & "FROM ItemEndedDescription " _
            & "WHERE Description LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
            & "ORDER BY Sku, SequenceNumber"

I look forward to your response.
  • 2
  • 2
1 Solution

1. Select all rows having ITEM01 (or whatever ID you want) as the Sku, ordered by SequenceNumber. You need to collect these rows in a recordset (presumably your db_connect.asp takes care of this).

2. declare a variable strDescription.

3. Loop through the recordset, building the description...
Do Until rst.EOF
    strDescription = strDescription + rst("Description")


Job done.

Not sure if the syntax for the recordset is right (think I might be confusing my ADO and DAO!) but the idea should work :).

Julian HansenCommented:
Wow - interesting question.

The easiest solutoin is to restructure your database to allow for a single line description or alternatively build a second table with the description in concatenated form.

Here is the problem. In order to get a complete description field you have to join fields from different rows together. Because there can be any number of rows making up the description a single query will not do it.

What I would do is build a second table which has the Item code and description fields. I would then create a process that runs each time new data comes in to do the following

Does the Item ID exist in the new table
    NO: Insert new_table (item_id, descr) values ( ... )
    YES: update new_table set descr = descr + next_descr where item_id = incoming_item_id

You will need to do this for each new record coming in. When done the new_table will contain an Item ID and a complete description on which you can do your search.
I don't know if your database supports Stored Procedures and / or triggers but these would be useful in updating the new_table.

Does this help?
twstdpairsAuthor Commented:
Thank you for reponding solution46 and julianH.

I am considering your ideas as options & will need some time to test.  I anticipate that neither can in whole solve my problem but hope that they can be used as tools.

solution46:  The problem anticipated with your suggestion, is that it appears to only deal with one 'item'.  What I believe I need to do is "build" the description for EVERY item in the database into a recordset / result which itself can be keyword searched / queried (what a waste of server resources - I know).  It sounds like your idea is on the right track & I will play with your sample to see if I can accomplish it.  At the glance, it still looks like I will have problem but won't know until I kick it around a bit.

julianH:  An interesting alternative that I had not thought of.  The problem(s) anticipated with your suggestion is likely due to foundational stuff that I failed to include in my initial post; This is an proprietary Mas90/200 ERP system.  New items are added to the proprietary ProvideX db using a GUI interface / not the web app that I'm working on.  The database is jacked / there is probably not much I can do with it unless I do as you suggested to a table in an Access db or something.  But I still run into a problem of dynamically adding new inventory item to the new table when the addition is coordinated through the chitty GUI.

Make sense?

I have decided to present some additional information that at first, thought would just further complicate the matter but know see that it is possible to help.

There exists another table, the InventoryMasterfile.  This is actually the main inventory item table where, the table in my question above is the ended description table.  The InventoryMastefile holds records based on a unique sku.  If the inventory item has a description less than 50 characters, then an ItemEndedDescription table entry is not created.  If the item description is greater than 50 characters, I believe the first 50 characters of description are entered into the InventoryMasterfile table, & re entered in the ItemEndedDescription according to the format in my original post / question.

What I am thinking is, that the Sku field (being unique) in the InventoryMasterfile field can be the index to identify which items in ItemExtendedDescription get their descriptions "built".

Anyhow I'll post my progress hopefully soon.
twstdpairsAuthor Commented:

When I stat that "I believe the first 50 characters of description are entered into the InventoryMasterfile table, & re entered in the ItemEndedDescription according to the format in my original post / question." I actually mean that the data is INSERTED into the tables at runtime when the user is entering them through the proprietary GUI.

Also, through a "Y" or "N" value, the InventoryMasterfile does flag whether or not the inventory item description is larger than 50 characters / whether or not data is entered in the ItemEndedDescription table for item.

try the following (more pseudo code than real code so you'll have to fill in the blanks!!!

dim rstMaster as recordset, rstDescription as recordset   'flavour is up to you.

dim strSku as string, strSkuNext as string
dim strDescription as string

'populate the recordsets accordingly.

'add a field 'Description' to rstMaster. This is no problem to do in ADO; if you're using DAO I'm not so sure.
'Either way, if this poses a problem we'll address it at the time.

strSku = ""
strSkuNext = ""

do until rstMaster.EOF
    strDescription = ""
    strSkuNext = rstMaster!Sku

    if rstMaster.NeedDescription = 'Y' Then        'change as appropriate!
        strDescription = rstMaster!Description      'couldn't work out from your post if this is necessary or not :)
        rstDescription.FindFirst (sku= strSku)      'syntax of this line depends on the type of recordset you have.
        do while rstDescription!Sku = strSku
            strDescription = strDescription & rstDescription!Description
        strDescription = rstMaster!Description
    end if

    'this is DAO style code - change as appropriate
    rst!Description = strDescription



As you pointed out, this is a REALLY inefficient way of doing this but it will allow you moderately easy access to all the descriptions.

If there are any problems with it, post them up and I'll take a look.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now