Solved

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

Posted on 2004-09-15
7
435 Views
Last Modified: 2008-03-17
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,,50,000
ITEM01,This is a test descr,,001
ITEM01,iption for your info,,002
ITEM01,rmation~;~,003
ITEM02,,26,000
ITEM02,This is another one ,,001
ITEM02,FYI~;~,,002

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">
</form>
<%
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.
0
Comment
Question by:twstdpairs
  • 2
  • 2
7 Comments
 
LVL 9

Expert Comment

by:solution46
ID: 12062200
hmm...

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")
    rst.movenext

Loop

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 :).

s46
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 12063028
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?
0
 
LVL 1

Author Comment

by:twstdpairs
ID: 12066194
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.
0
 
LVL 1

Author Comment

by:twstdpairs
ID: 12066430
P.S.

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.
0
 
LVL 9

Accepted Solution

by:
solution46 earned 250 total points
ID: 12067656
twstdpairs,

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
            rstDescription.MoveNext
        loop
    else
        strDescription = rstMaster!Description
    end if

    'this is DAO style code - change as appropriate
    rst.Edit
    rst!Description = strDescription
    esr.Update

    rstMaster.movenext

loop


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.

s46.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now