DB2 - Multi Fetch Cursor and character position in a string

Posted on 2011-02-23
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
  • 3
  • 3
  • 2
  • +1
LVL 37

Accepted Solution

momi_sabag earned 500 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,, 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 45

Expert Comment

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
LVL 45

Expert Comment

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?

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).


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 45

Expert Comment

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 142

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

10 Experts available now in Live!

Get 1:1 Help Now