| Author |
Topic |
|
blackd77
Starting Member
9 Posts |
 Posted - 2007-10-12 : 13:47:44
|
| I have a query that I would like to pare down its results for use in an application (Crystal Reports). For instance, one query would return five rows. I would like the first result to be placed in one location in the CR template, the second result posted below it, etc...In MySQL, I could use something like "LIMIT 0, 1" and "LIMIT 1,1" where the first number is the starting point for the search and the second one is its offset. I was thinking I could use something like this with TOP, but my SQL books don't elaborate much on TOP. Provided this is possible, would I use a TOP flag, or something else? Thanks in advance! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 13:56:03
|
| Could you show us a data example of what you want as most of aren't too familiar with MySQL and some of us have never touched Crystal Reports?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
blackd77
Starting Member
9 Posts |
Posted - 2007-10-12 : 14:39:31
|
| Sure. The Crystal Reports aspect of this is just a matter of pasting the results in a GUI template of boxes. I tried to paste an image of the pertinent area of the application to illustrate, but it didn't work. It's just sort of like a web based form, with a column of boxes for each item below. Each transaction has its own row of boxes in the template, into which we need to plug the specifics from the transaction. For a particular job number (the primary key for this query -- select MCCN, transdate, remainingqty from materialtrans where jobn = 12345This is the result:MCCN Transaction Date Remaining Qty11012 8/22/2007 623811012 8/29/2007 2348 11444 8/22/2007 150 41216 8/22/2007 23841216 8/29/2007 173The first row needs to be assigned to the row of boxes in the template, the second row to the next row, etc. How to I construct a query to isolate each row (return ONLY that row of potential results)? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 14:42:55
|
| You can't isolate rows in SQL Server. You get one result set back per query.So is your question how to do it in Crystal Reports? If so, you'll probably want to post this in a Crystal Reports forum or at the very least change your subject so that people know this is about Crystal Reports.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
blackd77
Starting Member
9 Posts |
Posted - 2007-10-12 : 14:50:40
|
| OK, will do (look to Crystal experts). Since I can execute direct SQL queries from CR, and that is how I'll be generating this data, I didn't see it as a Crystal issue. I can isolate rows within a query with TOP (TOP 1)...I was just looking for a TOP flag that would give the second, third, etc... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 14:57:03
|
| Perhaps you need to explain what you mean by "isolate" then as I'm not seeing how even TOP 1 would help you.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
blackd77
Starting Member
9 Posts |
Posted - 2007-10-12 : 15:17:17
|
| If I introduce "TOP 1" into a query that would otherwise result in five rows being returned, only one row is then returned. That one row is "isolated" from the others. I would like to apply that functionality (the returning of the first record found) to the second record found. If I can do that, I can probably "isolate" the third, fourth, and so on. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 15:19:58
|
| So why not use TOP 5 or TOP n?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
blackd77
Starting Member
9 Posts |
Posted - 2007-10-12 : 16:26:01
|
| Because each of the records found with the TOP 5 or TOP n records need to be delivered individually, one record per query. It's just a matter of SQL capabilities, and nothing else...either Transact-SQL can handle this need or it cannot. MySQL can, but our data is "housed" on a SQL Server 2000 machine. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 16:31:56
|
| Then you'll need to pass multiple queries from your report to SQL Server. Can't a Crystal report use multiple data sets per report like Reporting Services can?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
blackd77
Starting Member
9 Posts |
Posted - 2007-10-12 : 17:49:00
|
| I can, and it can. The problem lies in what I'm trying to (out of necessity) query...I can't include more items in the query beforehand to pare it down. I just know that we will have X results and I need to parse out each subset of X individually. I may not be understanding your last question...I'll look up "multiple data sets/Reporting Services" to look for clues. Thanks for your assistance with this. |
|
|
|
|
Returning specific rows from a query