DB2 - Multi Fetch Cursor and character position in a string

Posted on 2011-02-23
Medium Priority
Last Modified: 2012-05-11
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

Question by:smartsujiki
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
LVL 37

Accepted Solution

momi_sabag earned 2000 total points
ID: 34969293
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
LVL 46

Expert Comment

by:Kent Olsen
ID: 34969868
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.


Author Comment

ID: 34976937
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
Stressed Out?

Watch some penguins on the livecam!

LVL 46

Expert Comment

by:Kent Olsen
ID: 34980075
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?


Author Comment

ID: 34981203

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.  '

Author Comment

ID: 34999017
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
LVL 46

Expert Comment

by:Kent Olsen
ID: 34999784
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.

LVL 37

Expert Comment

ID: 34999985
have you even looked at my post?
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35913493
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 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