Loop in Select Lookup Statement

[John772]

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];
<<1@Cell104>>
<<2@Cell105>>
<<3@Cell106>>
<<4@Cell107>>


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
<<1@Cell108>>
<<2@Cell109>>
<<3@Cell110>>
<<4@Cell111>>

Third pass in the loop:
RiskDescriptor ID = 3
<<1@Cell112>>
<<2@Cell113>>
<<3@Cell114>>
<<4@Cell115>>
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

[Rawhide]

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.

[Dbu]

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

End
------------------------