looping through recordset

hello,
i have a select query that returns multiple rows (within a cursor). How do i loop through the rows to process it (in a stored proc)? I donot want to use nested cursors. a code sample is requested.
thanks!Usually a cursor is created to process multiple rows (unless you developed a passion for cursors and would like to create one for 1 row).

Have you looked at FETCH NEXT?|||Well, the situation is somethin like this. I am using a cursor that returns multiple rows, within the while @.@.FETCH_STATUS = 0, I have another sql select that returns multiple rows for every row the cursor is processing. I would want to process every row returned in the inner select query. Do I have an option other than using nested cursors? Any help is appreciated.|||If you post the code maybe we can get rid of the cursor.

Loooping through SELECT values in SQL

Hi,

Environment - VB.NET, ASP.NET, SQL Server 2000.

In a SQL User-Defined Function, I am selecting a row which returns multiple values. I need to construct one single string out of those returned values. To do that, I am using CUROSR.

Now, CURSOR is expensive operation. If there are 1000 users at a time, it will consume lot of resources.

Is there a way, I can construct this String without using CURSORs??

Please advice. Thanks

PankajYou should be able to do something like this to concatenate the columns together:

DECLARE @.myResult VARCHAR(8000)
SET @.myResult = ''
SELECT
 @.myResult = @.myResult + myColumn1 + myColumn2 + myColumn3 + myColumn4
FROM
 myTable

Terri|||my mistake, the multiple values selected are from the same column (multiple rows)|||That's OK. you can still use the same method:

DECLARE @.myResult VARCHAR(8000)
SET @.myResult = ''
SELECT
 @.myResult = @.myResult + myColumn1
FROM
 myTable

Terri|||Even though the described SQL works this not a supported T-SQL approach. As per the documentation deom BOL on Select statement :
:: If the SELECT statement returns more than one value, the variable is assigned the last value returned.::

And such an approach is dangerous and can be removed in future builds or service packs. Donot rely on them.

Check out a thread posted by Umachander (MVP, SQL Server) on the same at the public.sqlserver.programmming newsgroup. http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e7dcU%23jiAHA.2088%40tkmsftngp03

There are many other approaches to the same problem. Some of the solutions have been outlined at : http://www.extremeexperts.com/sql/articles/IterateTSQLResult.aspxsql

Lookups - multiple hits

An underlying assumption of the lookup task seems to be that there will be a unique record matching the lookup criteria. However what happens when more than one record is matched? Does it return the designated field of the first record encountered, or does it error out? Is there a way to specify what SHOULD happen in such a case? Is there a way to specify secondary criteria if the primary returns multiple hits?

Related to this - suppose that my lookup returns 2 data points, but one of them is null. It seems that the lookup should appear successful, and rows can then be redirected based on which field was null via the Truncation setting.
For example, say I'm matching on "name" and returning "Id" and "description". The name is found, and there's a valid Id, but the description is null.
Would this result in a successful lookup with the "description" truncated, or would the lookup fail?

Thanks!
PhilIn full cache mode the lookup will report a warning and use one of the records. In partial or no cache it will use one of the records with no warning. I say one of the records because this is an implementation detail and is subject to change.

You can always specify more than one join column so that you will not get multiple hits. Obviously, this is not exactly what you are asking for because it sounds like you only want the secondary match to occur if the primary is a dupe. This the lookup does not do.

NULL does not mean truncation. In the scenario you specify the lookup would be considered successful with a description that happens to be NULL. Trunctation is when you try to put a string that contains 100 characters into a column that is less than 100 characters.

Thanks,
Matt

Lookup with multiple hits

In a Lookup component I've defined a SQL query which returns a sorted resultset. For each Lookup component input row I want to have a single output row. Problem is that for each input row there is possibility of multiple matches in SQL query resultset. From all of the possible multiple hits I want only the first one to be returned, and if no match is found then no output row. How to implement this?

Try changing your lookup query to only return the rows you are interesting in.

Code Block

select

colA

,colB

,max or min (colC)

from

tableA

group by

colA

,colB

|||

The lookup component does this by default does it not?

Edit: that is return only the first result that it runs into. Just make sure that you return the correct row first and you should be fine...

Try the following to prove:

select 19000101 as datekey, '01/01/1900' as datename

UNION

select 19000102 as datekey, '01/02/1900' as datename

UNION

select 19000103 as datekey, '01/03/1900' as datename

UNION

select 19000104 as datekey, '01/04/1900' as datename

UNION

select 19000105 as datekey, '01/05/1900' as datename

in an oledb source

and then in the lookup

select 19000101 as datekey, 'myname11' as name

UNION

select 19000102 as datekey, 'myname21' as name

UNION

select 19000103 as datekey, 'myname31' as name

UNION

select 19000104 as datekey, 'myname41' as name

UNION

select 19000101 as datekey, 'myname12' as name

