DB2 - Multi Fetch Cursor and character position in a string

I have two kinds of exception tables, I need help writing DB2 SQL PL to fetch the store numbers for a product

PRODUCT_STORE is one kind of an exception where only one record is stored for a particular product when all the attributes(Vendor,Retail,Cost) are same for most of the stores, and its STORE_I is given 0, which denotes its a base record and it may contain many store numbers, which have to be accessed from another table (STORE_STATUS).
when any attribute of a product for a particular store changes then that record is store in this table as it is.
prod_i   store_i    vend_i     retl_a    cost_a
--------  ---------   ---------    ------   ---------
123                 0     23047        7.0          5.0
123             107     23047        6.0          5.0
123             251     23047        6.0          5.0
123            1042    23047        6.0          4.0

Open in new window

Now my main challenge is to decode all the store numbers from that base record.

 STORE_STATUS is another kind of an exception table where STATUS_T field is 1000 characters, where each character position denotes the store number if it is active
             ex:     'AA A  AAAA'  ( stores 1,2,4,7,8,,9,10 are active )
prod_i    beg_i     end_i     status_i
--------   --------   -------     ----------
123                1     1000     'AA A  AAAA A AA ..........' (until 1000 characters, only 'A' or ' ')
123          1001     2000     '   AA        A    ...................'
123          2001     3000     '                               .........A'

Open in new window

In above example, I should get an output
prod_i    store_i    vend_i   retl_a   cost_a
--------   ---------    --------  -------   ---------
123              107    20347      6.0           5.0
123              251    20347      6.0           5.0
123              107    20347      6.0           4.0
123                  1    20347      7.0           5.0
123                  2    20347      7.0           5.0
123                  4    20347      7.0           5.0
123                  7    20347      7.0           5.0
123                  8    20347      7.0           5.0
123                  9    20347      7.0           5.0
123                10    20347      7.0           5.0
123             3000    20347      7.0           5.0
all store ids which are active from 1 to 3000 

Open in new window

I should be using a multi fetch cursor to fetch multiple rows for each product and then find out the actual stores for each product and display them with other attirbutes.

Please Help, I am novice to PL/SQL

Who is Participating?
i am not sure i completely understand your case but i think the easiest solution would be:
1) create a table (for examples nums) with a single column (id) and insert the values 1 to 1000 into that table
2) now you can join that table to your statuses table and do the decoding, something like

select t1.*
from store_status t1 join nums t2
  on substr(store_t, t2.id, 1)  = 'A'

this should generate a table with a row for every active store
from here on, all you need is another join to your product table and you are good to go
Kent OlsenData Warehouse Architect / DBACommented:
Hi smarts,

Whoever designed your database with a 1000 character status field that has to be indexed to get a store status might have thought that he was doing a good thing, but that completely negated the power of a relational database.  Is there any way to get the redesigned?

Unless you want to write a stored procedure to "take apart" the string, this seems to be a prime candidate for a recursive solution, which can do that in SQL.

smartsujikiAuthor Commented:
Hi Kdo,

there is a store table already which lists each and every store for a particular product.
The volume of that table is huge and it takes hours to query against that table, so they came up with this new solution.
there are 1000+ products and each product can be active in about 3000 stores.

I don't think that there is any chance of redesign, since the new structure stores 95% less records than the old system
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Kent OlsenData Warehouse Architect / DBACommented:
Hi SS,

It may store 95% less records, but that isn't the only criteria.   :)

In the OP, you show an example where store_i is 1042, and describe status_i as a 1,000 character string indexed by store number.  How do you resolved store 1042 into a string of only 1,000 characters?

smartsujikiAuthor Commented:

They have created two fields (beg_i & end_i).
So, for each product there would be three rows created if there are only 3000 stores

So, there will be three character strings created
beg_i.    end_i.      Store_status
_____.   _____.     ___________
1.              1000.      'AAA A A .....'
1001.        2000.      'AA.         A ..'
2001.        3000.      '.              A.  '
smartsujikiAuthor Commented:
Hello All,

Can anyone give me a link to a sample db2 sql pl program. Whic must include use of cursors and procedure.
My search in google gave me evrything written in COBOL.
I don't use that.
Please help
Kent OlsenData Warehouse Architect / DBACommented:
Hi Smart,

Sorry -- I missed your previous post....

What flavor of DB2 are you using?  There are 3:  DB2 on the AS/400; DB2 for Z/OS (OS390); and UDB/LUW (for Linux, Unix, and Windows).

There are some significant differences in the 3.

have you even looked at my post?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.

All Courses

From novice to tech pro — start learning today.