Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    82

    Unanswered: Db2 Sql Procedure String tokenizer and loop

    Hi,
    I am trying to write an SQL procedure in DB2 9.7 on linux.

    The scenario is the following . there is this table with these 2 columns and account. Both are varchars.

    The group is has multiple accounts associated to it and the accounts are ID's of the account seperated by commas so a dummy row is

    Norman Group : 1334,1335,17798

    I need to call another SP that maps these account to a group in our security framework.

    What i need is a way to seperate the accounts and call a procedure recursively till all accounts are mapped

    call add('Norman Group',<recursively add accounts>)

    This is just part of the implementation so a SP is must.

    Thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    First question I had was why you need procedures?
    I felt that your requirements might be satisfied by a user-defined-function
    (or if your requirements were very complex than I imagined, it might be required some UDF functions).


    If someone answered just an answer or some suggestions,
    I want to see that dialogues.

    But, if no usefull responses(for me) were not seen,
    I might want to consider the ansewers for this issue, by clarifying your requirements some more concretely.

    For example,
    (1) How many tables related to your issue and what were the names of the tables?
    (2) Some sample data of the tables
    (it doesn't need real data, but data in each tables need to have rational relations of each others)


    I didn't understand
    ...
    The group is has multiple accounts associated to it and the accounts are ID's of the account seperated by commas so a dummy row is

    Norman Group : 1334,1335,17798
    In what table(s) these data were?
    Why dummy row?
    Why you can't show some real data?
    Why did you showed a dummy row?
    (Did you thought that only one data might be enough to explain your requiremens?)
    What data in other tables?
    How relate with rows in each tables?
    What results from your sample data did you want?
    ...

  3. #3
    Join Date
    Jan 2012
    Posts
    82
    We are using an SP because the previous implementation of this functionality was done using SP and I have been asked to extend it.

    The table in question is a landing table and a front end dumps this data into the landing table. Where this sp will be called and process the data and add it into our security layer. SP are already in place which add the data into the tables.

    I showed the dummy data because actual data is confidential and can't be shared, but the dummy data was suppose to show what I intend to do which it does.

    A group will have multiple accounts related to it. The application using Dimension tables (its a star schema) to pick up the relationship between group and accounts and then assign certain accounts to a group


    Previously the landing table had a fixed 4 account allocation as in instead of csv there were 4 columns for accounts and the SP was had coded only to accept 4 accounts per group

    but now we want to change that and allow dynamic allocation of accounts to group thus the csv column and need for a recursive loop

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •