sql server cursors? 1

[jambu]

I am trying to create a report based on the contents of two tables like so.

select *
from A inner join B on A.key = B.foreignkey
where A.date = {?dateparam}
and B.field like {?multi value param}
order by B.SequenceNumber ASC

I have a problem though in that for each row in A there are 1 or more in B and no way to exclude the 'extra' rows (I just want the first one with the lowest SequenceNumber).

I think I will need to solve this with a stored procedure and use a cursor to 'skip' over the extra rows but I have never used one before and have not found the help files too helpful.

I am using crystal 8.5 and would like to know how I can return specific rows using a cursor in stored proc. I also need to use a multi value string parameter to select with and don't know how to do this.

Thanks in advance.

 

You don't need a SP nor a cursor.

Is this a View, or the SQL from the report, or is it just a secret

Are you grouped by something, or do you want only 1 row returned (the lowest sequence) for the entire report?

Not enough technical information, try posting:

Crysta' version
Database/connectivity used
Example data
Expected output

Stating what doesn't work for you or what you don't know how to do isn't nearly as important as understanding what you have and what you need.

To limit rows to the minimum sequence, and assuming that there's some sort of grouping:

Add to the Report->Edit Selection Formula->Group:

{table.field} = minimum({table.sequence},{table.group})

To limit rows based on some multiple value parameter, create a parameter and select Allow Multiple Values.

Add to the Report->Edit Selection Formula->Record:

{table.field} = {?MyMultiParm}

Hope this gets you there.

-k

 

[synapsevampire]

Should have been from me.

This site is now a BETA version (typical Cold Fusion coders, QA means try it in production), those suffering dropped and no name posts should post a second time:

You don't need a SP nor a cursor.

Is this a View, or the SQL from the report, or is it just a secret

Are you grouped by something, or do you want only 1 row returned (the lowest sequence) for the entire report?

Not enough technical information, try posting:

Crysta' version
Database/connectivity used
Example data
Expected output

Stating what doesn't work for you or what you don't know how to do isn't nearly as important as understanding what you have and what you need.

To limit rows to the minimum sequence, and assuming that there's some sort of grouping:

Add to the Report->Edit Selection Formula->Group:

{table.field} = minimum({table.sequence},{table.group})

To limit rows based on some multiple value parameter, create a parameter and select Allow Multiple Values.

Add to the Report->Edit Selection Formula->Record:

{table.field} = {?MyMultiParm}

Hope this gets you there.

-k

 

[jambu]

Thanks for the reply but I can't get this to work at the crystal level even with your suggestions. I need to do this at the sp level, hence the cursor.

 

[synapsevampire]

Note that I offered a tried and tested solution, requested technical information, and you responded with "I can't get this to work at the crystal level", with no indication of what you tried, in what type of database, using which connectivity, nor examples of the data and expected output.

Why does this need to be done at the Cursor level? Cursors are slow and tpo be avoided when possible. Try reading Celko's "SQL for Smarties"

Why do you need an SP?

If you insist on using an SP, then create a Union in the SP.

-k

 

[jambu]

thanks for the help and the suggestions.
I have posted my question in sql server programming now.

All I want to know is how to use a cursor to select a subset of rows from a select statement and return them from the stored proc.

It is impossible to add the logic I want to use to the where clause of my query. It is also impossible for crystal to filter out that data. (No it really is impossible, believe me I would love to do this in crystal).

I am quite prepared to use a cursor on a rarely run, small data set report.

Please don't reply to this post unless you are can answer the question instead of offering alternatives that I cannot use. If you don't know the answer to the question please don't post at all.

 

[synapsevampire]

There are several answers, none oif which include a Cursor. I've been SQL coding for > 15 years, and your attitude certainly doesn't warrant assistance.

-k