Results 1 to 3 of 3

Thread: Loop in Select Lookup Statement

  1. #1
    Join Date
    Apr 2005
    Jensen Beach, FL

    Question Loop in Select Lookup Statement

    Hello All,

    Below is a simple Select statement performing a Lookup into a SQL database and returning the columns (associated with the Row) in to Cells on an eForm. The issue I have is there are 42 rows (which go up and down) and do not feel like writing 42 select statements.

    select RiskDescriptor, RiskImpactLowDescriptor, RiskImpactMediumDescriptor, RiskImpactHighDescriptor
    from [Risk Descriptors]
    where [RiskDescriptor ID] in (1)
    order by [RiskDescriptor ID];

    I would like to add a loop, adding 1 to the RiskDescriptor ID and 4 to the Cells. So on second pass in the loop:
    RiskDescriptor ID = 2

    Third pass in the loop:
    RiskDescriptor ID = 3
    and so on.

    The Until portion of the loop can be hardcode (42 in this example) but would rather use an EOL or Query the DB for the total number of RiskDescriptor ID. This way when the DB changes (ID's go up or down) the SQL Statement does not need to be notified.

    It is a JDBC call from within the eForm.

    I would appreciate any help on how to format a loop in a SQL Statement
    Last edited by John772; 04-19-2005 at 09:09 PM.

  2. #2
    Join Date
    Feb 2003
    What language are you using for the eForm? VB, ASP, etc.?

    If at all possible, you want to avoid using a cursor or multiple queries as these degrade performance!! It's usually (not always) best to query for the whole dataset and sort it out in the code.

  3. #3
    Join Date
    Apr 2005
    You could possibley use the [sysindexes] and [sysindexkeys] tables to determine the number of rows present in the index and then use a [while] statement loop on the outside of your code.

    This could get you away from a cursor and the need to use [select distinct..] for a count. Of course, it may not be feasible based on how your indexes are set up.

    At any rate here is an example of what I am attempting to convey;

    Declare @iCount int

    Set @icount = (Select si.[rows] From sysindexkeys As sik
    Inner join sysindexes As si
    On si.[id] = sik.[id]
    And si.indid = sik.indid
    Where object_name(si.[id]) = <your tablename>
    And col_name(sik.[id],sik.colid) = <your columnname>
    And si.indid = 1)

    While (@iCount >= 1) Begin

    ...your code

    Set @iCount = @iCount - 1


Posting Permissions

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