UNION

select 19000102 as datekey, 'myname22' as name

UNION

select 19000102 as datekey, 'myname23' as name

Notice, there is no match for 19000105, it will redirect.

You will return the values

19000101, '01/01/1900', 'myname11'

19000102, '01/02/1900', 'myname21'

19000103, '01/03/1900', 'myname31'

19000104, '01/04/1900', 'myname41'

|||

Eric Wisdahl wrote:

The lookup component does this by default does it not?

I believe so. The message about duplicate values is just a warning, not an error. I don't think there's a guaranteed order -- it just picks the first one it comes across.|||

Eric Wisdahl wrote:

The lookup component does this by default does it not?

Yes. Lookup is a synchronous component; hence the number of rows in the output is the same than the number of rows in the input. If your lookup query returns more than one row for an incoming row; then 'the first' one would be used; and you don't have control over which one would be used.|||SQL query returns result set of multiple contact persons for multiple companies. On output I need for each company to filter out just one of the contact persons. Result set is sorted (ORDER BY) so that if many contact persons are found per comapny one contact person that should be chosen as highest in order. On input of the Lookup component I've put OLE_SRC component which fetches all the companies.

I couldn't use just GROUP BY because I need columns in resultset which aren't used in aggregate function nor should be be group on.
|||

I would not feel comfortable relying in SSIS picking the 1st from the list. If the query in the lookup component is against SQL Server, Oracle or other RDBS where the rank function I would use a query like:

http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html

Notice that you could mimic the result set even without the rank() function; but the query could get little complex|||Great, thank you all, especially to Rafael.

In an OLE_SRC I manually entered SQL command. I had to do it manually because it seems that Query Builder doesn't (yet) support OVER construct - when I click Build Query on OLE_SRC component it says "The OVER SQL construct or statement is not supported.".
|||

Is your source sql server 2005?

If not, you'll have to use another technique to get the row number

Code Block

select

colA

,(

select count(*)

from tableA b

where b.colA <= a.colA

) as RowNum

from tableA a

|||Yes, the source is SQL Server 2005. OLE DB Source component has Query Builder GUI which doesn't support OVER construct. I entered query manually and it works.

Lookup transform with multiple matches

Please indulge my ignorance, as I have only been using SSIS for a couple of weeks.
I'm trying to create a data warehouse using two input tables.
A column needs to be added to one table by using a lookup into the second table.
SSIS seems to handle the "no matches" and "single match" cases perfectly.
I can't for the life of me figure out how to properly handle multiple matches.
SSIS defaults to the first match, but I need to compute the "best" match.

Many thanks in advance
Scott!

What's the criteria for the "best" match? Knowing the criteria would make it easier to help |||Here are some specifics:
Table A contains repair records.
Table B contains inspection records.
Each piece of equipment can have multiple repairs and many more inspections.
A lookup can be performed based on the equipment number.

I need to determine which repair immediately proceeds each inspection.
This can be found by computing the smallest positive date difference.

Programmactically, I would normally do this with nested loops.
Using SSIS, I suppose it could also be done this way, using a foreach loop
and a condition...
|||

Interesting problem

I haven't tested this, but could you join the rows in SQL on the equipment, calc the date difference and order by it, and use a rank function to get the first row?

LookUp Entities and Roles against Multiple Entities?

Apologies if this has already been asked and answered, though I haven't found it via search.

In my report model I have a lookup entity that consists of a Code field (PK) and a Description field this is linked to a parent entity (table) and works fine, e.g. the description is shown in the parent entity instead of the FK code. However this lookup entity would be useful linked to many other entities (tables) which use the same FK code linked to the lookup entity's Code field (PK) for use of the Description field. In my first attempt I linked the lookup to a second table entity but unlike the originally linked table entity, this is not shown as the description in report builder, only the role appears and I have to select the role then the description from the 2 fields displayed (Code, Description), additionally I get shown the related first table entity.

Firstly can a lookup entity be linked to multiple tables?

Secondly, if so am I forgetting something so only the description is shown like in the first linked table entity?

Thirdly, if I correct the problem is there a property I need to enable/disable to stop the first linked table entity appearing in the second table entity entity list in report builder or should I just leave it?

Any help/advice/suggestions would be appreciated, and apologies for long list of questions but all the books and help files I have used don't seem to use imperfect/real life data sources and examples.

Thanks

Andy

This is a bit weird replying to myself, but think I need to let you know, and others who may come across the problem, I believe I have solved it.

The problem appeared to be related to my using more than 1 Identifying Attribute for the lookup entity. Once I amended this to one Identifying Attribute it would appear that all is well. I get the description instead of a role entity appearing and the lookup works with multiple table entities (e.g. more than one table entity is linked to the lookup-table entity). I think I misunderstood the IdentifyingAttributes property thinking this had to be the PK field.

Thanks

Andy