Results 1 to 9 of 9

Thread: Iterator in SQL

  1. #1
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Unanswered: Iterator in SQL

    Hi
    I have a situation here, and I want to write a clean SQL query, if possible just one SQL.

    I have categoryid, say 95000.
    Then I have sub-categoryid for each categoryid, starting from 95000 ... 95005 ...

    With each sub-categoryid, I have a active/inactive flag.

    I want to write a query which chooses the first active sub-categoryid for a categoryid.

    For example,


    95000 inactive
    95001 inactive
    95002 active
    95003 inactive
    95004 active
    95005 active

    should return 95002, instead of 95000 or 95001.

    Immediate help required.

    Thanx and Regards
    Aruneesh

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Iterator in SQL

    Originally posted by aruneeshsalhotr
    Hi
    I have a situation here, and I want to write a clean SQL query, if possible just one SQL.

    I have categoryid, say 95000.
    Then I have sub-categoryid for each categoryid, starting from 95000 ... 95005 ...

    With each sub-categoryid, I have a active/inactive flag.

    I want to write a query which chooses the first active sub-categoryid for a categoryid.

    For example,


    95000 inactive
    95001 inactive
    95002 active
    95003 inactive
    95004 active
    95005 active

    should return 95002, instead of 95000 or 95001.

    Immediate help required.

    Thanx and Regards
    Aruneesh
    If I have understood:

    select category_id, min(subcategory_id) as first_active_subcat_id
    from subcats
    where flag = 'active'
    group by category_id;

    However, that will not return any information for categories where ALL subcategories are inactive. That would require an outer join:

    select cat.category_id, min(sub.subcategory_id) as first_active_subcat_id
    from categories cat, subcats sub
    where cat.category_id = sub.category_id (+)
    and sub.flag = 'active' (+)
    group by cat.category_id;

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Aruneesh,

    This should do what you want....

    select min (category_id)
    from fred
    where category_id-mod( category_id,5 ) = 95000 and
    status = 'active'

    The above is virtually un-optimisable for Oracle, you could start adding some additional where clauses (category between 95000 and 95004 for instance) but I would strongly suggest you reconsider your table structure if possible.

    Ideally two columns, one for category and one for subcategory such....

    category subcategory
    95000 0
    95000 1
    95000 2

    All activity on that table then becomes simple - both for you and for Oracle.

    Hth
    Bill

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    this is off the top of my head and there is a better way of doing this.


    select cat_id, min(sub_cat_id), flag
    from table
    where flag = 'active'
    and cat_id = '95000'
    group by cat_id, flag;


    obviously will not work if you are characters in that field.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    doh!
    they beat me to it!!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jul 2003
    Location
    US
    Posts
    314
    Hi Bill
    The problem at hand is that the design is slightly deep rooted and not much can be done, to get it straightened up.
    You know how it is, when something has been stable/efficient and working for 2 years. Changing the model, might be very difficult to convince.

    What I want is that I want to go through the starting index of 95000 onwards till the time all the sub-category ids have been dealt with. (Those ones which have first three digits at 950)...
    so that would be 95000 .... 950XX.

    I dont have parent child relationship in this table.
    So the query you wrote, would work fine in that scenario, but in my case, it just wont.

    Any ideas on iterations.
    I also need to have boundary check in here, as if I have just sub-cat which is inactive ...
    Thanx and Regards
    Aruneesh

    Originally posted by billm
    Hi Aruneesh,

    This should do what you want....

    select min (category_id)
    from fred
    where category_id-mod( category_id,5 ) = 95000 and
    status = 'active'

    The above is virtually un-optimisable for Oracle, you could start adding some additional where clauses (category between 95000 and 95004 for instance) but I would strongly suggest you reconsider your table structure if possible.

    Ideally two columns, one for category and one for subcategory such....

    category subcategory
    95000 0
    95000 1
    95000 2

    All activity on that table then becomes simple - both for you and for Oracle.

    Hth
    Bill

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    I'm not quite sure I follow ... am I right in thinking that your table consists of the one column which acts as the categoryid and subcategoryid together and another column which is the active/inactive status?

    To get a range of first active subcategory for a range of categories you could....

    select category_id-mod( category_id,5 ), min(category_id)
    from fred
    where status = 'active'
    group by category_id-mod( category_id,5 )

    But I'm not quite sure I fully understand the question to be honest :-(

    Hth
    Bill

  8. #8
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Sorry to post this thread

    I guess I was excited about the weekend, and my mind really didnt work at all, when I needed such a simple query like

    select min(subcategoryid) from table1 where flag='active' and subcategoryid like '950%';


    But thanx for your help Bill and Andrews.

    Thanx and Regards
    Aruneesh

  9. #9
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    :-)

    Incidentally, you can think about correcting the design problem on that table by creating what would be the parent category table, adding a category column to the subcategory table along with a trigger to insert/delete parent categories from the main table as necessary.

    This is completely transparent to existing applications.

    Over time you should be able to enforce a traditional foreign key relationship on it and slowly migrate existing queries to use straightforward SQL behaviour.

    Hth
    Bill

Posting Permissions

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